Apps Script Crash Course

30 Minutes to Apps Script Fluency

You're a senior engineer. You don't need to learn JavaScript — you need the domain-specific vocabulary, mental model, API surface, patterns, and gotchas of Google Apps Script. This page gives you exactly that.

01 What Is Apps Script

Google Apps Script is a cloud-hosted JavaScript runtime (V8) that is deeply integrated with Google Workspace. Think of it as "serverless functions with first-class access to Google's entire productivity suite — zero infra, zero billing, zero deploy pipeline."

The Elevator Pitch

You write JavaScript in a browser-based IDE (or locally with clasp). Your code runs on Google's servers. It can read/write Sheets, send Gmail, create Calendar events, manage Drive files, build custom UIs — all through purpose-built service objects that are globally available (no imports). Deployment means clicking "Deploy" or running clasp push.

Key Properties

Property	Detail
Language	JavaScript (V8 engine since 2020; legacy Rhino engine still exists but is deprecated)
Runtime	Server-side, on Google Cloud infrastructure. Not in the browser.
IDE	Built-in editor at `script.google.com`, or local dev with `clasp` CLI
Auth	OAuth2 handled automatically. Scripts run as the user or as a service account.
Cost	Free for personal use. Workspace Business/Enterprise have higher quotas.
Execution model	Synchronous. Single-threaded. No event loop. No `async/await`.
No npm	No module system. You can use Libraries (other Apps Script projects) instead.
Deployment types	Web App, API Executable, Add-on, Bound Script, Standalone Script

Mental Model If AWS Lambda is "functions as a service for general-purpose compute," Apps Script is "functions as a service for Google Workspace automation." You trade flexibility for incredible integration depth — one line of code can do what would take 50 lines of API calls + OAuth setup in any other environment.

02 Key Terminology

Bound Script

A script attached to a specific Google Doc, Sheet, Slide, or Form. Created via Extensions > Apps Script. Has access to its parent document via getActiveSpreadsheet() etc.

Standalone Script

A script that lives on its own in Drive, not attached to any document. Created at script.google.com.

Container

The Google file (Sheet, Doc, etc.) that a bound script is attached to. The script's "host."

Services

Global objects that provide access to Google APIs: SpreadsheetApp, GmailApp, DriveApp, CalendarApp, etc. No imports needed.

Triggers

Event handlers that run your functions automatically. Two kinds: Simple (e.g., onOpen, onEdit) and Installable (more powerful, set up via UI or ScriptApp).

Web App

An Apps Script project deployed with a public URL. Must implement doGet(e) and/or doPost(e). Returns HTML or JSON.

HTML Service

The system for serving HTML to users — used for custom dialogs, sidebars, and full Web Apps. Client-server communication via google.script.run.

clasp

Command Line Apps Script Projects. Official CLI tool for local development. Push/pull code, manage deployments, enable TypeScript support.

Manifest

appsscript.json — the project config file. Defines runtime version (V8 vs Rhino), OAuth scopes, time zone, and dependencies.

Libraries

Reusable Apps Script projects that other scripts can import by Script ID. The closest thing to npm packages. Versioned.

Add-on

An Apps Script project published to the Google Workspace Marketplace. Can be installed by any user. Has its own review/publishing process.

Properties Service

Key-value storage for scripts. Three scopes: ScriptProperties (shared by all users), UserProperties (per user), DocumentProperties (per document).

Simple vs Installable Triggers

	Simple Triggers	Installable Triggers
Setup	Just name the function `onOpen`, `onEdit`, `onInstall`	Created via UI or `ScriptApp.newTrigger()`
Auth	Cannot access services requiring authorization	Can access authorized services (Gmail, Calendar, etc.)
Types	`onOpen`, `onEdit`, `onInstall`, `onSelectionChange`	All simple types + time-driven + `onChange` + form submit
Runs as	The active user	The user who created the trigger
Restrictions	Cannot open dialogs/sidebars (except in `onOpen` menus), 30s limit	Fewer restrictions, 6-min limit

Interview tip "Simple triggers are limited but zero-config; installable triggers are more powerful but require explicit setup and run as the installing user's identity" — this distinction comes up frequently.

03 The Services API Surface

Apps Script provides global service objects — no imports, no initialization. They're just... there. This is the core API you work with.

Core Workspace Services

Service	Global Object	What It Does
Spreadsheet	`SpreadsheetApp`	Read/write cells, format, create sheets, charts. The most commonly used service by far.
Gmail	`GmailApp` / `MailApp`	Send, search, label, read emails. `MailApp` is simpler (send-only), `GmailApp` is full-featured.
Drive	`DriveApp`	Create, move, share files and folders. Manage permissions.
Calendar	`CalendarApp`	Create/read/update events, manage calendars.
Docs	`DocumentApp`	Read/write Google Docs programmatically.
Slides	`SlidesApp`	Create/modify presentations.
Forms	`FormApp`	Create forms, read responses.

Utility & Platform Services

