CRUD Web App Sheets: Copy-Paste Apps Script Pattern

What this script does

Prerequisites

Walkthrough

Edge cases

How to test

Hardening for production

Variations

Full code: Contacts CRUD

Call listContacts/createContact/updateContact/deleteContact from HtmlService. Keep UUIDs as the client identity.

/**
 * Minimal CRUD API for a Contacts sheet, called from HtmlService UI.
 * Columns: A id, B name, C email, D phone, E updatedAt
 */
const CONTACT_SHEET = "Contacts";

function listContacts() {
  const sheet = sh_();
  const values = sheet.getDataRange().getValues();
  const out = [];
  for (let i = 1; i < values.length; i++) {
    if (!values[i][0]) continue;
    out.push({
      id: values[i][0],
      name: values[i][1],
      email: values[i][2],
      phone: values[i][3],
      updatedAt: values[i][4],
    });
  }
  return out;
}

function createContact(data) {
  const id = Utilities.getUuid();
  sh_().appendRow([id, data.name, data.email, data.phone || "", new Date()]);
  return { id: id };
}

function updateContact(id, data) {
  const row = findRowById_(id);
  if (!row) throw new Error("Not found");
  const sheet = sh_();
  sheet.getRange(row, 2, row, 5).setValues([[data.name, data.email, data.phone || "", new Date()]]);
  return { ok: true };
}

function deleteContact(id) {
  const row = findRowById_(id);
  if (!row) throw new Error("Not found");
  sh_().deleteRow(row);
  return { ok: true };
}

function sh_() {
  return SpreadsheetApp.getActive().getSheetByName(CONTACT_SHEET);
}

function findRowById_(id) {
  const values = sh_().getDataRange().getValues();
  for (let i = 1; i < values.length; i++) {
    if (values[i][0] === id) return i + 1;
  }
  return null;
}

1. Line 7: Returns plain objects for JSON-friendly script.run.
2. Line 25: Creates a stable primary key for each contact.
3. Line 26: Inserts a new contact at the bottom.
4. Line 31: Maps UUID to sheet row number.
5. Line 34: Updates name/email/phone/updatedAt in place.
6. Line 41: Removes the contact row after id lookup.