Add a row safely when many people save at once
Apps Script recipes › Sheets as a database
LockService keeps two saves from writing the same row; the row gets an Id and the time it was made.
File Code.gs
/** Adds one row from an object; returns the new Id. */
function addRow_(sheetName, record) {
const lock = LockService.getScriptLock();
lock.waitLock(10000);
try {
const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(sheetName);
const header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const row = Object.assign({}, record, { Id: Utilities.getUuid().slice(0, 8), Created: new Date() });
sheet.appendRow(header.map(h => (h in row ? row[h] : '')));
return row.Id;
} finally {
lock.releaseLock();
}
}
/** Called from a form on the page: google.script.run.saveOrder(this) */
function saveOrder(form) {
if (!form.Customer) throw new Error('Fill in the customer.');
return addRow_('Orders', { Customer: form.Customer, Amount: Number(form.Amount) || 0 });
}
Good to know
- waitLock throws after 10 seconds when someone else holds the lock: the page shows the error and the person tries again.
- Fields are matched to the header row by name, so the order of columns in the sheet doesn't matter.
Let the AI put it in your appGAS AI Builder knows every recipe here. Describe your app and it uses this code where it fits, in a Google Apps Script project in your own Drive.
Use it in a projectMore in Sheets as a database
Write many rows at onceBuild a 2-D array and write it with one setValues call instead of one call per cell.A dropdown list from another sheetKeeps the choices in their own tab (Lists) and puts a dropdown on a column that follows that list.Make a sheet with its header oncesetup() creates the tabs the app needs with a bold, frozen header, and adds missing columns to tabs that exist.Read a sheet as a list of objectsOne getValues() call, the first row as field names, dates turned into text so google.script.run can return them.Update or delete a row by its IdFinds the row by the Id column with TextFinder and writes the changed fields in one setValues call.