Service	Global Object	What It Does
URL Fetch	`UrlFetchApp`	HTTP requests to external APIs. Your `fetch()` equivalent.
HTML Service	`HtmlService`	Serve HTML for sidebars, dialogs, or Web Apps.
Cache	`CacheService`	Temporary key-value cache (up to 6 hours).
Properties	`PropertiesService`	Persistent key-value storage (script, user, or document scoped).
Lock	`LockService`	Mutual exclusion locks for concurrent execution safety.
Utilities	`Utilities`	Base64, UUID, date formatting, zip, digest, sleep.
Logger	`Logger` / `console`	Logging. `Logger.log()` is legacy; `console.log()` works with V8 and shows in Execution Log.
Session	`Session`	Get active user's email, locale, timezone.
Script App	`ScriptApp`	Manage triggers, get script URL, get OAuth token.
Content	`ContentService`	Return JSON or text from Web Apps (instead of HTML).
JDBC	`Jdbc`	Connect to external MySQL, SQL Server, Oracle databases.

Advanced Services

Advanced Services are thin wrappers around full Google REST APIs (Sheets API v4, Admin SDK, BigQuery, Analytics, etc.). They must be explicitly enabled in the manifest. They give you access to features not covered by the built-in services — e.g., batch updates via Sheets API v4 instead of SpreadsheetApp.

When to use Advanced Services Use the built-in service (SpreadsheetApp) for 90% of tasks. Switch to the Advanced Service (Sheets API) when you need batch operations, specific formatting options, or features the built-in doesn't expose.

04 Common Use Cases

What companies actually build with Apps Script:

Workflow Automation

Approval flows, onboarding checklists, leave requests, purchase orders — triggered by form submissions or sheet edits.

Custom UIs in Sheets/Docs

Sidebars and dialogs that turn a spreadsheet into a lightweight app. Data entry forms, dashboards, admin panels.

Email Automation

Mail merge, templated notifications, digest emails, auto-replies, email-to-sheet logging.

Data Pipelines

Pull data from APIs into Sheets on a schedule (time-driven triggers). CRM sync, analytics roll-ups, inventory tracking.

Report Generation

Auto-generate Google Docs/Slides/PDFs from spreadsheet data. Monthly reports, invoices, certificates.

Internal Tools

Web Apps deployed internally. Simple CRUD apps using Sheets as a database. Employee directories, booking systems.

Google Forms Extensions

Custom validation, conditional email routing, auto-grading, response-triggered workflows.

Add-ons

Marketplace-published extensions for Sheets, Docs, Slides, or Forms. Revenue potential via Workspace Marketplace.

05 Code Patterns & Idioms

Reading & Writing Sheets Data

The most fundamental pattern. Always batch your reads/writes — never loop getValue()/setValue().

Code.gs — The Right Way

function processData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');

  // BATCH READ — get all data in ONE call → returns 2D array
  const data = sheet.getDataRange().getValues();
  // data = [['Name', 'Amount', 'Status'], ['Alice', 500, 'Pending'], ...]

  const headers = data[0];    // First row = headers
  const rows = data.slice(1); // Rest = data

  // Process in memory
  const processed = rows.map(row => {
    const [name, amount, status] = row;
    return [name, amount, status, amount > 1000 ? 'High' : 'Normal'];
  });

  // BATCH WRITE — write all data in ONE call
  const outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Output');
  outputSheet.getRange(2, 1, processed.length, processed[0].length)
    .setValues(processed);
}

Code.gs — The WRONG Way (N+1 problem)

// ❌ DON'T — this makes one API call PER CELL
function badExample() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let i = 1; i <= 1000; i++) {
    const val = sheet.getRange(i, 1).getValue();   // 1000 API calls to read
    sheet.getRange(i, 2).setValue(val * 2);          // 1000 API calls to write
  }
  // This will be 100x slower than the batch approach
}

Custom Menu + Sidebar

Code.gs

// onOpen is a simple trigger — runs automatically when doc opens
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Tool')           // Creates a top-level menu
    .addItem('Open Panel', 'showSidebar')  // Menu item → function
    .addSeparator()
    .addItem('Run Report', 'generateReport')
    .addToUi();
}

function showSidebar() {
  // Load an HTML file from the project
  const html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('My Tool')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

// This function is called FROM the HTML via google.script.run
function saveFormData(formData) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([new Date(), formData.name, formData.email]);
  return { success: true };
}

Sidebar.html

<!DOCTYPE html>
<html>
<head><base target="_top"></head>
<body>
  <input id="name" placeholder="Name">
  <input id="email" placeholder="Email">
  <button onclick="submit()">Save</button>
  <div id="result"></div>

  <script>
    function submit() {
      const data = {
        name: document.getElementById('name').value,
        email: document.getElementById('email').value
      };

      // google.script.run is THE bridge between client HTML and server Apps Script
      google.script.run
        .withSuccessHandler(result => {
          document.getElementById('result').textContent = 'Saved!';
        })
        .withFailureHandler(err => {
          document.getElementById('result').textContent = 'Error: ' + err.message;
        })
        .saveFormData(data);  // ← calls the server-side function
    }
  </script>
</body>
</html>

