How to get values from unspecified number of sheets

Question

I have a spreadsheet that may have any number of sheets on it at any given time. These "Side Sheets" have a total value added and placed in a specified cell. We'll say this total is in cell "A1" on every side sheet. I want to total all of these side sheet totals, and place the total in-cell on another sheet.

I've coded a solution I think should work, but it displays "loading" forever. I'm certain there's an easier way to do this, I just can't see it.

function GETSIDESHEETTOTALS(){
  var totalCell = "A1"
  var total = 0;
  var cur_ss = SpreadsheetApp.getActive();
  cur_ss.getSheets().forEach(function(sheet){
    total += sheet.getRange(totalCell).getValue();
  });
  return total;
}

I'm expecting the totals from each sheet to add together and display in the cell I've specified on the main sheet. I've placed the function "=GETSIDESHEETTOTALS()" into a cell on the main page of my spreadsheet. I would prefer it to be a cell-called function if possible.

Does anyone have an alternate solution, or can tell me what I'm doing wrong?

Paul Paul · Accepted Answer · 2019-09-01T20:00:44

For those familiar with Excel, this could be rephrased as, "How do I use Google App Script to sum using 3D cell references?".

Briefly looking at yours, you do not exclude the sheet on which you aggregate the total. Perhaps you're recursively adding the values together?

My very quick example from scratch:

function sum3D(cellRef) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var cumTotal = 0;
  for (var i=0;i<sheets.length;i++){
    if (sheets[i].getIndex()!==1){ // specifically omit the first sheet
      cumTotal += sheets[i].getRange(cellRef).getValue();
    }
  }
  return cumTotal;
}

This is implemented in the first sheet in my Google Sheet as "=sum3d('A1')".

However, I would recommend designing this more generally to simply return an array upon which you can perform any function (average, multiplications, etc.).

E.g.

  function get3D(cellRef) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var arr = [];
  for (var i=0;i<sheets.length;i++){
    if (sheets[i].getIndex()!==1){
      arr.push( sheets[i].getRange(cellRef).getValue());
    }
  }
  return arr;
}

and implemented as, e.g., "=sum(get3d('A1'))".

EDIT Some parts unnecessarily separated in the code have been consolidated (but the overall function remains the same)

EDIT 2 There are obvious improvements regarding how you designate the aggregator sheet. For example, you could simply pass in the sheet name in the formula and omit that based on the return value of "sheets[i].getName()".

How to get values from unspecified number of sheets

1 Answers