I'm trying to script an equivalent to Excel 3D formula into Google sheets. I have a workbook with numerous worksheets and a master sheet that should sum up the data.
In excel I would have used this 3D formula :
=SUM(Sheet1:Sheet4!A2)
Working with Google Sheets for just a couple of months, I have some difficulties going through this.
I tried the following found here on stackExchange:
function sum3D(reference, start, end){
var sheets = SpreadsheetApp.getActiveSpredsheet().getSheets();
var sum = 0;
for(var i = start; i <= end; i++){
sum += sheets[i].getRange(reference).getValue();
}
return sum;
}
But this would sum the sheets only defined by their index and would not include future sheets inserted as would Excel do.
So I would like to change this script using only optional argument fromIndex: to sum all values across all sheets from the chosen sheet in my workbook, as ordered by tab position.
That way, I would insert my new sheets after the from Index and would be automatically added to my sum.
Here is what I came with but it's not working and literally driving me mad...
function sum3D(rangeReferenceString,fromIndex){
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sum = 0;
for(var i = fromIndex){
sum += sheets[i].getRange(reference).getValue();
}
return sum;
}
Would you be so kind to help me find my way through this?
