Fix: Exception: The number of rows in the data does not match the number of rows in the range in Apps Script

TL;DR: The Instant Fix

Stop hardcoding your range coordinates. This error almost always means your data array has a different number of rows than the spreadsheet area you targeted. To fix it, let the data define the range size automatically.

// Avoid this: it breaks if your data grows
// sheet.getRange("A1:B10").setValues(data);

// Use this: it adapts to your data size
if (data && data.length > 0) {
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

The Root Cause

Google’s setValues() method is incredibly picky. It expects a perfect rectangular match between your 2D array and the destination range. If your array contains 500 rows of data but your getRange() call only covers 499 rows, the script will immediately halt.

Here is where things usually go wrong:

• Static Ranges: You hardcoded a range like "A2:C100", but your data source just grew to 101 rows.
• Dimension Mismatch: You are passing a simple list like [1, 2, 3] instead of the required nested format like [[1], [2], [3]].
• Empty Filters: You filtered your dataset, but the resulting set is smaller than the original range you cleared.

Reliable Solutions

1. Dynamic Range Sizing

Calculating the end row or column manually is a recipe for bugs. Instead, use the length property of your array. This tells the sheet exactly how much space to carve out for your data.

function updateSheetSafely(data) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Report");

  // Guard clause to prevent errors on empty datasets
  if (!data || data.length === 0) return;

  const startRow = 1;
  const startCol = 1;
  const numRows = data.length;
  const numCols = data[0].length;

  // This range will always expand or shrink to fit your data
  sheet.getRange(startRow, startCol, numRows, numCols).setValues(data);
}

2. Mapping 1D Arrays to 2D

Apps Script treats ["Value 1", "Value 2"] as a flat list, not a grid. setValues() will reject this format. You must wrap your data to give it "height" or "width."

// To write as a single ROW (1 row, 3 columns)
const rowData = [["Value 1", "Value 2", "Value 3"]];
sheet.getRange(1, 1, 1, rowData[0].length).setValues(rowData);

// To write as a single COLUMN (3 rows, 1 column)
const flatArray = ["A", "B", "C"];
const colData = flatArray.map(item => [item]); // Result: [["A"], ["B"], ["C"]]
sheet.getRange(1, 1, colData.length, 1).setValues(colData);

3. Debugging Off-by-One Errors

When building arrays inside loops, it is easy to accidentally add an extra empty row at the end. Use console.log() to check the dimensions right before the script crashes. This reveals exactly what the script sees.

console.log("Array Rows: " + data.length);
console.log("Array Cols: " + data[0].length);
// If these numbers don't match your range, the script will fail.

How to Verify the Fix

Check these three things to ensure your script is production-ready:

• The Execution Log: Look for the green "Completed" checkmark. If you see "Failed," the dimensions are still mismatched.
• Sheet Boundaries: Verify the last row of your data. A dynamic range ensures data fits perfectly without leaving gaps or overwriting the wrong cells.
• Stress Testing: Run the script with a single row, then try it with 1,000 rows. A robust script handles both without any manual code changes.

Pro-Tip: Avoid "Ghost" Data

Dynamic ranges solve the crash, but they can leave old data behind. If your new dataset has 10 rows and your old one had 50, setValues() only overwrites the first 10. Always clear the target area first to keep your reports clean.

// Clear everything before writing fresh data
sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);