spreadsheet-automation

What this skill does

# Spreadsheet Automation

## Overview
Google Sheets isn't just for budgets and lists. With the right formulas, Apps Script, and integrations, it becomes a database, CRM, project tracker, analytics dashboard, and workflow engine — all in one free tool. This playbook shows you how to build production-grade systems in Sheets that replace $50-500/month SaaS tools.

---

## 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:**
- **Data collection from multiple sources** (form responses, API data, manual input) → centralize in one place
- **Lightweight databases** (customer lists, inventory, project tracker) → under 10K rows, basic relationships
- **Dashboards and reporting** (pull data from other tools, visualize, share)
- **Workflow triggers** (when row added/updated → send email, create task, update another sheet)
- **Data transformation** (clean, format, enrich data from messy sources)

**Bad use cases (use a real database or tool instead):**
- Heavy computation (millions of rows, complex queries) → use BigQuery, Airtable, or SQL database
- Real-time collaboration with 10+ concurrent users → use Airtable, Notion, or dedicated project management tool
- Mission-critical data that can't afford accidental deletion → use a real database with backups and version control
- Complex relational data (many-to-many relationships) → use Airtable or proper database

**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:**
- [ ] Auto-populate cells based on other cells (formulas)
- [ ] Pull data from external sources (APIs, other sheets, web scraping)
- [ ] Auto-format or clean data (remove duplicates, standardize dates, extract values)
- [ ] Send notifications when conditions are met (email alerts, Slack messages)
- [ ] Create charts or dashboards that update automatically
- [ ] Sync data between Sheets and other tools (CRM, project management, accounting)

---

## 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")
```
- Use for: Filter, sort, group, and summarize data
- Syntax: `SELECT [columns] WHERE [condition] ORDER BY [column] LIMIT [number]`
- Example: Pull all customers with orders > $1,000, sorted by date

**IMPORTRANGE (pull data from other sheets):**
```
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")
```
- Use for: Centralize data from multiple sheets into one master sheet
- Setup: First time, you need to approve access (click "Allow access" when prompted)
- Example: Pull sales data from regional team sheets into one master dashboard

**ARRAYFORMULA (apply formula to entire column):**
```
=ARRAYFORMULA(IF(A2:A="",,B2:B*C2:C))
```
- Use for: Auto-calculate for all rows (no dragging formulas down)
- Example: Auto-multiply quantity × price for every new row added

**VLOOKUP / XLOOKUP (lookup values from another table):**
```
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
```
- Use for: Match and pull related data (e.g., customer name → pull their email)
- XLOOKUP (newer): More flexible, can search left-to-right or right-to-left

**FILTER (dynamic filtering):**
```
=FILTER(A2:D100, D2:D100>1000, C2:C100="Active")
```
- Use for: Show only rows that meet criteria (updates automatically when data changes)
- Example: Show only active customers with revenue > $1,000

**UNIQUE (remove duplicates):**
```
=UNIQUE(A2:A100)
```
- Use for: Extract unique values from a list (auto-updates when source changes)

**REGEXEXTRACT (extract patterns from text):**
```
=REGEXEXTRACT(A2, "[0-9]{3}-[0-9]{3}-[0-9]{4}")
```
- Use for: Pull phone numbers, emails, URLs, or any pattern from messy text
- Example: Extract domain from email addresses

**IMPORTXML / IMPORTHTML (scrape web data):**
```
=IMPORTXML("https://example.com", "//h1")
```
- Use for: Pull live data from websites (prices, headlines, tables)
- Example: Track competitor pricing automatically

---

## 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**
- Columns: Name, Email, Company, Source, Date Added
- Use: Google Form → auto-populates this sheet
- Validation: Email format check, required fields

**Sheet 2: Master Lead Database**
- Pulls from Sheet 1 using `IMPORTRANGE` or direct reference
- Adds enrichment: Status (New/Contacted/Qualified/Closed), Last Contact Date, Notes
- Formula example: `=IF(ISBLANK(D2), "New", D2)` (auto-set status to "New" if empty)

**Sheet 3: Dashboard**
- Total leads: `=COUNTA(MasterDB!A2:A)`
- Leads this week: `=COUNTIF(MasterDB!E2:E, ">="&TODAY()-7)`
- Conversion rate: `=COUNTIF(MasterDB!D2:D, "Closed")/COUNTA(MasterDB!A2:A)`
- Chart: Leads by source (pie chart)

**Sheet 4: Weekly Report**
- Formula: `=FILTER(MasterDB!A2:E, MasterDB!E2:E>=TODAY()-7)`
- Auto-pull this week's leads for review meeting

**Key principles:**
- One sheet = one purpose (don't mix input, storage, and display)
- Use formulas to connect sheets (avoid manual copy/paste)
- Protect important sheets (prevent accidental edits)

---

## 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:**
- Formulas can't do it (sending emails, hitting APIs, complex logic)
- You need automation to run on a schedule (every hour, daily, weekly)
- You want custom functions or menu items

**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

```javascript
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: "[email protected]",
      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

```javascript
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]);