How to automate Google Sheets with Apps Script

How to Automate Google Sheets with Apps Script

Google Apps Script provides free, server-side JavaScript automation for Google Sheets. This guide covers creating automated data processing, scheduled reports, and custom functions without external tools.

Step 1: Open the Script Editor

From any Google Sheet, click Extensions > Apps Script. This opens the script editor bound to your spreadsheet, giving the script direct access to the sheet's data.

Step 2: Read and Write Sheet Data

Basic operations:

function processData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  const data = sheet.getDataRange().getValues(); // 2D array of all data
  
  // Process each row (skip header)
  for (let i = 1; i < data.length; i++) {
    const name = data[i][0];
    const amount = data[i][1];
    // Write calculated result to column C
    sheet.getRange(i + 1, 3).setValue(amount * 1.1);
  }
}

Step 3: Create Custom Functions

Custom functions work like built-in Sheets formulas:

function TAXAMOUNT(subtotal, rate) {
  return subtotal * (rate / 100);
}
// Use in sheet as =TAXAMOUNT(A1, 8.5)

Step 4: Set Up Automated Triggers

Triggers run scripts automatically:

• Time-based: Run daily, weekly, or at specific times. Click Triggers (clock icon) > Add Trigger > Time-driven.
• Spreadsheet event: Run when the sheet is edited, opened, or a form is submitted.
• Installable triggers: More flexible than simple triggers, supporting event filtering.

Common automated workflows:

• Daily report generation at 8 AM
• Process new form submissions automatically
• Send email summaries of updated data weekly

Step 5: Send Automated Emails from Sheet Data

function sendWeeklyReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary");
  const data = sheet.getDataRange().getValues();
  let report = "Weekly Summary:\n\n";
  for (let i = 1; i < data.length; i++) {
    report += data[i][0] + ": " + data[i][1] + "\n";
  }
  GmailApp.sendEmail("[email protected]", "Weekly Report", report);
}

Step 6: Connect to External APIs

function fetchExternalData() {
  const response = UrlFetchApp.fetch("https://api.example.com/data", {
    headers: { "Authorization": "Bearer YOUR_TOKEN" }
  });
  const data = JSON.parse(response.getContentText());
  // Write to sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("API Data");
  data.forEach((item, i) => {
    sheet.getRange(i + 2, 1).setValue(item.name);
    sheet.getRange(i + 2, 2).setValue(item.value);
  });
}

Execution Limits

Editor's Note: We built 8 Apps Script automations for a marketing agency's Google Sheets workflow. Daily KPI calculations, weekly client reports, and monthly billing summaries — all running on time-based triggers at $0/month. The initial development took 12 hours. Running equivalent workflows in Zapier would cost approximately $73.50/month ($882/year). The scripts have run without modification for 14 months.