Home / Web Development

Google Sheets as a Backend Database: The Honest Guide (Apps Script Edition)

By Mr. Nun Post a Comment


So, here's a confession. The first "real" app I ever shipped didn't have a real database. No Postgres, no Firebase, no fancy ORM. It had a spreadsheet. A very messy, color-coded, slightly emotional spreadsheet that I opened at 2am to fix a typo in a customer's name.

And you know what? It worked. It ran for almost fourteen months before we outgrew it.

That's the thing nobody really tells beginner devs: using Google Sheets as a backend database is not a joke setup. It's a legit, boring-in-a-good-way solution for small tools, internal dashboards, MVPs, and those little side projects that only need to serve fifty people a day. The magic glue is Google Apps Script, and once you get it, you'll start seeing use cases everywhere.

Let's talk about how it actually works, where it breaks, and how to not embarass yourself with a data leak on day three.

Why Would Anyone Use a Spreadsheet as a Database?

Fair question. Let me answer it the way I'd answer a friend over es kopi susu.

Most side projects die not because the code is bad, but because setting things up takes too long. You want to build a simple order form for your friend's kue kering business. Suddenly you're comparing database hosting plans, writing migration files, and configuring environment variables at 11pm. The project loses momentum and dies in the ~/projects graveyard.

A spreadsheet skips all of that. It's already hosted, already backed up, already collaborative, and — this is the underrated part — already familiar to non-technical people.

The real advantages

  • Zero infrastructure cost. Free with any Google account.
  • Instant admin panel. Your client can edit rows directly. No CRUD dashboard needed. This alone saves you weeks.
  • Built-in versioning. File → Version history is basically a poor man's audit log.
  • Native integrations. Looker Studio, Google Forms, Gmail, Calendar — all one line of script away.
  • Human-readable data. Debugging is literally just... looking at it.

And the parts that hurt

  • No real concurrency control unless you write it yourself.
  • Roughly 10 million cells per spreadsheet — sounds huge, isn't infinite.
  • Apps Script has daily quotas and a 6-minute execution limit per run.
  • Slow reads compared to a proper DB. We're talking hundreds of miliseconds, not single digits.
  • Zero relational integrity. Foreign keys? Never heard of her.

So: great for a warung, terrible for a supermarket chain. Know which one you're building.

Understanding the Apps Script Layer

Google Apps Script is basically JavaScript that runs on Google's servers with special access to your Workspace files. If you know modern JS, you already know 85% of it. The other 15% is learning the service objects like SpreadsheetApp, DriveApp, and MailApp.

The killer feature for our purpose is the Web App deployment. You write two special functions — doGet(e) and doPost(e) — deploy the script, and Google hands you a public HTTPS endpoint. That endpoint is your API. No server, no Docker, no domain, no SSL certificate to renew.

It's serverless before serverless was cool.

Where the code lives

You have two options. Container-bound scripts live inside a specific spreadsheet (Extensions → Apps Script). Standalone scripts live in Drive on their own and connect to sheets by ID.

My take: use container-bound for quick internal automations, and standalone once the project has more than one sheet or you want to version it properly with clasp and Git. Standalone scripts are much easier to keep in a repo, and future-you will be greatful.

Setting Up Your First Sheet-Backed API

Let's build something concrete: a simple customer feedback store for a small F&B business in Bandung. It needs to accept submissions and return a list for the owner's dashboard.

Step 1: Design your sheet like an actual table

This is the step everyone rushes and everyone regrets. Treat row 1 as your schema. No merged cells, no blank spacer rows, no cute headers spanning three columns.

For our example:

id | timestamp | outlet | rating | comment | status

Name the tab something predictable like feedback. Then freeze row 1 so it doesn't wander off during scrolling.

One more tip: add an id column and generate a UUID for every row. Row numbers change when someone sorts or deletes, and if your app relies on row position, one accidental sort will ruin your entire dataset. Ask me how I know.

Step 2: Write the read endpoint

Open Extensions → Apps Script and drop this in:

const SHEET_ID = 'your-spreadsheet-id-here';
const TAB = 'feedback';

