Google Sheets as a Simple Database with Apps Script

Google Sheets isn't a real database, but for small to medium datasets it works remarkably well as a lightweight data store — especially when combined with Apps Script for CRUD operations.

Sheet structure

Treat row 1 as your header row (column names). Each subsequent row is a record. Add an ID column (column A) to uniquely identify rows.

A: ID	B: Name	C: Email	D: Status	E: Created At

Helper: get the sheet

function getDb(sheetName = 'Database') {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
}

function getHeaders(sheet) {
  return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
}

CREATE — insert a new row

function createRecord(data) {
  const sheet = getDb();
  const id = Utilities.getUuid(); // Unique ID
  const now = new Date();

  sheet.appendRow([id, data.name, data.email, data.status || 'Active', now]);
  Logger.log('Created record: ' + id);
  return id;
}

// Usage:
// createRecord({ name: 'Alice', email: '[email protected]' });

READ — get all records as objects

function getAllRecords() {
  const sheet = getDb();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  return data.slice(1).map(row => {
    const obj = {};
    headers.forEach((h, i) => { obj[h] = row[i]; });
    return obj;
  });
}

// Usage:
// const records = getAllRecords();
// Logger.log(records[0].Name);

READ — find a record by ID

function findById(id) {
  const sheet = getDb();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const idCol = headers.indexOf('ID');

  const row = data.find((r, i) => i > 0 && r[idCol] === id);
  if (!row) return null;

  const obj = {};
  headers.forEach((h, i) => { obj[h] = row[i]; });
  return obj;
}

UPDATE — modify a record by ID

function updateRecord(id, updates) {
  const sheet = getDb();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const idCol = headers.indexOf('ID');

  for (let i = 1; i < data.length; i++) {
    if (data[i][idCol] === id) {
      Object.entries(updates).forEach(([key, value]) => {
        const col = headers.indexOf(key);
        if (col !== -1) {
          sheet.getRange(i + 1, col + 1).setValue(value);
        }
      });
      Logger.log('Updated record: ' + id);
      return true;
    }
  }

  Logger.log('Record not found: ' + id);
  return false;
}

// Usage:
// updateRecord('some-uuid', { Status: 'Inactive' });

DELETE — remove a record by ID

function deleteRecord(id) {
  const sheet = getDb();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const idCol = headers.indexOf('ID');

  for (let i = 1; i < data.length; i++) {
    if (data[i][idCol] === id) {
      sheet.deleteRow(i + 1);
      Logger.log('Deleted record: ' + id);
      return true;
    }
  }

  Logger.log('Record not found: ' + id);
  return false;
}

QUERY — filter records

function queryRecords(filterFn) {
  return getAllRecords().filter(filterFn);
}

// Usage: find all active users
// const active = queryRecords(r => r.Status === 'Active');

Expose as a Web App API

function doGet(e) {
  const action = e.parameter.action;

  if (action === 'getAll') {
    const records = getAllRecords();
    return ContentService
      .createTextOutput(JSON.stringify(records))
      .setMimeType(ContentService.MimeType.JSON);
  }

  return ContentService.createTextOutput(JSON.stringify({ error: 'Unknown action' }))
    .setMimeType(ContentService.MimeType.JSON);
}

Tips

sheet.appendRow() is simple but slow for bulk inserts. Use sheet.getRange(...).setValues() for batch writes.
Google Sheets has a limit of 10 million cells. For high-volume data, consider Firestore or a real database.
Avoid frequent reads/writes in loops — cache the full sheet data in a variable and operate on that array instead.
Use LockService if multiple users might write simultaneously to prevent race conditions.