Originally written for bulldo.gs — republished here with the canonical link pointing home.
I want to rewrite my getValues for loop using map and filter so the code is shorter, but I keep getting a parameter mismatch error when I try to write results back with setValues.
// Keep only rows where column A > 100, then write column B values
function filterAndRemap() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
// filter keeps the full row -- 2D shape intact
var filtered = data.filter(function(row) {
return row[0] > 100;
});
// map over filtered rows to extract column B, wrapped back into arrays
var colB = filtered.map(function(row) {
return [row[1]];
});
if (colB.length > 0) {
sheet.getRange(2, 4, colB.length, 1).setValues(colB);
}
}
What getValues actually gives you
getValues returns a 2D array: an array of rows, where each row is itself an array of cell values. A three-row, two-column range comes back as [[A1,B1],[A2,B2],[A3,B3]]. That 2D shape is not optional -- setValues requires it, and flattening it (even accidentally) triggers the parameter mismatch error: 'The number of rows in the data does not match the number of rows in the range.'
The classic for loop sidesteps this by never changing the shape at all; it just reads and writes by index. When you switch to map and filter you start transforming the array, which is where the shape problem surfaces.
filter rows first, then map values -- in that order
filter is safe because it returns a subset of the original rows unchanged. Each surviving element is still a full array, so the outer array remains 2D. You lose nothing by running filter before any other transformation.
map is where people get burned. Calling data.map(function(row) { return row[0]; }) produces a 1D array of scalars -- fine for reading, fatal for setValues. The fix is to wrap the extracted value back into a one-element array: return [row[0]]. That single extra pair of brackets restores the 2D shape setValues needs.
The first time I hit this I spent twenty minutes staring at what looked like a perfectly correct range reference before realizing the column count was 1 but the data depth was 0. The guard at the end (checking colB.length before calling setValues) is also worth keeping: calling setValues on a zero-row range throws a separate error that is even harder to read.
Chaining without a temp variable
Once the filter-then-map order is clear, you can chain both calls directly off getValues and skip the intermediate variable. That cuts a five-to-eight line for loop down to three lines of actual logic, with no index arithmetic and no off-by-one risk on the loop bound.
One real tradeoff: chaining obscures the intermediate shape for anyone reading the code later. If the filter condition is at all complicated, keeping filter and map as separate named variables (as in the snippet above) makes the intent obvious at a glance. For a trivial condition like row[0] > 100, the chain is fine. Use judgment rather than a rule.
FAQ
Why does map(function(row) { return row[0]; }) cause a setValues error?
That map produces a 1D array of scalars, like [10, 20, 30]. setValues requires a 2D array where every element is itself an array. Wrap the value: return [row[0]], which gives [[10],[20],[30]] -- the shape setValues accepts.
Can I filter and map in a single pass instead of two steps?
You can use reduce to do both at once, but Apps Script does not have flatMap and reduce with conditional push is harder to read than a chained filter().map(). Two steps is idiomatic and the performance difference over a sheet-sized dataset (a few thousand rows) is not measurable.
Does this work if I need to keep multiple columns, not just one?
Yes. In the map callback, return all the columns you want as an array literal: return [row[1], row[2], row[4]]. The length of that inner array must match the column count you pass to getRange when you call setValues.
What happens if filter removes all rows and colB is empty?
setValues on an empty array throws 'Incorrect range height'. Guard with if (colB.length > 0) before the write, as shown in the snippet. Alternatively, clear the destination range first so no stale values remain when the filter result is zero rows.
Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.
