Update or delete a row by its Id
Apps Script recipes › Sheets as a database
Finds the row by the Id column with TextFinder and writes the changed fields in one setValues call.
File Code.gs
/** Changes the fields of the row whose Id is `id`; returns false when there is no such row. */
function updateRow_(sheetName, id, changes) {
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 idCol = header.indexOf('Id') + 1;
const cell = sheet.getRange(2, idCol, Math.max(1, sheet.getLastRow() - 1)).createTextFinder(String(id)).matchEntireCell(true).findNext();
if (!cell) return false;
const range = sheet.getRange(cell.getRow(), 1, 1, header.length);
const values = range.getValues()[0];
header.forEach((h, i) => { if (h in changes && h !== 'Id') values[i] = changes[h]; });
range.setValues([values]);
return true;
} finally {
lock.releaseLock();
}
}
/** Deletes the row whose Id is `id`; returns false when there is no such row. */
function deleteRow_(sheetName, id) {
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 idCol = header.indexOf('Id') + 1;
const cell = sheet.getRange(2, idCol, Math.max(1, sheet.getLastRow() - 1)).createTextFinder(String(id)).matchEntireCell(true).findNext();
if (cell) sheet.deleteRow(cell.getRow());
return !!cell;
} finally {
lock.releaseLock();
}
}
Good to know
- Only fields named in the change are written; other cells keep their values (and their formulas).
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
Add a row safely when many people save at onceLockService keeps two saves from writing the same row; the row gets an Id and the time it was made.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.