Will appendRow overwrite an existing row if two form submissions arrive at exactly the same time?

No. appendRow is atomic on the server side — Sheets resolves the race and each call lands in its own row. That guarantee is the main reason to prefer it in form-response triggers over the getLastRow() + setValues pattern.

Why does setValues throw 'number of columns does not match the range'?

Your data array is jagged. Every inner array must have the same length, equal to the column count you passed to getRange(). Pad short rows with empty strings: row.concat(Array(targetCols - row.length).fill('')).

Can I use setValues in an onFormSubmit trigger safely?

Only if you wrap the getLastRow() read and the setValues write in a LockService.getScriptLock() block. Without the lock, two concurrent triggers compute the same target row and one clobbers the other. appendRow is simpler and safer for that use case.

How many rows can setValues write in one call before hitting a limit?

There is no hard row-count cap on the call itself, but the payload size limit is 10 MB per request and total cell count per sheet is 10 million. In practice, writing more than ~50,000 rows in a single setValues call risks a timeout before the HTTP response completes; chunk large imports into batches of 5,000–10,000 rows with a Utilities.sleep(500) between chunks to stay within execution time limits.

// Sheets · Apps Script

appendRow vs setValues for adding rows in Google Sheets.

When to use appendRow (safe under concurrent writes, slower) vs setValues (fast batch inserts, race-prone) in Google Apps Script — with a working example of each pattern.

“I want to add rows to a Google Sheet from Apps Script without losing data or hitting quota errors, and I'm not sure whether to use appendRow or setValues.”

The script

copy · paste · trigger

append_vs_setvalues.gs

Apps Script

// appendRow: safe for concurrent form submissions
// setValues: fast path for bulk imports (no concurrency)

function safeAppend(sheet, rowData) {
  // appendRow finds the first empty row atomically
  sheet.appendRow(rowData);
}

function bulkInsert(sheet, rows) {
  // setValues writes all rows in one API call
  var startRow = sheet.getLastRow() + 1;
  var numCols = rows[0].length;
  sheet.getRange(startRow, 1, rows.length, numCols)
       .setValues(rows);
}

function demo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Responses');

  safeAppend(sheet, ['Alice', '[email protected]', new Date()]);

  var batch = [['Bob', '[email protected]', new Date()],
               ['Carol', '[email protected]', new Date()]];
  bulkInsert(sheet, batch);
}

Generate yours

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

Walkthrough

How appendRow keeps concurrent writes safe

appendRow does one thing that setValues cannot: it locates the first empty row and writes to it as a single atomic operation. That matters the moment two executions can overlap — a Google Form trigger is the clearest case. Two submissions arrive within the same second, both fire onFormSubmit, and both call getLastRow(). They read the same value, compute the same target row, and one overwrites the other. appendRow sidesteps that by letting the Sheets back end resolve the race.

The cost is one API call per row. If you're writing inside an onFormSubmit trigger that fires for a single response, that's fine. The execution budget for a simple trigger is six minutes, and a single appendRow typically takes under a second. Where appendRow starts to hurt is inside a loop: calling it 500 times in a migration script will crawl, and you'll likely hit the 30-second script runtime ceiling before you're done.

One practical note: appendRow always appends after the last row that contains any data, including rows with formulas or invisible whitespace. If your sheet has a stray space in row 200, your new row lands at 201, not where you expected. I keep a dedicated responses sheet with no trailing noise for exactly this reason.

When setValues is the right call

setValues writes a two-dimensional array to a pre-specified range in a single Sheets API call. Writing 1,000 rows takes roughly the same wall-clock time as writing 10, because the round-trip cost dominates. For batch imports — syncing a CSV, backfilling a report, seeding a sheet from an external API — setValues is the correct tool and appendRow is the wrong one.

The race condition is real but avoidable when you control the execution context. A time-based trigger that runs once every five minutes, a manually invoked migration function, an appsscript.json-scheduled nightly job: none of these overlap with form submissions or with each other (assuming you've confirmed no parallel executions). In those cases, getLastRow() + 1 as your start row is safe, and you get the full throughput benefit.

One detail that bites people: setValues requires that your 2D array is perfectly rectangular. Jagged rows — where row 2 has three columns and row 3 has four — throw a 'The number of columns in the data does not match the number of columns in the range' error. Pad shorter rows with empty strings before calling setValues.

Quota reality and when to batch anyway

Apps Script enforces a daily Sheets API quota of 20,000 read and write operations for consumer accounts (50,000 for Workspace). appendRow burns one operation per row; setValues burns one operation per call regardless of row count. A form that receives 500 submissions per day is fine on either method. A nightly sync that writes 5,000 rows should use setValues or it will consume 25% of the daily quota on a single job.

There's a middle path for high-frequency form triggers that still need throughput: batch with a lock. Acquire LockService.getScriptLock(), read getLastRow() once, write all pending rows with setValues, release the lock. This is more code than appendRow and the lock has a ten-second timeout, so it only makes sense if you're genuinely hitting the per-row overhead at scale. Most form-response handlers don't need it; appendRow is the right default until proven otherwise.

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

Will appendRow overwrite an existing row if two form submissions arrive at exactly the same time?: No. appendRow is atomic on the server side — Sheets resolves the race and each call lands in its own row. That guarantee is the main reason to prefer it in form-response triggers over the getLastRow() + setValues pattern.
Why does setValues throw 'number of columns does not match the range'?: Your data array is jagged. Every inner array must have the same length, equal to the column count you passed to getRange(). Pad short rows with empty strings: row.concat(Array(targetCols - row.length).fill('')).
Can I use setValues in an onFormSubmit trigger safely?: Only if you wrap the getLastRow() read and the setValues write in a LockService.getScriptLock() block. Without the lock, two concurrent triggers compute the same target row and one clobbers the other. appendRow is simpler and safer for that use case.
How many rows can setValues write in one call before hitting a limit?: There is no hard row-count cap on the call itself, but the payload size limit is 10 MB per request and total cell count per sheet is 10 million. In practice, writing more than ~50,000 rows in a single setValues call risks a timeout before the HTTP response completes; chunk large imports into batches of 5,000–10,000 rows with a Utilities.sleep(500) between chunks to stay within execution time limits.

// one good script a week