0

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?

0

The for statement requires three parameters

  • one or more expressions for iteration variables initialization
  • a conditional expression
  • one or more expressions to change the iteration variables

Resources

Related

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.