# Airtable Scripting Patterns

Production-tested patterns for common automation scripting tasks. Each pattern includes the full implementation and notes on where it has been validated.

---

## 1. UPSERT (Sync) Pattern

The foundational pattern for syncing external data into Airtable. Keyed on a source system ID, it creates new records or updates existing ones. Safe to re-run without duplication.

```javascript
/**
 * UPSERT: Sync external records into an Airtable table.
 * - Dedup key: a unique ID from the source system (e.g., Marketo ID)
 * - Diff logic: only update fields that have actually changed
 * - Change log: append timestamped diff to a changelog field
 */

var table = base.getTable(TABLE_NAME);
var query = await table.selectRecordsAsync({
  fields: [DEDUP_FIELD, /* ... other fields to compare ... */]
});

// Build lookup map: source ID -> Airtable record
var existingMap = new Map();
for (var rec of query.records) {
  var key = rec.getCellValue(DEDUP_FIELD);
  if (key != null) {
    existingMap.set(String(key), rec);
  }
}

var creates = [];
var updates = [];
var unchanged = 0;

for (var item of sourceData) {
  var existing = existingMap.get(String(item.id));

  if (!existing) {
    // New record
    creates.push({
      fields: {
        [DEDUP_FIELD]: item.id,
        "Name": item.name,
        "Status": item.status,
        // ... map all fields
      }
    });
  } else {
    // Existing record: diff field-by-field
    var changes = {};
    var diffs = [];

    if (String(existing.getCellValueAsString("Name")) !== String(item.name || "")) {
      changes["Name"] = item.name;
      diffs.push("Name: " + existing.getCellValueAsString("Name") + " -> " + item.name);
    }
    // ... repeat for each field

    if (Object.keys(changes).length > 0) {
      // Append to change log
      var existingLog = existing.getCellValueAsString("Change Log") || "";
      changes["Change Log"] = existingLog + "\n[" + isoNow() + "] " + diffs.join("; ");
      updates.push({ id: existing.id, fields: changes });
    } else {
      unchanged++;
    }
  }
}

// Write in batches of 50
for (var i = 0; i < creates.length; i += 50) {
  if (timeRemaining() < 5000) break;
  await table.createRecordsAsync(creates.slice(i, i + 50));
  console.log("Created batch " + (Math.floor(i / 50) + 1));
}

for (var i = 0; i < updates.length; i += 50) {
  if (timeRemaining() < 5000) break;
  await table.updateRecordsAsync(updates.slice(i, i + 50));
  console.log("Updated batch " + (Math.floor(i / 50) + 1));
}

console.log("Created: " + creates.length + ", Updated: " + updates.length + ", Unchanged: " + unchanged);
```

**Key design decisions:**
- The dedup key is always the source system's ID, never the Airtable record ID
- Field-by-field diff prevents unnecessary writes (Airtable charges against rate limits per write call, not per field)
- Change log provides an audit trail without requiring a separate history table
- Time budget check before each batch prevents mid-write timeout

---

## 2. Scout-Then-Collect Pattern

Split data ingestion into two phases to stay within fetch() and time limits:

**Phase 1 (Scout):** Lightweight script that paginates through the source API, counting and identifying targets. Writes a summary (total count, IDs, metadata) to a field or output variable. Runs fast because it only reads, never writes individual records.

**Phase 2 (Collect):** Triggered after the scout completes. Reads the scout's output, then does the heavy work: fetching detailed data per item and upserting into Airtable. Can be split into multiple chained scripts if the volume exceeds a single script's budget.

This pattern is used across Email Assets, Email Templates, Forms, Snippets, Smart Lists, Smart Campaigns, and Programs.

---

## 3. Chained Automation Structure

For work that exceeds a single script's 180s/50-fetch budget, chain multiple script actions in a single automation:

```
Automation Trigger (e.g., checkbox = true)
  -> Script 1: Process items 0-N, output exitReason and lastOffset
  -> Conditional: if exitReason !== "completed"
    -> Script 2: input StartOffset from Script 1's lastOffset, process next batch
    -> Conditional: if exitReason !== "completed"
      -> Script 3: ...
      -> Script 4: ...
```

Each script receives a `StartOffset` input variable and outputs:
- `exitReason`: "completed", "timeout", or "fetchLimit"
- `lastOffset`: where to resume
- `processedCount`: for logging

```javascript
// At the start of each chained script
var config = input.config();
var START_OFFSET = Number(config.StartOffset) || 0;

// ... do work starting from START_OFFSET ...

// At the end
output.set("exitReason", timedOut ? "timeout" : "completed");
output.set("lastOffset", currentOffset);
output.set("processedCount", processed);
```

---

## 4. System Panel Integration

A central "System Panel" table tracks the status of each sync/automation. Scripts update it at start and end to provide a unified operations dashboard.

```javascript
var systemPanelTable = base.getTable("System Panel");
var SYSTEM_RECORD_ID = config.RecordId;  // from automation trigger

// Mark "In Progress" at script start
await systemPanelTable.updateRecordAsync(SYSTEM_RECORD_ID, {
  "Run Status": { name: "In Progress" },
  "Last Run Start": new Date().toISOString(),
  "Run ID": RUN_ID
});

try {
  // ... main logic ...

  // Mark "Done" on success
  await systemPanelTable.updateRecordAsync(SYSTEM_RECORD_ID, {
    "Run Status": { name: "Done" },
    "Last Run End": new Date().toISOString(),
    "Records Processed": processedCount,
    "Resync": false  // clear the trigger
  });
} catch (err) {
  // Mark "Error" on failure
  await systemPanelTable.updateRecordAsync(SYSTEM_RECORD_ID, {
    "Run Status": { name: "Error" },
    "Error Log": err.message,
    "Resync": false  // prevent infinite retry loops
  });
}
```

**Resync flag management:** Always clear the Resync checkbox on both success and error. If you only clear on success, a persistent error will trigger an infinite retry loop. Surface the error in a field and let a human re-trigger after investigating.

---

## 5. Bearer Token Self-Refresh

For scripts that run in long pipelines (multiple chained steps sharing a token), the bearer token may expire mid-run. Build token refresh into the fetch helper:

```javascript
var bearerToken = null;
var tokenExpiresAt = 0;

async function getToken() {
  var clientId = input.secret("MarketoProdAirPulseClientId");
  var clientSecret = input.secret("MarketoProdAirPulseClientSecret");
  var url = BASE_URL + "/identity/oauth/token?grant_type=client_credentials"
    + "&client_id=" + clientId + "&client_secret=" + clientSecret;

  var res = await trackedFetch(url);
  var raw = await res.text();
  var json = JSON.parse(raw);

  if (json.error) {
    throw new Error("Token failure: " + json.error_description);
  }

  bearerToken = json.access_token;
  // Marketo tokens last ~3600s; refresh at 80% of lifetime
  tokenExpiresAt = Date.now() + (json.expires_in * 800);
  console.log("Token acquired (expires_in: " + json.expires_in + "s)");
  return bearerToken;
}

async function getValidToken() {
  if (!bearerToken || Date.now() > tokenExpiresAt) {
    return getToken();
  }
  return bearerToken;
}

// In the fetch helper, detect auth failures and refresh
async function apiFetch(url) {
  var token = await getValidToken();
  var res = await trackedFetch(url, {
    headers: { "Authorization": "Bearer " + token }
  });
  var raw = await res.text();
  var json = JSON.parse(raw);

  // Detect expired token
  if (json.success === false) {
    var code = json.errors && json.errors[0] && json.errors[0].code;
    if (code === "601" || code === "602") {
      console.log("Token expired mid-run, refreshing...");
      token = await getToken();
      res = await trackedFetch(url, {
        headers: { "Authorization": "Bearer " + token }
      });
      raw = await res.text();
      json = JSON.parse(raw);
    }
  }
  return json;
}
```

