How to Automate Google Sheets: From Formulas to Apps Script Workflows

Why Automate Google Sheets?

Manual spreadsheet updates consume hours of valuable business time each week. Whether copying email inquiries into sheets, calculating weekly totals, or exporting invoices, automating Google Sheets frees you from repetitive manual entry while eliminating formula errors.

Level 1: Native Formula Automation

Before writing a single line of code, leverage Google Sheets dynamic array formulas to eliminate manual fill-down dragging:

• ARRAYFORMULA: Apply mathematical or text formulas across thousands of rows automatically: =ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C))
• QUERY: Filter, aggregate, and sort data using SQL-like expressions without fragile pivot tables: =QUERY(A:E, "SELECT B, SUM(D) WHERE E = 'Closed' GROUP BY B", 1)
• IMPORTRANGE: Automatically pull live data from one master spreadsheet into departmental workbooks without manual copy-pasting.

Level 2: Automated Macro Recorder

Google Sheets includes an integrated Macro Recorder that converts mouse clicks and keyboard actions into clean Apps Script code:

1. Click Extensions > Macros > Record Macro.
2. Choose whether to use Absolute or Relative cell references.
3. Perform your repetitive formatting or sorting task.
4. Save the macro and assign a shortcut key (e.g., Ctrl+Alt+Shift+1).

Level 3: Google Apps Script Time & Event Triggers

To run tasks automatically at 6:00 AM every morning or whenever a colleague edits a cell, use Apps Script triggers:

function archiveDailyClosedDeals() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getSheetByName('Active_Leads');
  const target = ss.getSheetByName('Archive');
  const data = source.getDataRange().getValues();

  for (let i = data.length - 1; i >= 1; i--) {
    if (data[i][4] === 'Closed Won') {
      target.appendRow(data[i]);
      source.deleteRow(i + 1);
    }
  }
}

Set a clock trigger under Extensions > Apps Script > Triggers (Alarm icon) to run this function every morning at 02:00.