3

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?

user1416564
  • 401
  • 5
  • 18

1 Answers1

7

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.

JPV
  • 26,499
  • 4
  • 33
  • 48
  • So a couple of issues with your particular solution: 1) Because I am passing in "H2" instead of H2, I cannot copy and past this if I want to do say 50 cells like this in a row 2) If I update a value in one sheet after I've used the formula, it doesn't update the values in the "totals" sheet if I, for example, change the value under the "bob" sheet. – user1416564 Apr 03 '15 at 11:50
  • If you want to do 50 cells, you need to pass in the range, but then the code needs some changes. The update-issue is a well know issue typical for custom formulas: they suffer from what is know as 'memoization'. There is a way to 'enforce' the recalculation. For more info: http://stackoverflow.com/questions/9022984/google-apps-script-to-summarise-data-not-updating/9023954#9023954 – JPV Apr 03 '15 at 16:16