-2

I have a summary sheet called Summary. I then have multiple tabs that apply to people - these are named after them e.g. John Smith, Will Jones, Sally Smith... I want to sum or count the same cell in each of named tabs - how do I do this automatically?

Marios
  • 26,333
  • 8
  • 32
  • 52
Mark Ainsworth
  • 1
  • I have recently added an answer below. If that works for you, kindly upvote and pick it as an answer for the others to quickly identify it if they came here and want to fix a problem similar to yours. – NightEye Nov 25 '20 at 16:29
  • I have updated the answer to exclude the summary sheet as you've mentioned. Added filter on the getSheets() call. – NightEye Nov 25 '20 at 18:36

2 Answers2

1

What you can actually do is traverse all the sheets and them just sum them up. The sample below shows how to sum all cell 'A1' in all sheets.

function sumCells() {
    const cellLocation = "A1"; 
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheets = ss.getSheets().filter(function (sheet) {
      return sheet.getSheetName() != "Summary";
    }); // added filter to exclude Summary sheet from the calculation

    var sum = 0;
    var sheetName, cell, value;
    sheets.forEach((sheet) => {
        sheetName = sheet.getName();
        cell = sheet.getRange(cellLocation)
        value = cell.getValue();
        sum += parseFloat(value); // if has decimal
        // sum += parseInt(value); // if has whole numbers (no decimal)
    })
    Logger.log(sum);
}
NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    Good answer. Just one note. You can put a line or condition to exclude the `Summary` sheet. Either in the beginning exclude it from the `sheets` object or inside the `forEach` loop. – Marios Nov 25 '20 at 18:06
  • 1
    hey, Thanks for that @Marios, that went over my head. I'll edit the answer for that one. To be more efficient, it would be best to exclude it from the `sheets` instead of condition inside the loop. – NightEye Nov 25 '20 at 18:23
  • Thanks Nazi for having a go. This isn't working for me though - a few points: 1. What do I need to pass to it? =sumCells("C10") for example? 2. It says my script needs to be verified by google - is this a thing? how do I get them to do that? – Mark Ainsworth Nov 27 '20 at 11:53
  • I pass it nothing (), ("c10") or (c10) it all returns a blank – Mark Ainsworth Nov 27 '20 at 12:06
  • Hello @MarkAinsworth, you dont need to pass anything to the function. you just need to update the cellLocation value on where you put the data you want to add. In the code I presented above, change "A1" to the cell you want to sum up (C10 I believe) and just run the function. – NightEye Nov 27 '20 at 13:04
  • Sorry but this is still not working. The cell just stays blank. – Mark Ainsworth Nov 30 '20 at 08:58
  • When I try to run the script imn the script editor is says "Authorization required - sumCells needs your permission to access your data on Google" and when I click authorise it says "This app isn't verified - This app hasn't been verified by Google yet. Only proceed if you know and trust the developer." – Mark Ainsworth Nov 30 '20 at 08:59
  • please see https://stackoverflow.com/questions/45437557/this-app-isnt-verified-this-app-hasnt-been-verified-by-google-yet-only-procee @MarkAinsworth – NightEye Nov 30 '20 at 12:55
0

I was having the same issue with NighEye's script not passing the value back to my spreadsheet. In place ofLogger.log(sum); I added this to pass the value back into my spreadsheet: SpreadsheetApp.getActive().getRange("Sheet1!L1").setValue(sum);

Henry Blanchard
  • 1