GAS AI BuilderApps Script Assistant Sign in

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

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 project

More 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.