The Problem
You're running your Google Apps Script, everything seems fine, and then—boom. A red error message stops you in your tracks:
Exception: The coordinates or dimensions of the range are invalid.
This error is Google's way of saying: "I can't find the cell you're looking for." In plain English, you've asked the SpreadsheetApp service to look at a cell or range that doesn't exist on the grid. Unlike standard JavaScript arrays where an out-of-bounds index quietly returns undefined, Apps Script throws a hard exception that kills your script immediately.
TL;DR: The Quick Fix
Check the numbers inside your getRange() call. Most likely, one of these two things went wrong:
- You used a 0 or a negative number. Google Sheets is 1-indexed. The first cell is always (1, 1), not (0, 0).
- You asked for too much. You're trying to grab row 1,001 on a sheet that only has 1,000 rows total.
Why is this happening?
1. The "Zero Index" Trap
As developers, we're hardwired to start counting at 0. JavaScript arrays start at data[0], but Google Sheets starts at 1. If you use a loop index i directly in your getRange call, your script will crash the moment it hits the first iteration.
// ❌ ERROR: Loops start at 0, but Sheets starts at 1
for (var i = 0; i < data.length; i++) {
sheet.getRange(i, 1).setValue(data[i]);
}
The Fix: Always use i + 1 when converting an array index to a sheet coordinate.
2. The Empty Data Ghost
If you're writing data dynamically, you probably use data.length to define how many rows to fill. If your data source returns an empty array, getRange(1, 1, 0, 1) will fail because a range cannot have a height of 0. Even if you have the right starting coordinates, a zero-length dimension is illegal.
var values = getExternalData(); // Imagine this returns []
// ❌ ERROR: values.length is 0
sheet.getRange(1, 1, values.length, 1).setValues(values);
3. getLastRow() Confusion
Using getLastRow() + 1 is the standard way to find the next empty row. However, if a sheet is brand new and completely empty, getLastRow() returns 0. While getRange(0 + 1, 1) works, more complex logic—like calculating offsets—often results in a 0 being passed to a column or dimension parameter, triggering the error.
4. Hitting the Edge of the World
If you try to access getRange(1, 1, 1, 30) on a sheet that only goes from Column A to Z (26 columns), the script fails. Apps Script is not "elastic" by default; it won't automatically create Column AC just because you asked for it. You have to build the space first.
Proven Fixes
Method 1: Sanitize with Math.max()
Don't trust your calculations blindly. Use Math.max(1, yourVariable) to ensure you never pass a 0 or negative number to Google.
var targetRow = calculateNextRow();
// Safety net: forces the row to be at least 1
sheet.getRange(Math.max(1, targetRow), 1).activate();
Method 2: Guard Clauses for Dynamic Data
Always wrap your range operations in an if statement if the data size can vary. This prevents the script from trying to process "nothing."
if (data && data.length > 0) {
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
} else {
console.warn("Data was empty. Skipping sheet write to prevent crash.");
}
Method 3: The "Safe Range" Helper
If you're building a tool where users might import varying amounts of data, use a helper function to expand the sheet dimensions automatically before grabbing the range.
function getRangeAndExpand(sheet, row, col, numRows, numCols) {
var maxR = sheet.getMaxRows();
var maxC = sheet.getMaxColumns();
// Check if we need more rows
if (row + numRows - 1 > maxR) {
sheet.insertRowsAfter(maxR, (row + numRows - 1) - maxR);
}
// Check if we need more columns
if (col + numCols - 1 > maxC) {
sheet.insertColumnsAfter(maxC, (col + numCols - 1) - maxC);
}
return sheet.getRange(row, col, numRows, numCols);
}
Verification Checklist
To pinpoint the exact cause, add a log line immediately before the crash. Open the Executions tab in the Apps Script editor to see the output.
console.log(`Target: Row ${r}, Col ${c} | Dimensions: ${h}x${w}`);
sheet.getRange(r, c, h, w);
Look for any values that are 0, negative, or exceed the numbers shown in sheet.getMaxRows(). If your logs say you're requesting Row 1005 on a 1000-row sheet, you've found your culprit.