Google Apps Script for Google Sheets: serverless automation

Anyone working with Google Sheets has a built-in automation environment that many people don't know about: Google Apps Script. It is JavaScript that runs on Google's servers, tied to your account and to services such as Gmail, Drive and Calendar. There is nothing to install and no computer has to stay on: the script runs in the cloud, even after you have closed the browser.

Opening the editor

From any spreadsheet choose Extensions > Apps Script. An editor opens with a Code.gs file and an empty function. This kind of script is "bound" to the spreadsheet: it lives inside it and can access it without specifying any identifiers. The first time you run a function, Google asks you to authorise the permissions it needs, for example reading spreadsheets or sending emails on your behalf. Read the list carefully: this is the moment to understand what the script will be able to do.

Reading and writing data

The main service is SpreadsheetApp. The golden rule is to read and write in blocks, not cell by cell, because every call costs time.

function totalPerCustomer() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Orders");
  const data = sheet.getDataRange().getValues(); // matrix of rows
  const totals = {};
  for (let i = 1; i < data.length; i++) {      // skip the header
    const [customer, , amount] = data[i];
    totals[customer] = (totals[customer] || 0) + amount;
  }
  const rows = Object.entries(totals);
  const out = SpreadsheetApp.getActive().getSheetByName("Summary");
  out.clearContents();
  out.getRange(1, 1, rows.length, 2).setValues(rows);
}

getValues() returns a matrix in which rows and columns start at zero, while getRange(row, column, ...) counts from one. This double convention is the most common source of errors, so it is worth a comment in the code.

Adding a custom menu

A script is easier to use if it appears in the spreadsheet like any other command. The special onOpen function runs every time the file is opened:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Office tools")
    .addItem("Update summary", "totalPerCustomer")
    .addToUi();
}

From then on, colleagues find the entry in the menu without having to open the editor.

Sending emails from the spreadsheet

A typical case: a deadlines sheet with name, address and date, and the wish to remind anyone whose deadline is approaching.

function remindDeadlines() {
  const data = SpreadsheetApp.getActive().getSheetByName("Deadlines")
    .getDataRange().getValues();
  const today = new Date();
  const inSevenDays = new Date(today.getTime() + 7 * 24 * 3600 * 1000);
  data.slice(1).forEach(([name, email, deadline, sent]) => {
    if (!sent && deadline instanceof Date && deadline <= inSevenDays) {
      MailApp.sendEmail(email, "Deadline reminder",
        `Dear ${name}, this is a reminder of your deadline on ${deadline.toLocaleDateString("en-GB")}.`);
    }
  });
}

In the real version it is also worth writing the sending date into the "sent" column, so the reminder doesn't go out twice. It is a general principle: every automation with external effects must remember what it has already done.

Scheduling with triggers

The real leap forward comes with time-driven triggers. From the alarm-clock icon in the editor's left panel you create a trigger that runs remindDeadlines every day within a time slot of your choice. There are also event-driven triggers, for example on submission of a Google Form linked to the spreadsheet or on a cell edit.

Limits to be aware of

Apps Script is convenient but not unlimited. Google applies daily quotas, for example on the number of emails you can send and on total execution time, with different values for personal accounts and Workspace accounts. There is also a time limit on each single execution: a script that processes tens of thousands of rows must be designed to work in batches. The current quotas are listed in the official documentation and it is worth checking them before giving the script a critical job.

Another aspect to consider: the script runs with the permissions of whoever authorised it. If that person leaves the company and their account is deactivated, the triggers stop working. In my work I suggest creating important automations with a shared office service account, not a personal one.

When to prefer something else

Apps Script is ideal for anything that already lives in the Google ecosystem. If the data is in local Excel files or in an internal management system, a Python script on your own computer or on a server is often a better fit. If instead you need connections between many different services, a no-code platform may be quicker to set up.

In short

• Apps Script opens from Extensions > Apps Script and runs on Google's servers.
• Read and write data in blocks with getValues() and setValues().
• onOpen adds custom menus; time-driven triggers schedule executions.
• Keep track of actions already performed to avoid duplicate emails.
• Consider quotas, execution time limits and the account the script is authorised with.