function getSheet() {
  return SpreadsheetApp.openById(SHEET_ID).getSheetByName(TAB);
}

function rowsToObjects(values) {
  const [header, ...rows] = values;
  return rows.map(r => {
    const obj = {};
    header.forEach((key, i) => obj[key] = r[i]);
    return obj;
  });
}

function doGet(e) {
  const data = rowsToObjects(getSheet().getDataRange().getValues());
  return ContentService
    .createTextOutput(JSON.stringify({ ok: true, data }))
    .setMimeType(ContentService.MimeType.JSON);
}

Deploy it: Deploy → New deployment → Web app. Set "Execute as: Me" and "Who has access: Anyone". Copy the URL. Paste it in a browser. You should see JSON.

That's it. You have a REST-ish API. It took about four minutes.

Step 3: Write the write endpoint

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.waitLock(20000);
  try {
    const body = JSON.parse(e.postData.contents);
    const sheet = getSheet();
    sheet.appendRow([
      Utilities.getUuid(),
      new Date(),
      body.outlet,
      body.rating,
      body.comment,
      'new'
    ]);
    return ContentService
      .createTextOutput(JSON.stringify({ ok: true }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService
      .createTextOutput(JSON.stringify({ ok: false, error: String(err) }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

Please don't skip LockService

I'm putting this in its own heading because it matters that much. Apps Script can run multiple instances of your function at the same time. Without a lock, two simultaneous submissions can write to the same row and one just... vanishes. LockService forces them to queue politely.

It costs you three lines. Silent data loss costs you a client.

Performance: How to Not Make It Painfully Slow

Here's the single biggest mistake I see in Apps Script code: calling the Sheets service inside a loop.

// DON'T
for (let i = 0; i < 500; i++) {
  sheet.getRange(i + 2, 3).setValue(prices[i]);
}

// DO
sheet.getRange(2, 3, 500, 1).setValues(prices.map(p => [p]));

Every getRange().setValue() is a network round trip to Google's servers. Five hundred of them will blow past your 6-minute limit. One batched setValues() finishes in under a second. The rule is simple: read once, process in memory, write once.

Cache aggressively

If your data doesn't change every second — and it almost never does — wrap reads in CacheService:

function getCachedData() {
  const cache = CacheService.getScriptCache();
  const hit = cache.get('feedback_all');
  if (hit) return JSON.parse(hit);

  const data = rowsToObjects(getSheet().getDataRange().getValues());
  cache.put('feedback_all', JSON.stringify(data), 300); // 5 minutes
  return data;
}

Response times drop from ~1.5s to ~200ms. Your users will feel it immediately. Just remember to invalidate the cache after writes, otherwise people will submit data and swear it didn't save.

Know your quotas

Consumer Gmail accounts get roughly 90 minutes of script runtime per day and 20,000 URL Fetch calls. Workspace accounts get significantly more. For a tool serving a few hundred requests daily, you won't come close. For anything public-facing and viral, you absolutely will.

Security: The Part People Genuinely Mess Up

When you deploy with "Anyone" access and "Execute as: Me", your script runs with your permissions. Anyone who finds that URL can hit your endpoint. It's an unauthenticated public API into your Drive.

Please don't just hope nobody guesses the URL.

Minimum viable protection

  1. Shared secret token. Require a token in the request and store the real one in Script Properties (Project Settings → Script Properties), never hardcoded in the file.
  2. Validate everything. Whitelist allowed fields. Reject unexpected keys. Check that rating is actually a number between 1 and 5.
  3. Never expose a delete-all endpoint. Sounds obvious. Happens weekly.
  4. Separate sheets by sensitivity. Don't put your customer phone numbers in the same spreadsheet your public API reads from.
function isAuthorized(e) {
  const expected = PropertiesService
    .getScriptProperties()
    .getProperty('API_TOKEN');
  return e.parameter.token === expected;
}

Also worth knowing: for anything involving Indonesian consumer data, UU PDP (Undang-Undang Perlindungan Data Pribadi No. 27/2022) now applies real obligations around consent and data handling. A spreadsheet full of NIK numbers with a public endpoint is not a risk you want to carry.

A Real Indonesian Use Case That Actually Worked

A friend of mine runs a small distro brand out of Jogja. Twelve resellers across Java, each reporting daily sales through WhatsApp. Chaos. Screenshots. Missing numbers. The classic.

We built this in one weekend:

  • A Google Form for daily reseller submissions, feeding straight into Sheets.
  • An Apps Script trigger running every night at 21:00 WIB, aggregating totals per reseller.
  • A summary email to the owner via MailApp, plus a Telegram notification through UrlFetchApp.
  • A Looker Studio dashboard connected directly to the sheet, shared read-only.

Total cost: nol rupiah. Total dev time: maybe eleven hours. It's still running today, roughly two years later, and it handles about 400 rows a month without complaining.

Could we have built it on Next.js with Supabase? Sure. Would it have been better? For that owner — who edits the sheet herself when a reseller makes a typo — honestly, no.

When You Should Absolutely Migrate

Sheets is a great starting point, not a forever home. Move to a real database when:

  • You pass roughly 50,000 rows and reads start feeling sluggish.
  • You need concurrent writes from more than ~20 active users.
  • You need relational queries — joins, aggregates, anything beyond filter-and-map.
  • You're storing sensitive personal or financial data.
  • Response time under 300ms is a business requirement, not a nice-to-have.

The nice thing is migration is usually painless. Export CSV, import to Postgres, rewrite your fetch calls. If you kept your data access in one module instead of scattering fetch() everywhere, you'll swap it in an afternoon.

Tools Worth Knowing About

A few things I've genuinely used and would recommend, in the spirit of being honest rather than salesy.

clasp (free, official)

Pros: Lets you edit Apps Script locally in VS Code, use TypeScript, and commit to Git. Once you've used it, the browser editor feels like typing with mittens on.
Cons: Setup has some auth friction the first time, and the docs assume you already know Node tooling.

SheetDB / Sheety (freemium)

Pros: Turns any sheet into a REST API in about ninety seconds with no code at all. Great for frontend-only devs and for prototyping in a client meeting.
Cons: Free tiers are tight (a few hundred requests per month), and you're adding a third party between you and your own data. Also less flexible than writing your own doPost. [AFFILIATE LINK PLACEHOLDER]

Any solid Apps Script course

If you learn better with structure than with scattered docs, a focused Apps Script automation course is one of the higher-ROI things a non-developer can buy. Pros: you'll cover triggers, quotas, and error handling in a week instead of discovering them through production bugs. Cons: most of the content genuinely does exist free on YouTube — you're paying for sequencing, not secrets. [AFFILIATE LINK PLACEHOLDER]

Common Mistakes (A Short, Painful List)

  • Relying on row numbers as IDs. Someone will sort the sheet. It's inevitable.
  • Forgetting timezone settings — Apps Script defaults may not be WIB. Fix it in appsscript.json.
  • Not handling the case where the sheet is empty. getDataRange() on a blank sheet returns weird results.
  • Editing the deployed version instead of creating a new deployment, then wondering why nothing changed.
  • Storing API keys in the script file, then sharing the project publicly. Use Script Properties.

Wrapping Up

Using Google Sheets as a backend database isn't the "professional" choice, and honestly I think that's fine. Not everything needs to scale to a million users. Sometimes the right answer is the thing you can ship on Saturday and hand to a client on Monday, with an admin panel they already know how to use.

Start small. Respect the limits. Lock your writes, batch your reads, protect your endpoint, and migrate without drama when the numbers tell you it's time.

Your next steps

  1. Make a copy of any existing spreadsheet you're already maintaining manually.
  2. Clean row 1 into a proper header, add an id column.
  3. Paste the doGet snippet above and deploy it as a web app.
  4. Fetch it from a plain HTML page. Seeing your own data come back as JSON is weirdly motivating.
  5. Add LockService and a token check before you show anyone the URL.

Build the small thing. Ship it. You can always add Postgres later — and if you never need to, that's not failure, that's just good scoping.

Post a Comment for "Google Sheets as a Backend Database: The Honest Guide (Apps Script Edition)"

Post a Comment