Total quantity per SKU, spend per vendor, hours per client. sumByKey(values, keyIndex, valueIndex) takes a 2D array with a header row and returns a small summary table, header included and sorted by key, ready for setValues.
When to use
- Quick totals from an import without building a pivot table
- Summaries a script writes to a report tab or an email
- Feeding the totals into another check, like a stock warning
How to use this snippet
/**
* Sum one column grouped by another. Blank keys are skipped, non-numbers count as 0.
*
* @param {Array[]} values 2D array with a header row
* @param {number} keyIndex 0-based column to group by
* @param {number} valueIndex 0-based column to add up
* @return {Array[]} [[keyHeader, valueHeader], [key, total], ...]
*/
function sumByKey(values, keyIndex, valueIndex) {
var totals = {};
values.slice(1).forEach(function (row) {
var key = String(row[keyIndex]).trim();
if (!key) return;
var n = Number(row[valueIndex]);
totals[key] = (totals[key] || 0) + (isNaN(n) ? 0 : n);
});
return [[values[0][keyIndex], values[0][valueIndex]]].concat(
Object.keys(totals).sort().map(function (k) { return [k, totals[k]]; })
);
}
Example
function writeTotals() {
var ss = SpreadsheetApp.getActive();
var values = ss.getSheetByName('Orders').getDataRange().getValues();
var totals = sumByKey(values, 1, 3); // column B = SKU, column D = qty
var out = ss.getSheetByName('Totals');
out.clearContents();
out.getRange(1, 1, totals.length, 2).setValues(totals);
}
Notes
- Keys are trimmed and compared as text, so
42and"42"land in the same group. - Need a count instead of a sum? Add 1 instead of the value.
Happy Coding!
