New Tomorrow Partners: Resource Forecasting

Consulting / Resource Planning

New Tomorrow Partners: Resource Forecasting Middleware

Harvest API Airtable API Apps Script Idempotent Sync

The Mission A consulting firm was tracking hours in Harvest and staffing in Airtable, separately. I built middleware that syncs the two, calculates utilization rates, and projects burn charts in one place.

System Architecture

Key Engineering Challenges

API Rate Limiting & Idempotency: Airtable's strict rate limits required a syncIdempotent function that fetches all existing record IDs, builds a hash map, and batches separate toInsert / toUpdate queues to minimize round-trips.
Data Normalization: Harvest stores data in nested JSON. The middleware flattens it into a relational structure, calculating Burn Rate and Projected Finish dates on the fly.

Code Snippet

syncIdempotent.js

// From syncIdempotent()
// Logic: Batching requests to respect API limits & prevent duplicates

function syncIdempotent(sheetName, tableId, uniqueFieldName) {
  // 1. Fetch all existing Airtable records to build a Lookup Map
  const existingRecords = fetchAllAirtableRecords(tableId);
  const recordMap = new Map();
  existingRecords.forEach(r => recordMap.set(r.fields[uniqueFieldName], r.id));

  // 2. Diff Local Data vs Remote Data
  const {hdr, data} = read(sheetName);
  const toInsert = [];
  const toUpdate = [];

  data.forEach((row) => {
    const uniqueVal = row[uniqueColIndex];
    const fields = mapRowToFields(row);

    if (recordMap.has(uniqueVal)) {
      toUpdate.push({ id: recordMap.get(uniqueVal), fields: fields });
    } else {
      toInsert.push({ fields: fields });
    }
  });

  // 3. Execute Batched API Calls (Max 10 per request)
  while (toInsert.length > 0) {
    const batch = toInsert.splice(0, 10);
    airtablePOST(tableId, { records: batch });
    Utilities.sleep(250); // Rate limit backoff
  }
}