google.script.run is the single most important client-server concept in Apps Script. It's an asynchronous RPC bridge — the client HTML calls server-side functions by name, with .withSuccessHandler() and .withFailureHandler() callbacks. Parameters and return values are serialized (no DOM elements, no functions — plain objects and arrays only).

Time-Driven Triggers

Code.gs

// Set up via UI: Edit > Triggers > Add Trigger > Time-driven
// Or programmatically:
function createTimeTrigger() {
  ScriptApp.newTrigger('dailySync')
    .timeBased()
    .everyHours(6)       // or: .everyMinutes(10), .atHour(9).everyDays(1)
    .create();
}

function dailySync() {
  // Fetch data from external API
  const response = UrlFetchApp.fetch('https://api.example.com/data', {
    method: 'GET',
    headers: { 'Authorization': 'Bearer ' + getApiKey() },
    muteHttpExceptions: true  // Don't throw on 4xx/5xx
  });

  if (response.getResponseCode() !== 200) {
    console.error('API error:', response.getContentText());
    return;
  }

  const data = JSON.parse(response.getContentText());

  // Write to sheet
  const sheet = SpreadsheetApp.openById('SPREADSHEET_ID').getSheetByName('API Data');
  const rows = data.items.map(item => [item.id, item.name, item.value, new Date()]);

  if (rows.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length)
      .setValues(rows);
  }
}

// Store secrets in Properties, not in code
function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('API_KEY');
}

Web App (doGet / doPost)

Code.gs

// Deploy as: Web App
// Execute as: "Me" or "User accessing the web app"
// Access: "Anyone" or "Anyone within [org]"

