JavaScript for Sheets Updated November 2026

map, filter and reduce

On the 2D array from getValues: rows.map(fn) returns a new array with each row transformed, rows.filter(fn) keeps rows where fn returns true, and rows.reduce((acc, row) => acc + row[2], 0) folds them into one value such as a total. None of them changes the original array, and they chain: rows.filter(...).map(...).

Syntax

array.map(row => newRow) | array.filter(row => condition) | array.reduce((acc, row) => acc + row[i], 0)

Part	What it is
`callback (element, index, array)`	Called once per row; index is 0-based
`reduce initial value`	Second argument; always pass it (0 for sums, {} for objects, [] for arrays)

Examples

const rows = [["Pens", 12, 2.5], ["Paper", 5, 4], ["Clips", 300, 0.02]];
const totals = rows.map(r => [r[0], r[1] * r[2]]);
Logger.log(JSON.stringify(totals));

Result: [["Pens",30],["Paper",20],["Clips",6]]

const low = rows.filter(r => r[1] < 10);
Logger.log(JSON.stringify(low));

Result: [["Paper",5,4]]

const stockValue = rows.reduce((sum, r) => sum + r[1] * r[2], 0);
Logger.log(stockValue);

Result: 56

const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues();
const out = data.filter(r => r[2] === "Open").map(r => [r[0], r[1]]);
const target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open");
target.clearContents();
if (out.length) target.getRange(1, 1, out.length, 2).setValues(out);

Result: The Open tab lists name and amount of every Open row

Common mistakes

map returns undefined for every row: the arrow function has braces without return, write r => [r[0]] or r => { return [r[0]]; }.
setValues fails after map because rows are plain values: map must return an array per row ([v], not v) for a one-column write.
reduce gives NaN: a cell is text or blank; use Number(r[1]) || 0 inside the reducer.
The filter removes everything: compare with the right type; numbers from cells are numbers, so r[1] === "5" never matches.

Practice map, filter and reduce

These drills check your answer on live data, free, no account needed.

Total of a listReturn the total of an array of numbers. An empty array totals `0`. Numbers can be negative or have decimals.
Items in stockEach item looks like `{ name, qty }`. Return the items that are in stock, meaning `qty` is greater than 0, in their original order. A negative `qty` is a back order, not stock.
Full namesEach user looks like `{ first, last }`. Return an array of full names: first and last joined by one space. Some people have no last name (an empty string, or no `last` field at all); for them return just the first name, with no trailing space.

Questions

Does map change the sheet?

No. It only builds a new array in memory; nothing changes until you call setValues with the result.

How do I get one column as a flat list?

data.map(r => r[1]) gives a 1D array of column B; data.flat() flattens the whole block.

When should I use a for loop instead?

When you need to stop early (break), when you mutate several outputs at once, or when readability wins. Performance is similar.

forEach vs for loopsforEach, for, for...of and for...in in Google Apps Script: differences on 2D sheet data, when you need the index or an early break, why for...in fails.
Loop through rowsLoop through rows in Google Sheets with Apps Script: read the data once with getValues, loop the 2D array with for, for...of or forEach, then write back.
Objects: keys, values and entriesWork with objects in Google Apps Script: turn sheet rows into objects by header, Object.keys, values and entries, Object.fromEntries, count or group rows.
Remove duplicatesRemove duplicate rows in Google Sheets with Apps Script: range.removeDuplicates for whole rows or chosen columns, or a JavaScript Set filter on one column.