Google Apps Script: Automate Sheets and Gmail Without Running a Server

A tuition teacher with forty students, a small trader with a customer ledger, a freelancer chasing invoices: the monthly ritual is the same everywhere. Open the spreadsheet, scan down the column for who hasn’t paid, copy an address, write a message, send, repeat. Three hours, give or take, and two or three people still get missed. The work is entirely mechanical, which is the tell. Nothing in it needs a human, and nothing in it needs a server either. The spreadsheet you already have can send those emails itself. That’s the gap Google Apps Script fills, and it stays badly underused by exactly the people it would help most, largely because it gets filed mentally under programming rather than under spreadsheets. It runs on Google’s infrastructure, it’s included with a normal Google account, and there is nothing to install or keep patched.

what apps script actually is

Apps Script is a JavaScript runtime that Google hosts, with built-in access to Sheets, Gmail, Drive, Calendar, Docs and Forms through simple service objects. You write a function, Google runs it, either when you click Run or on a schedule you define. No machine of yours needs to be awake. That last part is the real advantage over everything else in this category: a home server can be sitting dead through a power cut while an Apps Script trigger fires on time anyway.

The trade is that you are working inside Google’s walls. You can call external APIs with UrlFetchApp, so it isn’t a closed box, but your automation lives in an account you don’t control the terms of, and there are daily quotas. For a monthly reminder job or a nightly report that trade is entirely reasonable. For anything I’d be upset to lose, I’d rather own the runtime, which is the argument I make in the n8n self-hosting guide.

getting to the editor

Open the spreadsheet you want to automate, then Extensions then Apps Script. A code editor opens in a new tab, already bound to that spreadsheet. Bound is the useful bit: SpreadsheetApp.getActive() refers to this sheet with no configuration, no API keys, no OAuth dance to set up yourself.

The first time you run anything that touches your data, Google shows an authorisation screen, and because your script isn’t a verified published app, it also shows a warning about an unverified developer. For a script you wrote, bound to your own sheet, that warning is expected. Read it, understand that you are granting your own code access to your own account, and continue.

a real script, not a hello world

Assume a sheet named Students with columns: name, email, fee due date, status, last reminded. This function walks the rows, finds anyone whose due date has passed, whose status isn’t Paid and who hasn’t been reminded in the last seven days, emails them, and writes back the date it did so.

function sendFeeReminders() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Students');
  const rows = sheet.getDataRange().getValues();
  const today = new Date();
  let sent = 0;

  // Row index 0 holds the headers, so begin at 1.
  for (let i = 1; i < rows.length; i++) {
    const name = rows[i][0];
    const email = rows[i][1];
    const dueRaw = rows[i][2];
    const status = rows[i][3];
    const lastReminded = rows[i][4];

    if (!email || status === 'Paid' || !dueRaw) continue;

    const due = new Date(dueRaw);
    if (due > today) continue;

    // Skip anyone reminded in the last 7 days, so the daily trigger, or a
    // re-run after a failure, doesn't email the same person again.
    if (lastReminded instanceof Date &&
        today.getTime() - lastReminded.getTime() < 7 * 24 * 60 * 60 * 1000) continue;

    MailApp.sendEmail({
      to: email,
      subject: 'Fee reminder for ' + name,
      body: 'Hello ' + name + ','
        + '\n\nOur records show the fee due on '
        + Utilities.formatDate(due, 'Asia/Kolkata', 'dd MMM yyyy')
        + ' is still pending. Please ignore this message if you have just paid.'
        + '\n\nThank you.'
    });

    // Column 5 is "last reminded"; getRange is 1-indexed, hence i + 1.
    // Store a real date (format column E as a date) so the 7-day check can read it.
    sheet.getRange(i + 1, 5).setValue(today);
    sent++;
  }

  Logger.log('Reminders sent: ' + sent);
}

Note Utilities.formatDate with an explicit Asia/Kolkata. Script projects carry their own timezone setting, and a project created from a different default will format and schedule against the wrong day boundary. Being explicit costs nothing and removes a whole category of confusing bug.

Run it once from the editor with a test row pointing at your own email address before you point it at real students. I’d go further: keep a column called status and set every real row to Paid during testing, so a logic mistake cannot email forty parents.

triggers, and the distinction that trips everyone

A script that only runs when you click Run isn’t automation. Triggers fix that, and there are two families of them with an important difference.

Type	Fires on	Can it send email or call an API?
Simple trigger (onOpen, onEdit)	Sheet opened or a cell edited	No, it runs without authorisation
Installable time-driven	A schedule you set	Yes
Installable on edit / on form submit	Edits or Form responses	Yes

This is a confusion that comes up often: “my onEdit function works but the email never sends”. A simple trigger runs unauthorised, so services needing permission are unavailable to it. You need an installable edit trigger for that, created from the Triggers panel in the editor or in code:

function createDailyTrigger() {
  ScriptApp.newTrigger('sendFeeReminders')
    .timeBased()
    .atHour(10)
    .everyDays(1)
    .inTimezone('Asia/Kolkata')
    .create();
}

Run createDailyTrigger once, never on a schedule, or you’ll accumulate duplicate triggers that all fire. Check the Triggers panel afterwards to confirm exactly one exists. Time-driven triggers run at an approximate time within the hour you pick rather than exactly on the minute, which is fine for reminders and wrong for anything needing precision.

quotas, and where they actually bite

Every Apps Script account has daily limits, and they differ substantially between a free gmail.com account and a paid Workspace account. The two that matter in practice are the daily recipient cap on outgoing email and the ceiling on total trigger runtime per day. Individual executions are also capped in duration, historically six minutes for consumer accounts, which is plenty for a few hundred spreadsheet rows and not enough for a job that walks ten thousand.

I’m deliberately not printing the current numbers here, because Google adjusts them and a stale figure in a blog post is worse than no figure. The quota page linked under Sources is the only version worth trusting. What you should take away is the shape: this is designed for small, regular jobs, and if your automation needs to mail a thousand people a day you have outgrown the free tier’s intent and should be looking at a proper mail provider.

write scripts that fail politely

If a run hits a quota, it throws and stops mid-way. Because the example writes the reminder date back to the sheet immediately after each send, and skips anyone reminded in the last seven days, a failure halfway leaves a clear record of who was already contacted, and re-running later picks up where it stopped instead of emailing them twice. Designing for resumability like that is worth more than trying to predict the limits.

debugging without a terminal

Three tools, all in the editor. Logger.log() writes lines you can read in the execution log. The Executions panel lists every run, triggered or manual, with status and duration, which is where you look when a trigger seems to have done nothing. And Google emails the account owner a failure summary when a triggered run throws an error, which is the closest thing to free monitoring you’ll get.

That error email is useful and I’d not disable it. It’s the same principle that governs scheduled jobs on any machine you own, where the absence of a visible failure is the dangerous state, not the presence of one.

when to stop and use something else

Apps Script stops being the right answer when you need to wait on webhooks from other services, chain many steps with retries, keep credentials for a dozen APIs, or run for longer than a few minutes. At that point a real automation tool is less work, not more. Similarly, if the job is really about files and folders on a Windows machine rather than cloud data, scheduled scripts on that machine are simpler; I covered that path in automating Windows tasks. And if the task lives on your phone rather than in a spreadsheet — silencing it at night, reacting to a location or a battery level — that is a job for on-device automation instead; I walk through it in Android automation with MacroDroid.

common mistakes

• Testing against live data. Duplicate the sheet, point the script at the copy, and only switch over once the log looks right.
• Reading cells one at a time in a loop. Each getValue() is a separate call and it gets slow fast. Fetch once with getDataRange().getValues(), work in memory, write back in as few calls as possible.
• Running createDailyTrigger more than once and then wondering why everyone got three emails.
• Leaving the project timezone at a default that isn’t yours, so “10 AM daily” means something else entirely.
• Putting an API key directly in the code. Use Script Properties instead, and remember anyone you share edit access with can read the script.
• Assuming a bound script travels with a copied sheet in the way you expect. Make a copy and re-check its triggers, because triggers are not copied.

FAQ

Does Apps Script cost anything?

Not as a product. It’s included with a Google account, free or Workspace, and the difference between them is quota size rather than access. You would only start paying if you moved to Workspace for the higher limits, or if your script calls a paid third-party API.

Can it send WhatsApp messages?

Not directly, and I’d be cautious with anything claiming otherwise. The supported route is a business messaging API through an approved provider, called from UrlFetchApp, which involves an account and per-message cost. For a coaching class or a small trader, email plus a manual nudge is usually the pragmatic answer.

How do I share a script with a colleague safely?

Sharing the spreadsheet with edit access shares the bound script too, including anything stored in it. Keep secrets in Script Properties rather than the code, review what a collaborator can trigger, and remember that a trigger runs as the person who created it, so their quota and their permissions apply, not yours.

Pick the one recurring task you do by hand every month and automate just that, end to end, before touching anything else. Three hours of monthly clerical work turning into a trigger you forget exists is exactly what good automation feels like.

Update history: the reminder script now skips anyone reminded in the last seven days, so the daily trigger or a re-run no longer emails the same person again.

Sources

• Installable triggers documentation — why simple triggers cannot call authorised services, and how installable triggers differ.
• Apps Script quotas and limits — the current per-account daily limits, which change and should always be read at source.
• MailApp service reference — the sendEmail parameters used in the example script.
• ClockTriggerBuilder reference — atHour, everyDays and inTimezone used in createDailyTrigger.