I made a small game for my daughter. It's called Počítadlo ("counter" in Czech), and it's a simple counting game that helps her learn the basics of math: adding and subtracting up to 20, ten questions, a timer and a trophy when she beats her record. It runs as a PWA on her iPad.

After a few days I wanted to know how she actually uses it. How often does she play, which levels, how long does it take her, where does she make mistakes? And I wanted it as an email to me and my wife every evening, because nobody is going to open a dashboard.

The obvious answer is a database, an API and some email service. For a kids' game that's way too much. So I used a Google Sheet and Apps Script. It costs nothing, there's no server, and it took one evening, most of which was me fighting Google's settings.

In this post, I'll walk you through:

  • how the app sends data to a Google Sheet
  • how the Sheet sends a daily email
  • the gotchas that cost me most of that evening

The idea

Every Google Sheet can have an Apps Script attached to it (Extensions → Apps Script). Apps Script is just JavaScript running on Google's servers, and it can do two things that matter here:

  1. Be deployed as a web app: you get a URL, and doPost(e) runs every time someone POSTs to it.
  2. Run functions on a schedule and send email with MailApp.

So the Sheet is the database, the script is the API, and Gmail is the email service. All free.

Receiving data

The script side is tiny. Parse the JSON, append rows:

function doPost(e) { const events = JSON.parse(e.postData.contents); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("events"); const rows = events.map((ev) => [new Date(), new Date(ev.ts), ev.type, ev.level, ev.seconds, ev.mistakes]); sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows); return ContentService.createTextOutput("ok"); }

Then Deploy → New deployment → Web app, Execute as: Me, Who has access: Anyone. You get a URL ending with /exec.

In the app, I send events like "app opened", "level started", "level finished in 53 seconds with one mistake". The call looks like this:

await fetch(URL, { method: "POST", mode: "no-cors", headers: { "Content-Type": "text/plain" }, body: JSON.stringify(events), });

Two things here are important:

  • text/plain, not application/json. A JSON content type makes the browser send a CORS preflight, and Apps Script can't answer it. With text/plain there's no preflight, and you still JSON.parse it on the other side.
  • no-cors means you can't read the response. A resolved fetch only tells you the request reached Google. Not that the script worked. Keep that in mind, it bit me later.

The iPad is not always online, so events go to a queue in localStorage first and get sent when there's a connection. Nothing gets lost when she plays in the car.

Sending the email

The report is a normal function. It reads the rows, counts games, best and average times per level, and lists the wrong answers. Then:

MailApp.sendEmail({ to: EMAILS, name: "Počítadlo", subject, htmlBody: html });

The name matters more than you'd think. Without it the email comes "from you", Gmail files it under Sent, and you think it never arrived. Ask me how I know.

For the schedule, you create a trigger:

function setup() { ScriptApp.newTrigger("check").timeBased().everyMinutes(15).create(); }

check does nothing before 19:00. After that, it sends the report once and remembers the date in PropertiesService. Why not just a daily trigger at 19:00? Read on.

The gotchas

This is where my evening went.

The trigger doesn't exist until you run setup by hand. Obvious in hindsight. Pasting the code creates nothing. You have to pick setup in the function dropdown and press Run. My first evening email never came because there was no trigger at all.

"At 19:00" means "sometime between 19:00 and 20:00". Google runs time triggers at any minute inside the window. A 15-minute check that sends on its first run after 19:00 gets you within 15 minutes, and if one run fails, the next one catches up.

403 "access denied" with access set to Anyone. The web app returned Google Drive's "you need access" page to everyone, even though the settings were right. It went away after I ran a function in the editor and approved the permissions. The script had simply never been authorized.

"Sorry, unable to open the file at this time." That's Apps Script telling you that you're logged into more than one Google account. Use a private window with only the account that owns the Sheet.

New deployment vs. new version. Deploy → New deployment gives you a new URL. To update the code and keep the URL, go to Manage deployments → Edit → Version: New version. The scheduled functions always run the latest saved code, so changes to the email need only a save.

Make it tell you it's alive. Since no-cors hides everything, I added a doGet that lists the triggers and when the last report went out. Opening the URL in a browser shows me whether the schedule exists.

Your hosting will play your game. The same evening there were app opens with no games in them. It was Vercel, opening every new deploy in a headless browser to take a screenshot for the dashboard. Now everything not running as the installed app gets a web- prefix on its session ID, and the email skips those. I don't drop them, just mark them. If the detection is ever wrong on the iPad, I still have the data.

Limits

This is not a real backend and I wouldn't pretend it is.

  • The web app URL is in your JavaScript, so anyone can POST to it. Validate what comes in. Worst case for me is junk rows in a spreadsheet.
  • A free Gmail account can send 100 emails a day with MailApp. For one report a day, fine.
  • It's slow and has quotas. For a few hundred rows a day from one iPad, I'll never notice. For your startup, use a real database.

Was it worth it?

Yes. For something like this, it's perfect. The whole thing is one script file, a spreadsheet I can open on my phone, and an email at 19:00. I already know she gets stuck on 20 − 7, and that she beat her Level 1 record in her second game of the evening.

If you have a small personal project that needs to store a bit of data or send you a summary, try a Sheet before you spin up a server. The code is on GitHub in the tracking folder.

What's your favourite "free backend" hack? Drop a comment.