Does appendRow guarantee that concurrent submissions cannot lose data?

The Apps Script reference does not document that guarantee. Coordinate cooperating writers with a shared lock, and treat manual edits and other script projects as separate concurrency risks.

Why does setValues reject a batch?

Every row must have the same non-zero number of columns, matching the destination range. An empty batch needs no write. The example validates this before touching the sheet.

Can I use setValues in an onFormSubmit handler?

Yes. When allocating a new destination row, acquire the shared lock before getLastRow() and keep it through setValues() and flush(). A Sheets form-submit event also provides e.range for the row already added by Google Forms; do not append a duplicate response to that same input sheet.

What batch size should I use?

There is no universal throughput promise here. Measure with your own spreadsheet and keep each execution within its runtime and service limits. Larger batches reduce service calls but can still time out.

// Sheets · Apps Script

appendRow vs setValues for adding rows in Google Sheets.

Choose appendRow for individual rows or setValues for batches in Google Apps Script. Coordinate overlapping script writes with a shared lock; neither is a transaction.

“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

// All cooperating writers in this script must use the same lock.
// This does not lock out people, other scripts, or external API writers.
function withSheetWriteLock(write) {
  var lock = LockService.getScriptLock();
  lock.waitLock(10000); // Throws on timeout: do not write without the lock.
  try {
    return write();
  } finally {
    try {
      SpreadsheetApp.flush();
    } finally {
      lock.releaseLock();
    }
  }
}

function appendOneRow(sheet, rowData) {
  if (!Array.isArray(rowData) || rowData.length === 0) {
    throw new Error('Provide a non-empty row.');
  }
  withSheetWriteLock(function () {
    sheet.appendRow(rowData);
  });
}

function bulkInsert(sheet, rows) {
  if (!Array.isArray(rows)) throw new Error('Provide an array of rows.');
  if (rows.length === 0) return;
  var numCols = Array.isArray(rows[0]) ? rows[0].length : 0;
  if (!numCols || rows.some(function (row) {
    return !Array.isArray(row) || row.length !== numCols;
  })) throw new Error('Rows must have the same non-zero column count.');

  withSheetWriteLock(function () {
    var startRow = sheet.getLastRow() + 1;
    sheet.getRange(startRow, 1, rows.length, numCols).setValues(rows);
  });
}

function demo() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  appendOneRow(sheet, ['Sample Alpha', 12]);
  bulkInsert(sheet, [['Sample Beta', 15], ['Sample Gamma', 6]]);
}

Generate yours

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

Walkthrough

Appending is not a documented concurrency guarantee

Google's Sheet.appendRow reference describes appending to the bottom of the current data region. It does not promise that concurrent callers cannot lose a write. Our earlier version claimed that guarantee; that claim was unsupported.

If two executions read the same last row before either writes, a getLastRow() + setValues() sequence can select the same destination. The example acquires a script lock before reading the destination or writing. Both entry points use the same lock, and pending spreadsheet changes are flushed before release.

A script lock coordinates only executions in the same script project that acquire that lock. It does not block manual edits, independent script projects, or external API requests. It provides neither rollback nor exactly-once delivery. A failed operation may need inspection before retrying.

Individual rows or rectangular batches

appendRow is convenient for one row. setValues writes a rectangular array to a chosen range and avoids making one service call for every row in a batch. Google recommends batching spreadsheet reads and writes; actual timing depends on the data and spreadsheet.

The batch example returns without touching the sheet for an empty array and rejects uneven row widths before acquiring the lock. It reads the destination row inside the lock. A scheduled trigger alone does not prevent another execution from overlapping.

Use a dedicated output tab and inspect its existing data before running the demo. Values beginning with an equals sign can be treated as formulas; do not pass untrusted input through this example without deciding how it should be stored. The demo adds fictional rows; it is not a repeat-safe import.

Runtime and verification limits

Google currently lists six minutes per ordinary script execution; simple triggers have a separate 30-second limit. The 20,000/50,000 daily read/write numbers are email quotas, not a published SpreadsheetApp write allowance. The earlier quota and fixed throughput claims on this page were incorrect.

The ten-second lock wait is a choice in this example, not a fixed LockService limit. If it times out, the write does not begin. Keep protected work short, handle failures, and check current Apps Script quotas before choosing a batch size.

This example has local fixture checks for empty and invalid batches, lock timeout, write failure, and lock release. It has not been qualified by concurrent executions in Google. Consult the official Sheet.appendRow, LockService, Lock.releaseLock, Apps Script quotas, and Best Practices references before adapting it to production.

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

Does appendRow guarantee that concurrent submissions cannot lose data?: The Apps Script reference does not document that guarantee. Coordinate cooperating writers with a shared lock, and treat manual edits and other script projects as separate concurrency risks.
Why does setValues reject a batch?: Every row must have the same non-zero number of columns, matching the destination range. An empty batch needs no write. The example validates this before touching the sheet.
Can I use setValues in an onFormSubmit handler?: Yes. When allocating a new destination row, acquire the shared lock before getLastRow() and keep it through setValues() and flush(). A Sheets form-submit event also provides e.range for the row already added by Google Forms; do not append a duplicate response to that same input sheet.
What batch size should I use?: There is no universal throughput promise here. Measure with your own spreadsheet and keep each execution within its runtime and service limits. Larger batches reduce service calls but can still time out.

// one good script a week