**Important:** Marketo's identity endpoint returns `200 OK` even on auth failures. The error is in the JSON body (`json.error`), not the HTTP status. Always check the response body.

---

## 6. Marketo Timestamp Normalization

Marketo returns timestamps in a non-standard format that breaks `new Date()`:

```
"2023-09-08T18:41:00Z+0000"   // Marketo format (broken)
"2023-09-08T18:41:00Z"         // Standard ISO 8601
```

The `Z+0000` suffix is redundant (`Z` already means UTC) and causes `Invalid Date`.

```javascript
function cleanMarketoDate(dateStr) {
  if (!dateStr) return null;
  // Remove the redundant +0000 after Z
  var cleaned = String(dateStr).replace(/Z\+0000$/, "Z");
  var d = new Date(cleaned);
  return isNaN(d.getTime()) ? null : d.toISOString();
}
```

**When sending datetimes TO Marketo:** Use the format `YYYY-MM-DDTHH:MM:SS+0000` (no Z, explicit +0000 offset).

---

## 7. Write-Back (Push) Pattern

For pushing changes from Airtable back to an external system (e.g., updating Marketo program tokens):

**Staging:** User writes changes to a structured richText field. One change per line in the format `Token Name = New Value`.

**Validation:** Script reads the staged field, parses each line, then validates against the live state:
- Does the token name exist in the target system?
- What is its current type? (inherit automatically, never ask the user)
- Is the new value actually different from live?

**Push:** For each validated change, call the target API. Log success/failure per item.

**Refresh:** After pushing, re-read the live state from the API and update the "Live" field so the user can confirm without leaving Airtable.

```javascript
// Read staged changes from richText (must use selectRecordAsync, not input.config)
var record = await table.selectRecordAsync(recordId);
var stagedText = record.getCellValueAsString("Tokens (Staged)");

// Parse "Token Name = Value" lines
var changes = [];
var lines = stagedText.split("\n");
for (var line of lines) {
  var parts = line.split("=");
  if (parts.length >= 2) {
    changes.push({
      name: parts[0].trim(),
      value: parts.slice(1).join("=").trim()
    });
  }
}

// Validate against live tokens (GET first, then POST)
var liveTokens = await getLiveTokens(programId);
for (var change of changes) {
  var match = liveTokens.find(function(t) { return t.name === change.name; });
  if (!match) {
    errors.push("Token not found: " + change.name);
    continue;
  }
  change.type = match.type;  // inherit type from live
  if (match.value === change.value) {
    skipped.push(change.name + " (already matches)");
    continue;
  }
  // Push the change
  await pushToken(programId, change);
}
```

**Key safety guardrails:**
- Update only: if a token name does not exist, the push is blocked. No accidental creation.
- Auto type matching: the script reads the current type from the API and uses it. Users never specify type.
- Name validation: exact match required. A typo is caught before anything is pushed.
- Idempotent: if the staged value already matches live, the push is skipped.

---

## 8. Marketo POST with URL-Encoded Body

Marketo's token update endpoint requires `application/x-www-form-urlencoded`, not JSON:

```javascript
async function pushToken(programId, token) {
  var url = BASE_URL + "/rest/asset/v1/folder/" + programId + "/tokens.json";
  var body = "name=" + encodeURIComponent(token.name)
    + "&value=" + encodeURIComponent(token.value)
    + "&type=" + encodeURIComponent(token.type)
    + "&folderType=Program";

  var res = await trackedFetch(url, {
    method: "POST",
    headers: {
      "Authorization": "Bearer " + bearerToken,
      "Content-Type": "application/x-www-form-urlencoded"
    },
    body: body
  });
  var raw = await res.text();
  return JSON.parse(raw);
}
```

