How to sum values from all Sheet dynamically in Google Sheets

Question

I have a list of sheets in my workbook (Sheet1, Sheet2, Sheet3) and I want to sum the values of a specic cell (A1) from all those sheets.

I could do =Sum('Sheet1'!A1;'Sheet2'!A1;'Sheet2'!A1). The catch is that i'll be adding multiple sheets as needed (e.g every new customers) and similar formulas will be used throughout the whole workbook, so editing the formula by hand every time is not an option.

I want to do that dynamically.

I'm toying with the idea of having a list of names of all the sheets and referecing with the INDIRECT(ADDRESS(...) formula, but that's not elegant and it also lacks the ability of changing the structure of the sheets (say, adding a new line).

Any ideia on how to do that? Preferably using only formulas.

Answer 1

Try this:

function sumA1(){
  var ss= SpreadsheetApp.getActive();
  const shts = ["Sheet0","Sheet1","Sheet2"];
  let s = ss.getSheets().filter(sh => ~shts.indexOf(sh.getName())).reduce((a,sh) =>  {a+=sh.getRange("A1").getValue();return a},0)
  Logger.log(s);
  return s;
}