Spreadsheet Automation

Spreadsheet Automation

Overview

Step 1: Identify What to Automate in Sheets

Not every workflow belongs in Sheets. Here's when Sheets is the right tool.

Good use cases for Sheets automation:

Bad use cases (use a real database or tool instead):

Audit your current manual work (10 min): 1. List tasks you do in Sheets manually (copy/paste, data entry, formatting, updating other sheets) 2. Which tasks are repetitive? (daily, weekly, triggered by an event) 3. Which tasks take 5+ minutes each time? 4. Which tasks have clear logic? ("If this, then that")

Low-hanging fruit checklist:

Step 2: Master Advanced Formulas (No Code Required)

Most Sheets automation starts here. Master these formulas and you can build 80% of what you need without Apps Script.

Core Formula Reference

QUERY (SQL-like queries in Sheets):

=QUERY(A1:D100, "SELECT A, B, C WHERE D > 1000 ORDER BY C DESC")

IMPORTRANGE (pull data from other sheets):

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

ARRAYFORMULA (apply formula to entire column):

=ARRAYFORMULA(IF(A2:A="",,B2:B*C2:C))

VLOOKUP / XLOOKUP (lookup values from another table):

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

FILTER (dynamic filtering):

=FILTER(A2:D100, D2:D100>1000, C2:C100="Active")

UNIQUE (remove duplicates):

=UNIQUE(A2:A100)

REGEXEXTRACT (extract patterns from text):

=REGEXEXTRACT(A2, "[0-9]{3}-[0-9]{3}-[0-9]{4}")

IMPORTXML / IMPORTHTML (scrape web data):

=IMPORTXML("https://example.com", "//h1")

Step 3: Build Multi-Sheet Systems

Single-sheet solutions are limited. Real power comes from connecting multiple sheets into a system.

System architecture pattern:

SHEET 1: Data Entry (input form or manual entry)
  ↓
SHEET 2: Master Database (cleaned, validated, enriched)
  ↓
SHEET 3: Dashboard (charts, summaries, insights)
  ↓
SHEET 4: Exports/Reports (formatted for sharing)

Example: Simple CRM in Sheets

Sheet 1: Lead Entry Form

Sheet 2: Master Lead Database

Sheet 3: Dashboard

Sheet 4: Weekly Report

Key principles:

Step 4: Learn Apps Script Basics (Google's JavaScript for Sheets)

Apps Script lets you do things formulas can't: send emails, make API calls, create custom menus, run code on a schedule.

When to use Apps Script:

How to access Apps Script: 1. Open your Google Sheet 2. Extensions → Apps Script 3. Write code in the editor

Example 1: Send Email Alert When New Row Added

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  
  // Only run on "Lead Entry" sheet
  if (sheet.getName() !== "Lead Entry Form") return;
  
  // Get edited row and column
  var row = e.range.getRow();
  var col = e.range.getColumn();
  
  // If new row added (row > 1 to skip header)
  if (row > 1 && col === 1) {
    var name = sheet.getRange(row, 1).getValue();
    var email = sheet.getRange(row, 2).getValue();
    
    // Send email notification
    MailApp.sendEmail({
      to: "you@example.com",
      subject: "New Lead: " + name,
      body: "Name: " + name + "\nEmail: " + email
    });
  }
}

How to set up: 1. Paste code into Apps Script editor 2. Save (Ctrl/Cmd + S) 3. Set up trigger: Triggers (clock icon) → Add Trigger → onEdit → From spreadsheet → On edit → Save 4. Authorize permissions when prompted

Example 2: Fetch Data from API and Write to Sheet

function fetchAPIData() {
  var url = "https://api.example.com/data";
  var options = {
    "method": "GET",
    "headers": {
      "Authorization": "Bearer YOUR_API_KEY"
    }
  };
  
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("API Data");
  
  // Clear existing data
  sheet.clear();
  
  // Write headers
  sheet.appendRow(["ID", "Name", "Value"]);
  
  // Write data rows
  data.forEach(function(item) {
    sheet.appendRow([item.id, item.name, item.value]);
  });
}

How to set up: 1. Paste code, replace url and YOUR_API_KEY 2. Run once manually to test (click Run button) 3. Set up time-based trigger: Triggers → Add Trigger → fetchAPIData → Time-driven → Hour timer → Every hour

Example 3: Auto-Archive Old Rows

function archiveOldRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Active Tasks");
  var archiveSheet = ss.getSheetByName("Archive");
  
  var data = sourceSheet.getDataRange().getValues();
  var today = new Date();
  var cutoffDate = new Date(today.getTime() - (30 * 24 * 60 * 60 * 1000)); // 30 days ago
  
  // Start from row 2 (skip header)
  for (var i = data.length - 1; i >= 1; i--) {
    var rowDate = new Date(data[i][3]); // Column D = date
    
    if (rowDate < cutoffDate) {
      // Copy row to archive
      archiveSheet.appendRow(data[i]);
      
      // Delete from source
      sourceSheet.deleteRow(i + 1);
    }
  }
}

Common Apps Script patterns:

Apps Script resources:

Step 5: Connect Sheets to Other Tools (Zapier, Make, n8n)

Sheets becomes 10x more powerful when integrated with other tools.

