Google Sheets Sum values from ALL sheets in a workbook

Question

So assuming I have a Sheets Workbook with random sheet names, "Bob", "Sally", "Billy", "John" or something similar that isn't sequential. And I have another sheet called "Totals". How can I sum the values from a particular cell, say "H4" from all of them? Specifically if I added new sheets after I created the formula without having to change the formula every time?

Answer 1

You may want to use a custom function for that. Add this to the scripteditor

function sumCellAS(cell) {
var val = [];
SpreadsheetApp.getActive()
    .getSheets()
    .forEach(function (s) {
        val.push(Number(s.getRange(cell)
            .getValue()))
    });
return val.reduce(function (x, y) {
    return x + y;
    });
}

Then in your spreadsheet enter the formula =sumCellAS("H2"), passing in the cell to be summed, as a string.