Why does this error appear only sometimes, not on every run?

The sheet's row count changes. deleteRows(), sheet duplication, or manually deleting a block of rows all lower getMaxRows(). A script that wrote 800 rows into a 1000-row sheet worked fine; after someone trimmed the sheet to 750 rows, the same script fails. Check getMaxRows() at runtime, not at deploy time.

Can I just call sheet.clearContents() and setValues() without inserting rows?

clearContents() removes cell values but does not shrink getMaxRows(). So if the sheet already has enough rows from a previous run, clearContents() plus setValues() works. If the sheet was trimmed or is brand-new and smaller than your data, you still need the expand step. The safe pattern is: expand if needed, then write — regardless of whether you cleared first.

Does spreadsheetApp.insertRowsBefore() work instead of insertRowsAfter()?

It works but inserts at the top, shifting existing data down. If you're writing to row 1 and the sheet has headers or prior data you want to preserve, insertRowsBefore() breaks that layout. insertRowsAfter(getMaxRows(), delta) appends cleanly at the bottom without disturbing what's already there.

What if I want to write starting at a row other than row 1?

The expand check needs to account for the offset. If you're writing at startRow, the sheet needs at least startRow + numRows - 1 rows total. Change the condition to: if ((startRow + numRows - 1) > sheet.getMaxRows()), then insert the difference. The same logic applies to column offsets.

// Sheets · Apps Script

Fix "The coordinates of the range are outside the dimensions of the sheet".

Calling setValues() into a range that exceeds the sheet's current row or column count throws a coordinates error. Learn why the grid has hard limits and how to expand it before writing data.

“I'm calling setValues() or getRange() in Apps Script and getting a coordinates error because my data is larger than the sheet.”

The script

copy · paste · trigger

writeWithExpand.gs

Apps Script

// Write a 2-D array to a sheet, expanding rows/cols if needed
function writeWithExpand(sheetName, data) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var numRows = data.length;
  var numCols = data[0].length;

  // Expand rows if the data is taller than the current grid
  if (numRows > sheet.getMaxRows()) {
    sheet.insertRowsAfter(sheet.getMaxRows(), numRows - sheet.getMaxRows());
  }

  // Expand columns if the data is wider than the current grid
  if (numCols > sheet.getMaxColumns()) {
    sheet.insertColumnsAfter(sheet.getMaxColumns(), numCols - sheet.getMaxColumns());
  }

  sheet.getRange(1, 1, numRows, numCols).setValues(data);
}

Generate yours

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

Walkthrough

Why a sheet that looks infinite has a hard edge

Google Sheets presents an effectively bottomless grid visually, but every sheet object has a definite row and column count stored in its metadata. A fresh sheet starts at 1000 rows by 26 columns. That metadata is what getMaxRows() and getMaxColumns() read. When you call getRange(1, 1, numRows, numCols) and numRows or numCols exceeds those stored limits, the API throws the coordinates error before it even attempts to write. The grid you see scrolling past row 1000 is Sheets rendering empty cells speculatively; the backing store hasn't actually allocated those rows.

The first time I hit this, the sheet had been truncated by a previous script that called deleteRows() to clean up old data. The max row count shrank accordingly, and a later write that used to fit cleanly started failing. Checking getMaxRows() at write time — not at import time — is the safe pattern.

Expanding the grid before writing

The fix is insertRowsAfter() or insertColumnsAfter(), called with the current max as the anchor and the shortfall as the count. In the snippet above, sheet.insertRowsAfter(sheet.getMaxRows(), numRows - sheet.getMaxRows()) appends exactly as many rows as needed — no more. This avoids the common mistake of calling sheet.insertRows(1, numRows), which prepends rows at the top and shifts all existing data down.

Column overflow is rarer but bites scripts that build wide pivot-style outputs. The same pattern applies: check getMaxColumns(), call insertColumnsAfter() with the delta. Both calls are cheap; they update metadata only and don't trigger a repaint on the user's browser.

One thing to watch: insertRowsAfter() counts from 1, so passing getMaxRows() as the first argument means 'after the last existing row.' If you accidentally pass getMaxRows() + 1 you'll get a different coordinates error in the opposite direction. Keep the anchor at getMaxRows(), not beyond it.

Sizing the range from the data, not a hardcoded constant

A separate, quieter cause of this error is passing a literal row or column count that once matched your data but drifted. getRange(1, 1, 500, 10) worked when the export was exactly 500 rows; it breaks the moment the source returns 501. The pattern in the snippet — deriving numRows and numCols from data.length and data[0].length — means the range always matches the payload. The expand check then guarantees the sheet can hold it.

If your data source can return zero rows, guard against data[0] being undefined before reading data[0].length. A zero-row write is a no-op, so the right move is to return early: if (data.length === 0) return. Calling getRange with numRows = 0 throws its own error, distinct from the coordinates one.

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

Why does this error appear only sometimes, not on every run?: The sheet's row count changes. deleteRows(), sheet duplication, or manually deleting a block of rows all lower getMaxRows(). A script that wrote 800 rows into a 1000-row sheet worked fine; after someone trimmed the sheet to 750 rows, the same script fails. Check getMaxRows() at runtime, not at deploy time.
Can I just call sheet.clearContents() and setValues() without inserting rows?: clearContents() removes cell values but does not shrink getMaxRows(). So if the sheet already has enough rows from a previous run, clearContents() plus setValues() works. If the sheet was trimmed or is brand-new and smaller than your data, you still need the expand step. The safe pattern is: expand if needed, then write — regardless of whether you cleared first.
Does spreadsheetApp.insertRowsBefore() work instead of insertRowsAfter()?: It works but inserts at the top, shifting existing data down. If you're writing to row 1 and the sheet has headers or prior data you want to preserve, insertRowsBefore() breaks that layout. insertRowsAfter(getMaxRows(), delta) appends cleanly at the bottom without disturbing what's already there.
What if I want to write starting at a row other than row 1?: The expand check needs to account for the offset. If you're writing at startRow, the sheet needs at least startRow + numRows - 1 rows total. Change the condition to: if ((startRow + numRows - 1) > sheet.getMaxRows()), then insert the difference. The same logic applies to column offsets.

// one good script a week