Integration strategies:

Strategy 1: Sheets as Database (write-only)

Example workflow (Zapier/Make):

TRIGGER: New Typeform submission
ACTION 1: Add row to Google Sheets
ACTION 2: Send email confirmation (optional)

Example workflow (Webhook → Sheets):

TRIGGER: Webhook received (from website, Stripe, etc.)
ACTION: Parse JSON → Write to Google Sheets

Strategy 2: Sheets as Trigger (read-only)

Example workflow:

TRIGGER: New row in Google Sheets (check every 15 min)
CONDITION: Status column = "Approved"
ACTION: Create task in Asana / Send email / Post to Slack

Strategy 3: Sheets as Middleman (read + write)

Example workflow (sync CRM to email tool):

TRIGGER: New row in Google Sheets (CRM export)
CONDITION: Email column not empty + Tag column = "Newsletter"
ACTION: Add contact to ConvertKit / Mailchimp

Best tools for Sheets integration:

Common integrations:

Step 6: Real-World Automation Examples

Example 1: Automated Invoice Tracker

Problem: Manually tracking invoices sent, paid, and overdue

Solution:

Sheet 1: Invoice Log

Formula magic:

Status = =IF(G2="Paid", "Paid", IF(E2

Apps Script (run daily):

function sendOverdueReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Invoice Log");
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    var status = data[i][6]; // Status column
    var client = data[i][1];
    var invoiceNum = data[i][0];
    var amount = data[i][2];
    
    if (status === "Overdue") {
      MailApp.sendEmail({
        to: "client@example.com",
        subject: "Reminder: Invoice " + invoiceNum + " Overdue",
        body: "Hi " + client + ",\n\nInvoice " + invoiceNum + " for $" + amount + " is overdue. Please remit payment.\n\nThank you!"
      });
    }
  }
}

Trigger: Time-driven, daily at 9am

Example 2: Lead Scoring System

Problem: Manually qualifying leads based on fit

Solution:

Sheet 1: Lead Data

Formula scoring:

Score = =
  IF(B2="Enterprise", 30, IF(B2="Mid-Market", 20, 10)) +
  IF(C2="SaaS", 20, IF(C2="E-commerce", 15, 5)) +
  IF(D2>10000, 30, IF(D2>5000, 20, 10)) +
  IF(E2="Immediate", 20, IF(E2="This Quarter", 10, 0))
Priority = =IF(F2>=70, "Hot", IF(F2>=50, "Warm", "Cold"))

Automation (Zapier):

TRIGGER: New row in Google Sheets
CONDITION: Priority = "Hot"
ACTION 1: Add to Pipedrive as high-priority deal
ACTION 2: Send Slack notification to sales team

Example 3: Content Calendar + Auto-Publishing

Problem: Manually tracking and scheduling social posts

Solution:

Sheet 1: Content Calendar

Apps Script (run hourly):

function publishScheduledPosts() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Content Calendar");
  var data = sheet.getDataRange().getValues();
  var now = new Date();
  
  for (var i = 1; i < data.length; i++) {
    var scheduleDate = new Date(data[i][0]);
    var status = data[i][4];
    
    // If scheduled for now or past, and not yet published
    if (scheduleDate <= now && status === "Scheduled") {
      var platform = data[i][1];
      var text = data[i][2];
      
      // Call API to post (Twitter, LinkedIn, etc.)
      postToAPI(platform, text);
      
      // Mark as published
      sheet.getRange(i+1, 5).setValue("Published");
    }
  }
}
function postToAPI(platform, text) {
  // Example: Twitter API call
  var url = "https://api.twitter.com/2/tweets";
  var payload = JSON.stringify({"text": text});
  var options = {
    "method": "POST",
    "headers": {
      "Authorization": "Bearer YOUR_TWITTER_TOKEN",
      "Content-Type": "application/json"
    },
    "payload": payload
  };
  
  UrlFetchApp.fetch(url, options);
}

Step 7: Performance and Scalability

When Sheets starts to slow down:

Problem: Sheet with 10K+ rows, complex formulas → slow to load/edit

Solutions: 1. Use QUERY instead of FILTER + SORT: More efficient 2. Limit ARRAYFORMULA range: A2:A1000 instead of A:A (entire column) 3. Use static values instead of formulas where possible: Copy → Paste Values 4. Split into multiple sheets: Archive old data to separate sheet 5. Use Importrange sparingly: Each call adds load time 6. Cache data with Apps Script: Pull external data once, store it, refresh periodically instead of live formulas

When to migrate away from Sheets:

Time Saved per Month (hours) = (Minutes per task / 60) × Frequency per month
Monthly Value = Time Saved × Hourly Rate
Setup Cost = (Setup time in hours × Hourly Rate) + Tool costs
Payback Period (months) = Setup Cost / Monthly Value
If payback period < 3 months → Definitely worth it
If payback period > 6 months → Probably not worth it

Task: Manually entering form submissions into CRM (15 min, 40x/month = 10 hours/month saved)
Your hourly rate: $50/hour
Monthly value saved: $500
Setup time: 2 hours
Setup cost: $100 (time) + $0 (Google Forms + Sheets are free)
Payback: $100 / $500 = 0.2 months → Absolutely worth it