// Sheets · Apps Script

Fix "The number of rows in the data does not match the number of rows in the range" in Google Sheets.

The setValues error means your 2-D array and your getRange call disagree on row or column count. Learn the one pattern that eliminates this class of bug permanently: size the range from the data, not the other way around.

“I'm getting a "number of rows in the data does not match" error when I call setValues in Apps Script and I can't figure out why my range and array are out of sync.”

The script

copy · paste · trigger
writeFilteredRows.gs
Apps Script
// Write only rows where column C is non-empty back to Sheet2.
// Key: size the destination range from the data — never hard-code it.
function writeFilteredRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src = ss.getSheetByName('Sheet1');
  var dst = ss.getSheetByName('Sheet2');

  var srcData = src.getDataRange().getValues();
  var filtered = [];

  for (var i = 0; i < srcData.length; i++) {
    if (srcData[i][2] !== '') {
      filtered.push(srcData[i]);
    }
  }

  if (filtered.length === 0) return;

  var range = dst.getRange(1, 1, filtered.length, filtered[0].length);
  range.setValues(filtered);
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why the error fires

setValues takes a 2-D array and writes it into a Range object. The API requires an exact match: the Range must have the same number of rows and the same number of columns as the array. If either dimension is off by even one, you get the 'number of rows in the data does not match' error (sometimes a column variant surfaces instead, but the root cause is identical).

The mismatch almost always comes from a hard-coded getRange call written when the script was first drafted against a known dataset, combined with an array that is later built dynamically — filtered with a condition, grown with push, or sliced. The range stays fixed; the array changes with the data. At some row count they diverge and the script breaks.

The first time I hit this, I spent twenty minutes re-checking my filter logic when the actual problem was a getRange('A1:D50') I'd forgotten at the bottom of the function. The data had shrunk to 31 rows; the range expected 50.

Invert the dependency: derive the range from the array

The fix is structural, not a patch. Instead of calling getRange with fixed row and column counts and then hoping the array matches, call getRange(startRow, startCol, data.length, data[0].length) after the array is fully built. The range is now a function of the array, so they cannot diverge.

In the snippet above, filtered is assembled with a for-loop and push, and only then is the range sized: dst.getRange(1, 1, filtered.length, filtered[0].length). The column count comes from filtered[0].length rather than a magic number, which handles cases where the source sheet gains or loses columns over time.

One guard is worth adding before that line: if filtered.length === 0, return early. Calling getRange with a row count of zero throws a separate 'range height must be at least 1' error, which is confusing if you have not seen it before.

Common variants that catch people out

Range strings like getRange('A1:D') — an open-ended column reference — look dynamic but Sheets still resolves them to a fixed row count at call time, usually the sheet's last row, which may not match your array. Avoid range strings entirely when writing back dynamic data; use the four-argument form getRange(row, col, numRows, numCols).

Appending to an existing dataset is another trap. If you fetch existing rows, concatenate new rows onto them, and then write back starting at row 1, the combined array length must equal the range you write into. The safest pattern is to clear the destination first — dst.clearContents() — then write the full array starting at row 1 with the derived-range approach. That avoids off-by-one bugs from partial overwrites.

A subtler variant: getValue (singular) returns a scalar, but setValues (plural) expects a 2-D array even for a single cell. Passing a flat array like ['a', 'b'] instead of [['a', 'b']] gives the same dimension error. If you are writing a single row, wrap it: setValues([rowArray]).

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
My array and range look the same size when I log them, but I still get the error. What am I missing?
Log filtered.length and filtered[0].length immediately before the getRange call, and log the range with range.getNumRows() and range.getNumColumns() immediately after. Apps Script sometimes silently adjusts a range to fit sheet boundaries, so a sheet with 40 rows of data may quietly cap a range you asked for at 50 rows to 40. If the sheet is shorter than your array, the write will fail. Clear the sheet first or extend it before writing.
Does this error appear with setValue (no 's') too?
No. setValue takes a single scalar and writes it to the entire range — no dimension check. The dimension mismatch error is specific to setValues (plural), which requires the 2-D array to exactly match the range dimensions.
I am using appendRow in a loop instead of setValues. Should I switch?
Yes, if you are appending more than a handful of rows. appendRow makes one API call per row, which burns quota quickly and is slow. Build the full 2-D array first, then write it in one setValues call. For 500 rows, this is the difference between a few seconds and hitting the 6-minute execution limit.
What happens if filtered[0] is undefined because all rows were filtered out?
filtered[0].length throws a TypeError before you even reach setValues. Guard with an early return when filtered.length === 0, as shown in the snippet. This is worth treating as a normal condition rather than an error — some days a filter legitimately returns nothing.

Related scripts