function doGet(e) {
  // e.parameter contains query string params
  // e.g., ?action=list&page=1 → e.parameter.action = 'list'

  const action = e.parameter.action;

  if (action === 'list') {
    // Return JSON
    const data = getItems();
    return ContentService
      .createTextOutput(JSON.stringify(data))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // Return HTML page
  return HtmlService.createHtmlOutputFromFile('WebApp')
    .setTitle('My Web App')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function doPost(e) {
  // e.postData.contents = raw body
  // e.postData.type = content type
  const body = JSON.parse(e.postData.contents);

  // Process the POST data...
  saveItem(body);

  return ContentService
    .createTextOutput(JSON.stringify({ success: true }))
    .setMimeType(ContentService.MimeType.JSON);
}

onEdit Trigger Pattern

Code.gs

// Simple trigger version — limited permissions
function onEdit(e) {
  // e = event object with context about what changed
  const range = e.range;
  const sheet = range.getSheet();
  const value = e.value;       // new value (single cell edits only)
  const oldValue = e.oldValue; // previous value

  // Common pattern: react to edits in a specific column
  if (sheet.getName() === 'Orders' && range.getColumn() === 5) {
    // Column 5 (Status) was edited
    if (value === 'Shipped') {
      // Auto-fill the "Shipped Date" column
      sheet.getRange(range.getRow(), 6).setValue(new Date());
    }
  }
}

// For an installable trigger that needs auth (e.g., send email on edit):
// Create via ScriptApp.newTrigger('onEditInstallable').forSpreadsheet(ss).onEdit().create()
function onEditInstallable(e) {
  const value = e.range.getValue();
  if (value === 'Urgent') {
    GmailApp.sendEmail('manager@company.com', 'Urgent item flagged',
      `Row ${e.range.getRow()} was marked as Urgent.`);
  }
}

Custom Functions (for use in cell formulas)

Code.gs

/**
 * Converts meters to feet.
 * Use in a cell as: =METERS_TO_FEET(A1)
 *
 * @param {number} meters The value in meters.
 * @return {number} The value in feet.
 * @customfunction           ← this JSDoc tag enables autocomplete in Sheets
 */
function METERS_TO_FEET(meters) {
  return meters * 3.28084;
}

/**
 * Fetches the current price of a stock.
 * =STOCK_PRICE("GOOGL")
 *
 * @param {string} symbol The ticker symbol.
 * @return {number} Current price.
 * @customfunction
 */
function STOCK_PRICE(symbol) {
  // Note: Custom functions CANNOT access services requiring auth
  // UrlFetchApp works, but GmailApp/DriveApp etc. do NOT
  const url = `https://api.example.com/price?symbol=${symbol}`;
  const response = UrlFetchApp.fetch(url);
  return JSON.parse(response.getContentText()).price;
}

06 Best Practices & Gotchas

Performance

The #1 Performance Rule BATCH YOUR READS AND WRITES. Never call .getValue() or .setValue() in a loop. Use .getValues()/.setValues() to work with 2D arrays. A single getValues() on 10,000 cells is ~100x faster than 10,000 individual getValue() calls.

Use SpreadsheetApp.flush() — forces pending changes to be written. Useful when you need to ensure data is committed before continuing.
CacheService — cache expensive API calls for up to 6 hours. CacheService.getScriptCache().put(key, value, ttl)
Minimize service calls — each service call is an RPC to Google's servers. Combine operations where possible.
Use Arrays for computation — read data into arrays, process in-memory, write back in one call.

Quotas & Limits

Limit	Consumer (free)	Workspace
Execution time (per call)	6 minutes	6 minutes (30 for some)
Triggers total runtime / day	90 min	6 hours
Emails sent / day	100	1,500
URL Fetch calls / day	20,000	100,000
Properties storage	500KB total	500KB total
Custom function runtime	30 seconds	30 seconds
Simultaneous executions	30	30

The 6-Minute Wall This is the most commonly hit limit. If your script processes large datasets, you need to implement continuation patterns — save progress to Properties, set a time-driven trigger to resume, and pick up where you left off. This is a very common interview topic.

Common Gotchas

V8 vs Rhino — Always use V8 runtime. Set "runtimeVersion": "V8" in appsscript.json. Legacy Rhino doesn't support modern JS (no const, no arrow functions, no template literals).
Timezone mismatches — Script timezone is set in the manifest. Sheet timezone is set in sheet settings. Dates can be silently wrong if these don't match. Always be explicit with timezones.
onEdit limitations — Simple onEdit can't use services requiring auth. Use installable triggers for sending emails, accessing Drive, etc.
Custom functions are pure — Custom functions (used in cells) cannot modify sheets, send emails, or use most services. They're computation-only.
No async/await — Everything is synchronous. UrlFetchApp.fetch() blocks. For multiple URLs, use UrlFetchApp.fetchAll() (batched, parallel under the hood).
Web App versioning — When you deploy a Web App, you create a versioned deployment. Changes to code don't take effect until you create a new deployment. Use "Test deployment" URL during dev to always get the latest code.
Scopes & permissions — OAuth scopes are auto-detected from your code. If you reference GmailApp, users will be prompted to grant Gmail access. Minimize scope usage for user trust.
google.script.run limits — Parameters must be serializable (no functions, no DOM nodes). Return values same. Max 9 concurrent server calls from client HTML.

Security & Best Practices

Never hardcode secrets — Use PropertiesService.getScriptProperties() for API keys, tokens, passwords.
Validate inputs — Especially in Web Apps exposed to external users. doGet/doPost can receive anything.
Use LockService — When multiple users/triggers might modify the same data concurrently.
Error handling — Wrap trigger functions in try/catch. Unhandled errors in triggers cause them to be disabled after repeated failures.
Logging — Use console.log() (V8). View logs at Executions in the Apps Script dashboard. Logger.log() is the legacy equivalent.
Testing — No built-in test framework. Use clasp + TypeScript + Jest locally, or create test functions that assert expected behavior.

clasp Workflow

# Install
npm install -g @google/clasp

# Login
clasp login

# Clone existing project
clasp clone <scriptId>

# Create new project
clasp create --type sheets --title "My Project"

# Push local changes to Apps Script
clasp push

# Pull remote changes
clasp pull

# Open in browser
clasp open

# Deploy
clasp deploy --description "v1.0"

# Enable TypeScript (just use .ts files — clasp transpiles automatically)
# Note: types are in @types/google-apps-script
npm install --save-dev @types/google-apps-script

07 Mini-Project: Leave Request System

A realistic, interview-worthy project that touches almost every major Apps Script concept. This is the kind of thing companies actually build.

What It Does

Employees open a Google Sheet → custom menu → sidebar form
Submit a leave request (type, dates, reason)
Request is logged in the Sheet with a unique ID
Manager gets an email with Approve/Reject links
Clicking the link hits a Web App endpoint that updates the Sheet
On approval: calendar event created, requester notified
Weekly summary report sent via time-driven trigger

Concepts Demonstrated

Feature	Concept
Custom menu	`onOpen` simple trigger + `SpreadsheetApp.getUi()`
Sidebar form	HTML Service + `google.script.run`
Sheet as database	`SpreadsheetApp` batch read/write
Email with action links	`GmailApp.sendEmail()` + HTML body
Web App endpoint	`doGet(e)` + URL parameters
Calendar integration	`CalendarApp.createAllDayEvent()`
Scheduled reports	Time-driven trigger
Config storage	`PropertiesService`
User identity	`Session.getActiveUser()`
UUID generation	`Utilities.getUuid()`

Code.gs — Main Server-Side Logic

// ── Configuration ──
const SHEET_NAME = 'Leave Requests';
const MANAGER_EMAIL = 'manager@company.com';

// In production, store the Web App URL in Properties after deploying:
// PropertiesService.getScriptProperties().setProperty('WEB_APP_URL', '...')
function getWebAppUrl() {
  return PropertiesService.getScriptProperties().getProperty('WEB_APP_URL')
    || 'https://script.google.com/macros/s/DEPLOY_ID/exec';
}

// ── Triggers ──

/**
 * Simple trigger: runs when spreadsheet opens.
 * Adds custom menu to the toolbar.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Leave Manager')
    .addItem('Submit Request', 'showSidebar')
    .addSeparator()
    .addItem('Refresh Dashboard', 'refreshDashboard')
    .addToUi();
}

// ── UI ──

/**
 * Opens the leave request form as a sidebar.
 */
function showSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('Submit Leave Request')
    .setWidth(360);
  SpreadsheetApp.getUi().showSidebar(html);
}

// ── Core Logic ──

/**
 * Processes a leave request from the sidebar form.
 * Called via google.script.run from Sidebar.html.
 *
 * @param {Object} formData - { leaveType, startDate, endDate, reason }
 * @return {Object} - { success: boolean, requestId: string }
 */
function submitLeaveRequest(formData) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME) || createRequestSheet(ss);

  const requestId = Utilities.getUuid().substring(0, 8).toUpperCase();
  const timestamp = new Date();
  const requester = Session.getActiveUser().getEmail();

  // Batch write: single appendRow call
  sheet.appendRow([
    requestId,
    timestamp,
    requester,
    formData.leaveType,
    new Date(formData.startDate),
    new Date(formData.endDate),
    formData.reason,
    'PENDING'
  ]);

  // Send approval email to manager
  sendApprovalEmail(requestId, requester, formData);

  return { success: true, requestId: requestId };
}

