How to Use Google Sheets as a Database (with Apps Script)

Google Sheets isn’t a database. But for prototypes, internal tools, small businesses, and automations under 50K rows — it works surprisingly well.

This guide shows you how to use Google Sheets as a structured data store with Apps Script, including CRUD operations, web API endpoints, and practical limits.

When Google Sheets Makes Sense as a Database

Good fit:

• Internal tools with <10 concurrent users
• Prototyping before building a real backend
• Data entry by non-technical team members
• Contact lists, inventory, task tracking under 50K rows
• Automations that run on a schedule (not real-time)

Bad fit:

• High-concurrency applications (>10 simultaneous writes)
• Datasets over 100K rows (performance degrades)
• Applications requiring ACID transactions
• Real-time data with sub-second requirements

Setting Up Your Sheet as a Database

Structure Your Data Like a Table

Use Row 1 as headers. Each column is a field, each row is a record:

id	name	email	status	created_at
1	Alice	[email protected]	active	2026-01-15
2	Bob	[email protected]	inactive	2026-01-20

Rules:

1. First column should be a unique ID
2. Use consistent data types per column
3. No merged cells, no blank rows in the middle
4. Keep headers simple — no spaces (use created_at not Created At)

Open Apps Script

From your Google Sheet: Extensions > Apps Script

This opens the Apps Script editor where you’ll write your database functions.

CRUD Operations with Apps Script

Read: Get All Records

function getAllRecords() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

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

Read: Find by ID

function findById(id) {
  const records = getAllRecords();
  return records.find(r => r.id === id) || null;
}

Read: Query with Filters

function queryRecords(filters) {
  const records = getAllRecords();

  return records.filter(record => {
    return Object.entries(filters).every(([key, value]) => {
      return record[key] === value;
    });
  });
}

// Usage: queryRecords({ status: 'active' })

Create: Add a New Record

function createRecord(data) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  // Auto-generate ID
  const lastRow = sheet.getLastRow();
  const lastId = lastRow > 1 ? sheet.getRange(lastRow, 1).getValue() : 0;
  data.id = lastId + 1;
  data.created_at = new Date().toISOString();

  // Build row in correct column order
  const row = headers.map(header => data[header] || '');

  sheet.appendRow(row);
  return data;
}

Update: Modify an Existing Record

function updateRecord(id, updates) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  // Find row index (1-based, +1 for header)
  const rowIndex = data.findIndex((row, i) => i > 0 && row[0] === id);

  if (rowIndex === -1) return null;

  // Apply updates
  headers.forEach((header, colIndex) => {
    if (updates[header] !== undefined) {
      sheet.getRange(rowIndex + 1, colIndex + 1).setValue(updates[header]);
    }
  });

  return findById(id);
}

Delete: Remove a Record

function deleteRecord(id) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();

  const rowIndex = data.findIndex((row, i) => i > 0 && row[0] === id);

  if (rowIndex === -1) return false;

  sheet.deleteRow(rowIndex + 1);
  return true;
}

Expose as a Web API

Apps Script can serve as a REST-like API endpoint using doGet and doPost.

Deploy as Web App

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

  switch (action) {
    case 'getAll':
      result = getAllRecords();
      break;
    case 'find':
      result = findById(Number(e.parameter.id));
      break;
    case 'query':
      const filters = JSON.parse(e.parameter.filters || '{}');
      result = queryRecords(filters);
      break;
    default:
      result = { error: 'Unknown action' };
  }

  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  const body = JSON.parse(e.postData.contents);
  const action = body.action;
  let result;

  switch (action) {
    case 'create':
      result = createRecord(body.data);
      break;
    case 'update':
      result = updateRecord(body.id, body.data);
      break;
    case 'delete':
      result = deleteRecord(body.id);
      break;
    default:
      result = { error: 'Unknown action' };
  }

  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

Deploy Steps

1. Click Deploy > New deployment
2. Select Web app
3. Set Execute as: Me
4. Set Who has access: Anyone (or specific users)
5. Click Deploy and copy the URL

Call the API

// GET all records
fetch('YOUR_WEBAPP_URL?action=getAll')
  .then(r => r.json())
  .then(data => console.log(data));

// POST: create a record
fetch('YOUR_WEBAPP_URL', {
  method: 'POST',
  body: JSON.stringify({
    action: 'create',
    data: { name: 'Charlie', email: '[email protected]', status: 'active' }
  })
});

Performance Tips

Batch Operations

Reading/writing cell by cell is slow. Always batch:

// Slow — reads one cell at a time
for (let i = 2; i <= lastRow; i++) {
  const name = sheet.getRange(i, 2).getValue();
}

// Fast — reads everything at once
const allData = sheet.getDataRange().getValues();
allData.forEach(row => {
  const name = row[1];
});

Use Cache for Read-Heavy Workloads

function getCachedRecords() {
  const cache = CacheService.getScriptCache();
  const cached = cache.get('allRecords');

  if (cached) return JSON.parse(cached);

  const records = getAllRecords();
  cache.put('allRecords', JSON.stringify(records), 300); // 5 min TTL
  return records;
}

Indexing with Named Ranges

For frequently queried columns, create a lookup map:

function buildIndex(columnName) {
  const records = getAllRecords();
  const index = {};

  records.forEach((record, i) => {
    const key = record[columnName];
    if (!index[key]) index[key] = [];
    index[key].push(record);
  });

  return index;
}

// Usage
const statusIndex = buildIndex('status');
const activeUsers = statusIndex['active']; // Instant lookup

Limitations to Know

Limitation	Value
Max cells per spreadsheet	10 million
Max rows per sheet	~5 million (but slow past 50K)
API quota (Apps Script)	20,000 calls/day (consumer)
Web app execution time	6 minutes max
Concurrent users (reliable)	~10
Response time	500ms–3s per request

When to Graduate to a Real Database

Move to a proper database (Supabase, Firebase, PostgreSQL) when:

• You exceed 50K rows and queries slow down
• You need more than 10 concurrent users
• You need real-time updates (sub-100ms)
• You need relational queries (JOINs)
• You need transactions or rollback
• You’re building a production app for customers

Practical Example: Contact Management System

Here’s a complete mini-CRM in Google Sheets:

Sheet structure (Contacts tab):

id	name	email	company	status	last_contacted	notes

Apps Script functions:

function addContact(name, email, company) {
  return createRecord({
    name: name,
    email: email,
    company: company,
    status: 'new',
    last_contacted: '',
    notes: ''
  });
}

function markContacted(id) {
  return updateRecord(id, {
    last_contacted: new Date().toISOString(),
    status: 'contacted'
  });
}

function getStaleContacts(daysSinceContact) {
  const records = getAllRecords();
  const cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - daysSinceContact);

  return records.filter(r => {
    if (!r.last_contacted) return true;
    return new Date(r.last_contacted) < cutoff;
  });
}

This gives you a functional CRM that non-technical team members can view and edit directly in Sheets, while your Apps Script handles the business logic.

Related Resources

• Google Sheets Productivity Tips — boost your Sheets workflow
• Google Sheets Templates — pre-built templates for business use
• Smart Email Verifier Add-on — verify emails directly in Sheets