Google-sheets – 3D Formula Google Sheets – sum across sheets

google sheetsgoogle-apps-scriptgoogle-sheets-custom-function

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?

Best Answer

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

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for

Conversion from Excel to Google Spreadsheet - sum across sheets

Related Solutions

Google-sheets – Filter() – referencing source array in criteria

I've been working on your file, even before the bounty period, and it as been a struggle. I wanted to give you the automation te ARRAYFORMULA can give in combination with a bit of Google Apps Script. In doing so, I wanted to understand the calculations a bit and thus I started with optimizing the first formula for column E:

=ARRAYFORMULA(IF(A4:A<>"";ARRAYFORMULA(A4:A*D4:D);""))

For column H, I created to following formula:

=ARRAYFORMULA(IFERROR(VLOOKUP(G4:G30;Suits!A:AC;{MATCH(H2, suits!A1:AC1 ,0)}*SIGN(ROW(G4:G30));0),""))

For column I, I created the following script:

function skills(array1, array2, array3) {
  var array = new Array();

  for(i=0, len2=array2.length; i<len2; i++) {
    var prod = 1;
    for(j=0, len1=array1.length; j<len1; j++) {
      if("% " + array2[i][0] == array1[j][0]) {        
        prod *= array1[j][2] + 1;        
      }      
    }
    var value;
    if(array3[i][0] == "") {
      value = "";
    } else {
      value = prod * array3[i][0];
    }
    array.push([value]);
  } 
  return array;
}

For column J, I created te following script:

function mods(array1, ModAttrb, ModHeader, ModStats, array5, array6) {
  var array = new Array(), aSecond = new Array(), aThird = new Array();

  // second calculation  
  for(i=0, len1=array1.length; i<len1; i++) {    
    var secondSum=0;    
    for(j=0, len2=ModAttrb.length; j<len2; j++) {      
      if(array1[i][0] == ModAttrb[j][0]) {        
        for(l=1, len4=ModStats[0].length; l<len4; l++) {
          if(ModStats[j][l] == 0 || ModStats[j][l] == "") {
            secondSum += 0;
          } else {
           secondSum += ModStats[j][l];
          }
        }
      }
    } 
    aSecond.push(secondSum);
  } 

  // third calculation
  for(i=0, len1=array1.length; i<len1; i++) {
    var thirdSum=0;
    for(m=0, len5=array5.length; m<len5; m++) {
      if(array1[i][0] == array5[m][0]) {        
        thirdSum += parseInt(array5[m][3]);        
      }      
    }
    if(thirdSum == 0) {
      thirdSum = 0;
    } 
    aThird.push(thirdSum);
  }  

  for (var i=0; i < aSecond.length; i++) {
    var result = 0;
    result += (array6[i] * 1 + parseInt(aSecond[i]) + parseInt(aThird[i]));
    array.push([result])
  }  
  return array;
}

Here, for the sake of time, I set the first calculation to be 1.

After that, time was running short. The Mult column is/was difficult to comprehense. We can work on it afterwards if you want?

See te example file I copied in te beginning: Fitting Tool

Google-sheets – Sum of dynamic number of values derived from fomula

The script below will perform a summation throughout tabs, taking into account different strings, as shown in your question.

Code

function mySum(tabRange, strings) {
  // small error handling
  if(typeof tabRange === 'string') {
    throw 'Multiple tabs are needed. Otherwise use SUM';
    return
  }  

  // get active spreasdsheet and create empty arrays
  var ss = SpreadsheetApp.getActive(), aStr = [], output = [];

  // push first result in array to control different strings
  aStr.push(strings[0][0], 0);

  // iterate through tabs
  for(var i = 0, iLen = tabRange.length; i < iLen; i++) {

    // get all data (at once) from tab
    var data = ss.getSheetByName(tabRange[i][0]).getDataRange().getValues();  

    // iterate through strings
    for(var k = 0, kLen = strings.length; k < kLen; k++) {

      // create and set the summation per string
      var strSum = 0;

      // iterate through the data
      for(var j = 0, jLen = data.length; j < jLen; j++) {

        // if a match is found, then add to the strSum
        if(data[j][1] === strings[k][0]) {
          strSum += data[j][0];
        }
      }

      // find index in array that matches a string
      var index = aStr.indexOf(strings[k][0]);

      // if found, then add to existing value
      // else, create a new entry for that string
      // so that it will be found the next time
      if(index !== -1) {
        aStr[index+1] += strSum;
      } else {
        aStr.push(strings[k][0], strSum);
      }
    }
  }

  // get rid off the strings  
  for(var l = 1, lLen = aStr.length; l < lLen; l += 2) {
    output.push(aStr[l]);
  }

  // show the outcome  
  return output; 
}

Screenshot

enter image description here

Example

I've created an example file for you: Sum of dynamic number of values derived from fomula