/**
 * Creates the data sheet with headers if it doesn't exist.
 */
function createRequestSheet(ss) {
  const sheet = ss.insertSheet(SHEET_NAME);
  const headers = [
    'ID', 'Submitted', 'Requester', 'Type',
    'Start', 'End', 'Reason', 'Status'
  ];
  sheet.getRange(1, 1, 1, headers.length)
    .setValues([headers])
    .setFontWeight('bold')
    .setBackground('#1a73e8')
    .setFontColor('#ffffff');
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, headers.length, 130);
  return sheet;
}

// ── Email ──

/**
 * Sends an approval email with Approve/Reject action links.
 */
function sendApprovalEmail(requestId, requester, formData) {
  const webAppUrl = getWebAppUrl();
  const approveUrl = `${webAppUrl}?action=approve&id=${requestId}`;
  const rejectUrl = `${webAppUrl}?action=reject&id=${requestId}`;

  const htmlBody = `
    <div style="font-family:Arial,sans-serif;max-width:500px;">
      <h2 style="color:#1a73e8;">Leave Request: ${requestId}</h2>
      <table style="border-collapse:collapse;width:100%;">
        <tr><td style="padding:8px;color:#666;">From</td>
            <td style="padding:8px;"><strong>${requester}</strong></td></tr>
        <tr><td style="padding:8px;color:#666;">Type</td>
            <td style="padding:8px;">${formData.leaveType}</td></tr>
        <tr><td style="padding:8px;color:#666;">Dates</td>
            <td style="padding:8px;">${formData.startDate} → ${formData.endDate}</td></tr>
        <tr><td style="padding:8px;color:#666;">Reason</td>
            <td style="padding:8px;">${formData.reason}</td></tr>
      </table>
      <br>
      <a href="${approveUrl}" style="background:#34a853;color:white;
         padding:12px 28px;text-decoration:none;border-radius:4px;
         display:inline-block;margin-right:12px;">Approve</a>
      <a href="${rejectUrl}" style="background:#ea4335;color:white;
         padding:12px 28px;text-decoration:none;border-radius:4px;
         display:inline-block;">Reject</a>
    </div>
  `;

  GmailApp.sendEmail(
    MANAGER_EMAIL,
    `Leave Request ${requestId} — ${requester}`,
    // Plain-text fallback
    `Leave request ${requestId} from ${requester}. ` +
    `${formData.leaveType}: ${formData.startDate} to ${formData.endDate}. ` +
    `Approve: ${approveUrl} | Reject: ${rejectUrl}`,
    { htmlBody: htmlBody }
  );
}

// ── Web App Endpoint ──

/**
 * Handles GET requests when deployed as a Web App.
 * Called when manager clicks Approve/Reject links in the email.
 *
 * @param {Object} e - Event object with e.parameter (query params)
 * @return {HtmlOutput} Confirmation page
 */
function doGet(e) {
  const action = e.parameter.action;
  const requestId = e.parameter.id;

  if (!action || !requestId) {
    return HtmlService.createHtmlOutput(
      '<h1>Invalid request</h1><p>Missing action or request ID.</p>'
    );
  }

  const newStatus = action === 'approve' ? 'APPROVED' : 'REJECTED';
  const result = updateRequestStatus(requestId, newStatus);

  if (!result.success) {
    return HtmlService.createHtmlOutput(
      `<h1>Request ${requestId} not found</h1>`
    );
  }

  // On approval, create a calendar event
  if (action === 'approve') {
    createCalendarEvent(result.row);
  }

  // Notify the requester
  notifyRequester(result.row, newStatus);

  const color = action === 'approve' ? '#34a853' : '#ea4335';
  return HtmlService.createHtmlOutput(
    `<div style="font-family:Arial;text-align:center;padding:60px;">
       <h1 style="color:${color};">${newStatus}</h1>
       <p>Request <strong>${requestId}</strong> has been ${newStatus.toLowerCase()}.</p>
     </div>`
  );
}

/**
 * Finds and updates a request's status in the sheet.
 *
 * @param {string} requestId
 * @param {string} newStatus
 * @return {Object} { success: boolean, row: Array }
 */