**Note:** Marketo's GET tokens response nests results at `result[0].tokens`, not directly in `result`.

---

## 9. Pagination Patterns

### Offset-Based (Marketo Asset APIs)

```javascript
var offset = 0;
var PAGE_SIZE = 200;
var allResults = [];

while (true) {
  if (timeRemaining() < 15000) break;

  var url = BASE_URL + "/rest/asset/v1/forms.json?maxReturn=" + PAGE_SIZE + "&offset=" + offset;
  var data = await apiFetch(url);

  if (!data.result || data.result.length === 0) break;

  allResults = allResults.concat(data.result);
  offset += data.result.length;

  console.log("Page " + Math.ceil(offset / PAGE_SIZE) + ": " + allResults.length + " total");

  if (data.result.length < PAGE_SIZE) break;  // last page
  busyWait(250);  // rate limit courtesy
}
```

### Cursor-Based (Marketo Activity APIs)

Marketo activities use a global stream cursor, not per-record pagination:

```javascript
// Get a paging token for the start of the window
var tokenUrl = BASE_URL + "/rest/v1/activities/pagingtoken.json?sinceDatetime=" + startDateIso;
var tokenResp = await apiFetch(tokenUrl);
var nextPageToken = tokenResp.nextPageToken;

var activities = [];
var more = true;
var page = 0;
var MAX_PAGES = 20;

while (more && page < MAX_PAGES) {
  if (timeRemaining() < 10000) break;
  page++;

  var url = BASE_URL + "/rest/v1/activities.json"
    + "?nextPageToken=" + encodeURIComponent(nextPageToken)
    + "&leadIds=" + leadId
    + "&activityTypeIds=" + activityTypeIds;

  var data = await apiFetch(url);
  activities = activities.concat(data.result || []);
  more = data.moreResult || false;
  nextPageToken = data.nextPageToken;

  if (more) busyWait(500);
}
```

**Critical:** The paging token is a cursor into the global activity stream. Empty pages for your specific `leadIds` do NOT mean there are no more activities. The cursor must continue advancing until `moreResult` is false. Exiting early on the first empty page is a known bug that causes missing data.

---

## 10. Databricks SQL from Airtable

For running SQL queries against a Databricks warehouse:

```javascript
var DATABRICKS_TOKEN = input.secret("DATABRICKS_PAT");
var WORKSPACE_HOST = "https://your-workspace.cloud.databricks.com";
var WAREHOUSE_ID = config.warehouseId;

var response = await fetch(WORKSPACE_HOST + "/api/2.0/sql/statements", {
  method: "POST",
  headers: {
    "Authorization": "Bearer " + DATABRICKS_TOKEN,
    "Content-Type": "application/json"
  },
  body: JSON.stringify({
    warehouse_id: WAREHOUSE_ID,
    statement: sqlQuery,
    wait_timeout: "20s",
    on_wait_timeout: "CANCEL",
    format: "JSON_ARRAY",
    disposition: "INLINE"
  })
});

var result = await response.json();

if (result.status && result.status.state === "SUCCEEDED") {
  var rows = (result.result && result.result.data_array) || [];
  console.log("Returned " + rows.length + " rows");
} else if (result.status && result.status.state === "CANCELED") {
  console.log("Query exceeded timeout");
} else {
  console.log("Query failed: " + JSON.stringify(result.status));
}
```

**Key parameters:**
- `wait_timeout: "20s"` with `on_wait_timeout: "CANCEL"` prevents orphaned queries
- `disposition: "INLINE"` returns results in the response body (limit ~25MB)
- `format: "JSON_ARRAY"` is more compact than JSON_OBJECT
- Leave 10s buffer between `wait_timeout` and Airtable's script timeout for network overhead

For queries that cannot complete in 20s, use an async callback pattern: submit a Databricks job, write the `run_id` to Airtable, and have Databricks call back to Airtable's REST API when done.
