🗂 Project Structure

Monolithic scripts become unmaintainable fast. Break your project into focused files from day one. Apps Script supports multiple .gs files that share a global scope — use that to your advantage.

Recommended File Layout

// config.gs — all magic numbers & constants
const CONFIG = {
  SHEET_ID: '1BxiM...',
  MAX_ROWS:  500,
  NOTIFY_EMAIL: 'ops@company.com'
};

// utils.gs — pure helper functions
function getSheet(name) {
  return SpreadsheetApp
    .openById(CONFIG.SHEET_ID)
    .getSheetByName(name);
}

// main.gs — entry points & orchestration
function runDailyReport() {
  const data = fetchData();
  writeToSheet(data);
  sendSummary(data);
}

⏱ Quota & Rate Limits

Google enforces strict daily quotas on API calls — exceeding them silently fails your script. The single biggest lever is batching: replace per-cell reads with range reads and per-row writes with setValues().

The Golden Rule: Batch Everything

// ❌ SLOW — one API call per cell (500 calls for 500 rows)
for (let i = 1; i <= 500; i++) {
  sheet.getRange(i, 1).setValue(data[i]);
}

// ✅ FAST — single API call for all 500 rows
const values = data.map(row => [row]);
sheet.getRange(1, 1, values.length, 1)
     .setValues(values);

*Subject to the 6-minute execution limit — batch reads prevent timeout.

🛡 Error Handling

Unhandled exceptions in timed triggers fail silently. Wrap every entry point in a try/catch, log to Stackdriver, and notify a human when something breaks.

Resilient Entry Point Pattern

function runWithGuard(fn, label) {
  try {
    fn();
    console.log(`[OK] ${label} completed`);
  } catch (err) {
    const msg = `[ERROR] ${label}: ${err.message}`;
    console.error(msg);
    GmailApp.sendEmail(
      CONFIG.NOTIFY_EMAIL,
      'Script Failure: ' + label,
      msg + '\n\nStack:\n' + err.stack
    );
  }
}

// Usage
function dailyTrigger() {
  runWithGuard(runDailyReport, 'Daily Report');
}

Retry with Exponential Backoff

External APIs and Sheets can return transient errors. A simple retry loop prevents false failures:

function withRetry(fn, maxAttempts = 3) {
  for (let i = 0; i < maxAttempts; i++) {
    try {
      return fn();
    } catch (e) {
      if (i === maxAttempts - 1) throw e;
      Utilities.sleep((2 ** i) * 1000); // 1s, 2s, 4s
    }
  }
}

🔐 Security & Auth

Never hardcode credentials. Apps Script offers two secure storage mechanisms depending on your use case.

Script Properties vs User Properties

// Store once (run manually or via setup function)
PropertiesService.getScriptProperties()
  .setProperty('API_KEY', 'sk-...');

// Read at runtime — never hardcoded
function callExternalAPI() {
  const key = PropertiesService
    .getScriptProperties()
    .getProperty('API_KEY');

  const res = UrlFetchApp.fetch('https://api.example.com/data', {
    headers: { 'Authorization': `Bearer ${key}` }
  });
  return JSON.parse(res.getContentText());
}

⚡ Performance

The 6-minute execution cap is your hardest constraint. These patterns keep you well inside it.

Cache API Results

function getCachedData(key, fetchFn, ttl = 300) {
  const cache = CacheService.getScriptCache();
  const cached = cache.get(key);
  if (cached) return JSON.parse(cached);

  const fresh = fetchFn();
  cache.put(key, JSON.stringify(fresh), ttl);
  return fresh;
}

// Usage — avoids repeated Sheets reads in the same exec
const config = getCachedData('config', () => loadConfigSheet());

Avoid Synchronous Patterns in Loops

// ❌ N+1 pattern — each iteration fetches separately
rows.forEach(row => {
  const extra = sheet.getRange(row, 5).getValue();
});

// ✅ Fetch the whole column once, then iterate
const extras = sheet
  .getRange(1, 5, rows.length, 1)
  .getValues()
  .flat();

rows.forEach((row, i) => {
  const extra = extras[i];
});

🧪 Testing

Apps Script has no built-in test runner, but a lightweight pattern with assertions goes a long way. Keep business logic in pure functions so it can be tested without Sheets access.

Minimal Test Harness

function runTests() {
  const results = [];

  function assert(label, actual, expected) {
    const pass = JSON.stringify(actual) === JSON.stringify(expected);
    results.push({ label, pass, actual, expected });
  }

  // Test pure functions directly
  assert('formatCurrency',    formatCurrency(1234.5),  '$1,234.50');
  assert('parseDate',         parseDate('2026-04-22'), new Date('2026-04-22'));
  assert('filterActiveRows', filterActive([...]).length, 3);

  results.forEach(r =>
    console.log(r.pass ? `✓ ${r.label}` : `✗ ${r.label} — got ${r.actual}`)
  );
}

🔔 Triggers

Triggers run without user interaction — which means they run without a user to debug them. Design trigger-driven scripts to be idempotent and self-healing.

Programmatic Trigger Management

/**
 * Safe setup: removes duplicates before creating.
 * Call this once from the script editor.
 */
function setupTriggers() {
  // Remove all existing triggers for this function
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === 'dailyTrigger')
    .forEach(t => ScriptApp.deleteTrigger(t));

  // Create exactly one trigger
  ScriptApp.newTrigger('dailyTrigger')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

🚀 Deployment

Always work from a versioned deployment, not HEAD. Sharing a web app or API endpoint from the "latest code" means any save could break production.

appsscript.json Checklist

{
  "timeZone": "America/Chicago",
  "dependencies": {},
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/gmail.send"
    // Declare ONLY what your script uses
  ],
  "webapp": {
    "executeAs": "USER_ACCESSING",
    "access": "DOMAIN"
  }
}