Suggestion
NOTE: This script is intended to serve as a starting point or reference for your project. It s important to note that the community members do not provide coding services.
Based on your description, it seems you want to subtract the subsequent years per cell in column D, where the years are separated by new lines (
). The result of the subtraction should be displayed adjacent to the corresponding column A references, also separated by new lines.
To tackle this in Google Apps Script, you can split the column D values by the new line character, subtract the subsequent years, and then join the results with new lines. Here s an example script as a custom Google Sheet function:
Sample Script
function yearsCount(range) {
var letters = [];
var years = [];
range.forEach(x => (letters.push(x[0].split(/
?
/)), years.push(x[1].split(/
?
/)))); //Loop & push the letters & years
return years.map(x => x.map((y, i, arr) => { //Iterate through the years
try {
var yearData = (parseInt(arr[i + 1].match(/d{4}/)[0]) - parseInt(y.match(/d{4}/)[0]));
return letters[0][i] + + (yearData > 1 ? yearData + years : yearData + year );//Subtract the current year to the next year
} catch {
return letters[0][i] + + current or null ; // return null if reached the last year
}
})).map(x => [x.join(
)]); // restructure the year array and join them with new line to be returned back to sheet
}
Demo
References