function updateRequestStatus(requestId, newStatus) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAME);

  // Batch read — all data in one call
  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === requestId) {
      // Use LockService to prevent race conditions
      const lock = LockService.getScriptLock();
      lock.waitLock(10000); // wait up to 10s
      try {
        sheet.getRange(i + 1, 8).setValue(newStatus);
        SpreadsheetApp.flush(); // force write
      } finally {
        lock.releaseLock();
      }
      return { success: true, row: data[i] };
    }
  }

  return { success: false };
}

// ── Calendar ──

/**
 * Creates an all-day calendar event for approved leave.
 */
function createCalendarEvent(rowData) {
  const calendar = CalendarApp.getDefaultCalendar();
  const title = `${rowData[2]} — ${rowData[3]} Leave`;
  const startDate = new Date(rowData[4]);
  const endDate = new Date(rowData[5]);

  // Add one day to end date (all-day events are exclusive of end)
  endDate.setDate(endDate.getDate() + 1);

  calendar.createAllDayEvent(title, startDate, endDate, {
    description: `Reason: ${rowData[6]}\nRequest ID: ${rowData[0]}`,
  });
}

// ── Notifications ──

/**
 * Sends a notification email to the requester about the decision.
 */
function notifyRequester(rowData, status) {
  const requesterEmail = rowData[2];
  const subject = `Your leave request ${rowData[0]} — ${status}`;
  const body = [
    `Your ${rowData[3]} leave request has been ${status.toLowerCase()}.`,
    ``,
    `Dates: ${rowData[4]} to ${rowData[5]}`,
    `Request ID: ${rowData[0]}`,
  ].join('\n');

  GmailApp.sendEmail(requesterEmail, subject, body);
}

// ── Scheduled Reports ──

/**
 * Sends a weekly summary email.
 * Set up as a time-driven trigger (weekly, Mondays at 9am).
 *
 * To create: Edit > Triggers > Add Trigger >
 *   Function: weeklyReport, Event source: Time-driven,
 *   Type: Week timer, Day: Monday, Time: 9am-10am
 */
function weeklyReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAME);

  if (!sheet) return;

  const data = sheet.getDataRange().getValues().slice(1); // skip headers

  const pending  = data.filter(r => r[7] === 'PENDING').length;
  const approved = data.filter(r => r[7] === 'APPROVED').length;
  const rejected = data.filter(r => r[7] === 'REJECTED').length;

  const htmlBody = `
    <h2>Weekly Leave Summary</h2>
    <table style="border-collapse:collapse;">
      <tr><td style="padding:8px;">Pending</td>
          <td style="padding:8px;font-weight:bold;color:#f9ab00;">${pending}</td></tr>
      <tr><td style="padding:8px;">Approved</td>
          <td style="padding:8px;font-weight:bold;color:#34a853;">${approved}</td></tr>
      <tr><td style="padding:8px;">Rejected</td>
          <td style="padding:8px;font-weight:bold;color:#ea4335;">${rejected}</td></tr>
    </table>
  `;

  GmailApp.sendEmail(MANAGER_EMAIL, 'Weekly Leave Summary',
    `Pending: ${pending}, Approved: ${approved}, Rejected: ${rejected}`,
    { htmlBody: htmlBody }
  );
}

// ── Dashboard ──

/**
 * Refreshes a simple dashboard in a 'Dashboard' sheet.
 */
function refreshDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName(SHEET_NAME);
  if (!dataSheet) return;

  const dashboard = ss.getSheetByName('Dashboard')
    || ss.insertSheet('Dashboard');
  dashboard.clear();

  const data = dataSheet.getDataRange().getValues().slice(1);

  const stats = {
    total: data.length,
    pending: data.filter(r => r[7] === 'PENDING').length,
    approved: data.filter(r => r[7] === 'APPROVED').length,
    rejected: data.filter(r => r[7] === 'REJECTED').length,
  };

  // Write dashboard
  dashboard.getRange('A1').setValue('Leave Request Dashboard')
    .setFontSize(18).setFontWeight('bold');

  const summaryData = [
    ['Metric', 'Count'],
    ['Total Requests', stats.total],
    ['Pending', stats.pending],
    ['Approved', stats.approved],
    ['Rejected', stats.rejected],
  ];

  dashboard.getRange(3, 1, summaryData.length, 2).setValues(summaryData);
  dashboard.getRange(3, 1, 1, 2).setFontWeight('bold');

  // Create a chart
  const chart = dashboard.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(dashboard.getRange(4, 1, 3, 2)) // data rows only
    .setPosition(3, 4, 0, 0)
    .setOption('title', 'Request Status Distribution')
    .setOption('width', 400)
    .setOption('height', 300)
    .build();

  dashboard.insertChart(chart);

  SpreadsheetApp.getUi().alert('Dashboard refreshed!');
}

Sidebar.html — Leave Request Form UI

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    * { box-sizing: border-box; margin: 0; padding: 0; }
    body {
      font-family: 'Google Sans', Roboto, Arial, sans-serif;
      padding: 20px;
      color: #202124;
      background: #fff;
    }
    h3 { font-size: 18px; margin-bottom: 20px; color: #1a73e8; }

    .form-group { margin-bottom: 18px; }

    label {
      display: block;
      font-size: 12px;
      font-weight: 500;
      color: #5f6368;
      margin-bottom: 6px;
      text-transform: uppercase;
      letter-spacing: 0.3px;
    }

    input, select, textarea {
      width: 100%;
      padding: 10px 12px;
      border: 1px solid #dadce0;
      border-radius: 6px;
      font-size: 14px;
      font-family: inherit;
      transition: border-color 0.2s;
    }
    input:focus, select:focus, textarea:focus {
      outline: none;
      border-color: #1a73e8;
      box-shadow: 0 0 0 2px rgba(26,115,232,0.15);
    }

    textarea { resize: vertical; min-height: 70px; }

    .btn {
      width: 100%;
      padding: 12px;
      background: #1a73e8;
      color: white;
      border: none;
      border-radius: 6px;
      font-size: 14px;
      font-weight: 500;
      cursor: pointer;
      transition: background 0.2s;
    }
    .btn:hover { background: #1557b0; }
    .btn:disabled { background: #dadce0; cursor: not-allowed; }

    .message {
      margin-top: 16px;
      padding: 12px;
      border-radius: 6px;
      font-size: 13px;
      display: none;
    }
    .message.success {
      display: block;
      background: #e6f4ea;
      color: #137333;
      border: 1px solid #ceead6;
    }
    .message.error {
      display: block;
      background: #fce8e6;
      color: #c5221f;
      border: 1px solid #f5c6cb;
    }

    .spinner {
      display: inline-block;
      width: 16px;
      height: 16px;
      border: 2px solid #fff;
      border-top-color: transparent;
      border-radius: 50%;
      animation: spin 0.8s linear infinite;
      vertical-align: middle;
      margin-right: 6px;
    }
    @keyframes spin { to { transform: rotate(360deg); } }
  </style>
</head>
<body>
  <h3>Submit Leave Request</h3>

  <form id="leaveForm" onsubmit="return false;">
    <div class="form-group">
      <label for="leaveType">Leave Type</label>
      <select id="leaveType">
        <option value="Annual">Annual Leave</option>
        <option value="Sick">Sick Leave</option>
        <option value="Personal">Personal Leave</option>
        <option value="Parental">Parental Leave</option>
        <option value="Unpaid">Unpaid Leave</option>
      </select>
    </div>

    <div class="form-group">
      <label for="startDate">Start Date</label>
      <input type="date" id="startDate" required>
    </div>

    <div class="form-group">
      <label for="endDate">End Date</label>
      <input type="date" id="endDate" required>
    </div>

    <div class="form-group">
      <label for="reason">Reason</label>
      <textarea id="reason" rows="3"
                placeholder="Brief description..."></textarea>
    </div>

    <button class="btn" id="submitBtn" onclick="submitForm()">
      Submit Request
    </button>
  </form>

  <div id="message"></div>

  <script>
    function submitForm() {
      // Basic validation
      const startDate = document.getElementById('startDate').value;
      const endDate = document.getElementById('endDate').value;

      if (!startDate || !endDate) {
        showMessage('Please fill in all date fields.', 'error');
        return;
      }

      if (new Date(endDate) < new Date(startDate)) {
        showMessage('End date must be after start date.', 'error');
        return;
      }

      // Collect form data
      const formData = {
        leaveType: document.getElementById('leaveType').value,
        startDate: startDate,
        endDate: endDate,
        reason: document.getElementById('reason').value || '(No reason provided)',
      };

      // Disable button, show spinner
      const btn = document.getElementById('submitBtn');
      btn.disabled = true;
      btn.innerHTML = '<span class="spinner"></span> Submitting...';

      // Call server-side function via google.script.run
      google.script.run
        .withSuccessHandler(onSuccess)
        .withFailureHandler(onError)
        .submitLeaveRequest(formData);
    }

    function onSuccess(result) {
      showMessage(
        'Request submitted! ID: ' + result.requestId +
        '. Your manager has been notified.',
        'success'
      );
      document.getElementById('leaveForm').reset();
      resetButton();
    }

    function onError(err) {
      showMessage('Error: ' + err.message, 'error');
      resetButton();
    }

    function showMessage(text, type) {
      const el = document.getElementById('message');
      el.textContent = text;
      el.className = 'message ' + type;
    }

    function resetButton() {
      const btn = document.getElementById('submitBtn');
      btn.disabled = false;
      btn.textContent = 'Submit Request';
    }
  </script>
</body>
</html>

appsscript.json — Project Manifest

{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_WITHIN_DOMAIN"
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/gmail.send",
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/script.send_mail"
  ]
}

Note: Explicitly declaring oauthScopes in the manifest gives you fine-grained control over permissions. Without this, Apps Script auto-detects scopes (often requesting broader access than needed).

Project architecture summary This project has 3 files: Code.gs (server logic), Sidebar.html (client UI), and appsscript.json (manifest). In a real project, you might split Code.gs into multiple .gs files (e.g., Triggers.gs, Email.gs, Web.gs). Apps Script merges all .gs files into a single execution scope — the split is purely organizational. No imports needed between .gs files.

08 Interview Cheat Sheet

Things to Say That Make You Sound Fluent

"I'd use a bound script here since we need getActiveSpreadsheet() context."
"We should batch the reads — call getValues() once and process the 2D array in memory."
"For that we'd need an installable trigger, since onEdit as a simple trigger can't access Gmail."
"I'd store that API key in Script Properties, not hardcode it."
"The 6-minute execution limit means we might need a continuation pattern — save progress to Properties and resume via a time-driven trigger."
"The sidebar communicates with the server via google.script.run — it's an async RPC bridge."
"For the Web App to pick up changes, you need to create a new deployment — or use the test deployment URL during development."
"UrlFetchApp.fetchAll() is better here — it batches multiple HTTP requests in parallel."
"We should use LockService since multiple users could submit simultaneously."
"If we need features beyond what SpreadsheetApp offers, we can enable the Sheets Advanced Service which wraps the REST API v4."

Common Interview Questions & Strong Answers

"What's the difference between Apps Script and Cloud Functions?"

Apps Script is purpose-built for Google Workspace — it has zero-config auth, native service objects for Sheets/Gmail/Drive, a built-in IDE, and a free tier. Cloud Functions are general-purpose serverless compute — more flexible (any language, any trigger, VPC access), but require manual OAuth setup, API client libraries, and billing. Use Apps Script when the workflow centers on Workspace products; use Cloud Functions when you need more control, longer execution times, or non-Google integrations.

"How do you handle scripts that take longer than 6 minutes?"

Continuation pattern: (1) Track progress in PropertiesService (e.g., last processed row index). (2) Check elapsed time with Date.now(). (3) When approaching the limit (~5 min), save state and create a time-driven trigger to resume. (4) The resumed function reads saved state and continues. (5) Clean up the trigger when done. This is a well-established pattern in the Apps Script community.

"How do you test Apps Script code?"

There's no built-in test framework. Options: (1) Manual testing via the Run button in the editor. (2) Test functions that assert expected behavior and log results. (3) clasp + TypeScript + Jest for local unit testing — extract pure logic into testable functions, mock the service objects. (4) Execution log for debugging (console.log() with V8). The key is separating business logic from service calls so you can test the logic locally.

"How do you manage source control for Apps Script?"

clasp (Command Line Apps Script Projects). clasp clone pulls the project locally, clasp push deploys. Files are .js (or .ts for TypeScript) locally and get transpiled to .gs on push. You can version the local files in Git normally. clasp also supports TypeScript out of the box — install @types/google-apps-script for full autocomplete.

"What are the security considerations?"

Key concerns: (1) OAuth scopes — minimize requested permissions; declare them explicitly in the manifest. (2) Secrets management — use PropertiesService, never hardcode. (3) Web App access control — choose "Anyone within domain" over "Anyone" when possible. (4) Input validation in doGet/doPost. (5) Content sanitization — use HtmlService.createHtmlOutput() (auto-sanitizes) rather than building raw HTML strings for user-facing content. (6) Add-on review — marketplace add-ons go through Google's review process.

"When would you NOT use Apps Script?"

When you need: (1) execution times > 6 minutes without the continuation workaround, (2) real-time / sub-second latency, (3) heavy compute or ML workloads, (4) non-Google service integrations as the primary use case, (5) complex dependency management (npm ecosystem), (6) multi-language support, (7) fine-grained IAM / VPC networking. In these cases, Cloud Functions, Cloud Run, or a traditional backend are better fits.

Quick Reference Card

Need	Use
Read/write spreadsheet	`SpreadsheetApp.getActive().getSheetByName('X').getDataRange().getValues()`
Send email	`GmailApp.sendEmail(to, subject, body, {htmlBody})`
HTTP request	`UrlFetchApp.fetch(url, {method, headers, payload, muteHttpExceptions})`
Create calendar event	`CalendarApp.getDefaultCalendar().createEvent(title, start, end)`
Show sidebar	`HtmlService.createHtmlOutputFromFile('X');` → `getUi().showSidebar(html)`
Call server from HTML	`google.script.run.withSuccessHandler(fn).serverFunction(args)`
Store config/secrets	`PropertiesService.getScriptProperties().setProperty(k, v)`
Cache data	`CacheService.getScriptCache().put(k, v, ttlSeconds)`
Mutual exclusion	`LockService.getScriptLock().waitLock(ms)`
Current user	`Session.getActiveUser().getEmail()`
Generate UUID	`Utilities.getUuid()`
Return JSON from Web App	`ContentService.createTextOutput(JSON.stringify(x)).setMimeType(ContentService.MimeType.JSON)`
Create trigger programmatically	`ScriptApp.newTrigger('fn').timeBased().everyHours(1).create()`
Local dev CLI	`clasp push / pull / deploy / open`

Built for an interview. Go get it.