Build a two-way Sheets-Airtable sync

Northwind’s delivery team lives in Airtable — its views, its linked records, its mobile app. Finance and the leadership team live in Google Sheets, where the project tracker sits next to budgets and forecasts. Neither team will move, so the project list has to exist in both places and stay consistent.

This script keeps the two in step. pullFromAirtable brings Airtable’s records down into the sheet, and pushToAirtable sends the sheet’s rows back up. It is a deliberately simple pattern — Airtable’s field names match the sheet’s column headers exactly, which is what lets one mapping serve both directions. Run the two functions on a schedule and the tracker is the same wherever you open it.

What you’ll need

• An Airtable base with a table whose field names match your sheet’s column headers exactly — same spelling, same case. The mapping depends on it.
• An Airtable personal access token with data.records:read and data.records:write scopes on that base. Create one at airtable.com/create/tokens.
• That token saved as AIRTABLE_KEY in Script Properties — see Store API keys and secrets securely.
• Your base ID (starts with app) and the table name, set in the config below.
• A Google Sheet whose first tab holds the tracker, with column headers in row 1. Put its ID in the config too.

The script

// --- Airtable connection ---
const AIRTABLE_BASE = 'appXXX';        // your base ID, starts with "app"
const AIRTABLE_TABLE = 'Projects';     // the table name to sync
const AIRTABLE_KEY = PropertiesService.getScriptProperties()
  .getProperty('AIRTABLE_KEY');

// --- Sheets connection ---
const PROJECTS_SHEET_ID = '1abcProjectsId';

// The Airtable REST endpoint for this base + table. Field names in the
// response and request must match the sheet's column headers.
const AIRTABLE_URL =
  'https://api.airtable.com/v0/' + AIRTABLE_BASE + '/' +
  encodeURIComponent(AIRTABLE_TABLE);

/**
 * Pulls every record from Airtable into the project sheet, paging through
 * results and rebuilding the rows under the existing header.
 */
function pullFromAirtable() {
  const sheet = SpreadsheetApp.openById(PROJECTS_SHEET_ID).getSheets()[0];
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];

  // 1. Page through Airtable. It returns 100 records at a time and hands
  //    back an "offset" token while more remain.
  const records = [];
  let offset = '';
  do {
    const url = AIRTABLE_URL + (offset ? '?offset=' + offset : '');
    const response = UrlFetchApp.fetch(url, {
      headers: { Authorization: 'Bearer ' + AIRTABLE_KEY },
      muteHttpExceptions: true,
    });
    if (response.getResponseCode() !== 200) {
      throw new Error('Airtable read failed: ' + response.getContentText());
    }
    const page = JSON.parse(response.getContentText());
    records.push(...page.records);
    offset = page.offset || '';
  } while (offset);

  // 2. Build one row per record, picking fields in the sheet's column
  //    order. A missing field becomes an empty cell (?? '').
  const rows = records.map((r) => headers.map((h) => r.fields[h] ?? ''));

  // 3. Clear the old data rows (everything below the header) and write
  //    the fresh set, so the sheet mirrors Airtable exactly.
  if (sheet.getLastRow() > 1) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, headers.length).clearContent();
  }
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }
  Logger.log('Pulled ' + rows.length + ' records from Airtable.');
}

/**
 * Pushes every sheet row up to Airtable as a new record. Run this when the
 * sheet is the source of truth — see "Watch out for" on duplicates.
 */
function pushToAirtable() {
  const [headers, ...rows] = SpreadsheetApp.openById(PROJECTS_SHEET_ID)
    .getSheets()[0].getDataRange().getValues();

  if (!rows.length) {
    Logger.log('No rows to push — nothing to do.');
    return;
  }

  for (const row of rows) {
    // Turn the row into an Airtable "fields" object, keyed by header.
    const fields = Object.fromEntries(headers.map((k, i) => [k, row[i]]));

    const response = UrlFetchApp.fetch(AIRTABLE_URL, {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + AIRTABLE_KEY },
      payload: JSON.stringify({ fields }),
      muteHttpExceptions: true,
    });
    if (response.getResponseCode() !== 200) {
      Logger.log('Push failed for a row: ' + response.getContentText());
    }

    // Airtable's API allows 5 requests per second per base. A short pause
    // keeps a long sheet from tripping the rate limit.
    Utilities.sleep(250);
  }
  Logger.log('Pushed ' + rows.length + ' rows to Airtable.');
}

How it works

1. Connection details — base ID, table name, sheet ID — sit in named constants at the top. AIRTABLE_URL builds the REST endpoint once, with the table name URL-encoded so spaces in the name do not break the request.
2. pullFromAirtable reads the sheet’s header row first. Those headers drive the mapping, so the column order in the sheet decides the column order of the pulled data.
3. It pages through Airtable in a do...while loop — Airtable returns 100 records per request and supplies an offset token while more remain — so a table of any size comes down in full.
4. Each record becomes a row by reading r.fields[header] for every column. The ?? '' turns a missing field into an empty cell instead of undefined.
5. It clears only the data rows beneath the header, then writes the fresh set in one setValues call, leaving the header untouched.
6. pushToAirtable does the reverse: it reads every row, rebuilds a fields object keyed by header, and POSTs it as a new record. A 250 ms pause between requests keeps a long sheet inside Airtable’s five-requests-per-second limit.

Example run

The Airtable Projects table holds three records:

Name	Status	Owner
Website refresh	In progress	Priya
Q3 campaign	Done	Marcus
Warehouse move	Planning	Dani

After pullFromAirtable, the sheet’s first tab — with headers Name, Status, Owner in row 1 — holds those same three rows. Edit a status in the sheet, run pushToAirtable, and Airtable gains records carrying the updated values. The two surfaces now show the same project list.

Trigger it

Run the directions on a clock so the tracker stays current without anyone remembering to sync:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a Time-driven trigger for pullFromAirtable on an Hour timer.
3. Pick whichever direction is the source of truth for your team and trigger only that one automatically; run the other by hand when needed.

Running both directions on a timer is risky — see the first pitfall below. Most teams schedule one direction and treat it as the master.

Watch out for

• pushToAirtable always creates records — it never updates. Running it twice produces duplicates in Airtable. Treat it as a one-way export, or extend it to look up an Airtable record ID per row and PATCH instead of POST.
• This is not true conflict resolution. If the same row changes in both places between syncs, whichever direction runs last wins and the other edit is lost. Pick one surface as the source of truth.
• Field names must match column headers exactly — same spelling, same case. A mismatch silently drops that column rather than throwing an error.
• Airtable limits the API to 5 requests per second per base. The Utilities.sleep pause respects that; do not remove it for a large table.
• Field types matter on push. Airtable rejects a write to a single-select or linked-record field whose value does not already exist as an option, so keep sheet values consistent with the Airtable schema.
• A large table means many pages and, on push, many requests — each one counts against Apps Script’s quotas and the six-minute execution limit. For very big tables, batch the writes (Airtable accepts up to 10 records per request) or sync in chunks across triggers.