While there's another answer that works for this, I think the use of text finders and getRange, getValue and setFormula in loops is not the best approach, since it greatly increases the amount of calls to the spreadsheet service, slowing down the script (see Minimize calls to other services).
Method 1. onEdit trigger:
An option would be to use an onEdit trigger to do the following whenever a user edits the spreadsheet:
- Loop through all sheets (excluding
Totals).
- For each sheet, loop through all data.
- For each row, check if the category has been found previously.
- If it has not been found, add it (and the corresponding amount) to an array storing the totals (called
items in the function below).
- If it has been found, add the current amount to the previous total.
- Write the resulting data to
Totals.
It could be something like this (check inline comments for more details):
const TOTAL_SHEET_NAME = "Totals";
const FIRST_ROW = 4;
function onEdit(e) {
const ss = e.source;
const targetSheet = ss.getSheetByName(TOTAL_SHEET_NAME);
const sourceSheets = ss.getSheets().filter(sheet => sheet.getName() !== TOTAL_SHEET_NAME);
let items = [["Category", "Amount"]];
sourceSheets.forEach(sheet => { // Loop through all source sheets
const values = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW+1, 2).getValues();
values.forEach(row => { // Loop through data in a sheet
const [category, amount] = row;
const item = items.find(item => item[0] === category); // Find category
if (!item) { // If category doesn't exist, create it
items.push([category, amount]);
} else { // If category exists, update the amount
item[1] += amount;
}
});
});
targetSheet.getRange(FIRST_ROW-1, 1, items.length, items[0].length).setValues(items);
}
Method 2. Custom function:
Another option would be to use an Apps Script Custom Function.
In this case, writing the data via setValues is not necessary, returning the results would be enough:
const TOTAL_SHEET_NAME = "Totals";
const FIRST_ROW = 4;
function CALCULATE_TOTALS() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheets = ss.getSheets().filter(sheet => sheet.getName() !== TOTAL_SHEET_NAME);
let items = [["Category", "Amount"]];
sourceSheets.forEach(sheet => { // Loop through all source sheets
const values = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW+1, 2).getValues();
values.forEach(row => { // Loop through data in a sheet
const [category, amount] = row;
const item = items.find(item => item[0] === category); // Find category
if (!item) { // If category doesn't exist, create it
items.push([category, amount]);
} else { // If category exists, update the amount
item[1] += amount;
}
});
});
return items;
}
Once the script is saved, you can use this function the same you would use any sheets built-in function:
The problem with this approach is that the formula won't recalculate automatically when changing any of the source data. In order to do that, see the above method.
Method 3. onSelectionChange trigger:
From your comment:
I'd love to be able to trigger it when the totals sheet is opened but that doesn't appear to be possible
You can do this by using an onSelectionChange trigger in combination with PropertiesService.
The idea would be that, every time a user changes cell selection, the function should check whether current sheet is Totals and whether the previously active sheet is not Totals. If that's the case, this means the user just opened the Totals sheet, and the results should update.
It could be something like this:
function onSelectionChange(e) {
const range = e.range;
const sheet = range.getSheet();
const sheetName = sheet.getName();
const previousSheetName = PropertiesService.getUserProperties().getProperty("PREVIOUS_SHEET");
if (sheetName === TOTAL_SHEET_NAME && previousSheetName !== TOTAL_SHEET_NAME) {
updateTotals(e);
}
PropertiesService.getUserProperties().setProperty("PREVIOUS_SHEET", sheetName);
}
function updateTotals(e) {
const ss = e.source;
const targetSheet = ss.getSheetByName(TOTAL_SHEET_NAME);
const sourceSheets = ss.getSheets().filter(sheet => sheet.getName() !== TOTAL_SHEET_NAME);
let items = [["Category", "Amount"]];
sourceSheets.forEach(sheet => { // Loop through all source sheets
const values = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW+1, 2).getValues();
values.forEach(row => { // Loop through data in a sheet
const [category, amount] = row;
const item = items.find(item => item[0] === category); // Find category
if (!item) { // If category doesn't exist, create it
items.push([category, amount]);
} else { // If category exists, update the amount
item[1] += amount;
}
});
});
targetSheet.getRange(FIRST_ROW-1, 1, items.length, items[0].length).setValues(items);
}
Note: Please notice that, in order for this trigger to work, you need to refresh the spreadsheet once the trigger is added and every time the spreadsheet is opened (ref).
Reference:
