Data Extraction with Airtable

Airtable is another useful tool for managing data. With CustomJS, you can extract data from your Airtable databases and convert it to JSON or a CSV by using links. These actions can be initiated manually by the team members instead of a non-transparent and event-driven workflow. You can also use all the listed code examples as templates when creating a function in CustomJS.

Example 1: Airtable to JSON

With this function an entire Airtable table is returned as a JSON object by accessing a link. By default, linked tables are also resolved so that not only the referenced IDs as well as all properties of the linked tables are contained in the JSON. Caching is implemented to minimize the usage of Airtable's API calls. It is also possible to define how deep, in other words, how the table links are traced.

(RECURSION_DEPTH = 1) Table 1 -> Table 2
(RECURSION_DEPTH = 2) Table 2 -> Table 3

JS Code:

const Airtable = require("airtable");
const RECURSION_DEPTH = 1;
const recordsCache = {};

const base = new Airtable({ apiKey: variables["Airtable API"] }).base(
  variables["Airtable BaseId"]
);

const data = await base(variables["Airtable TableId"])
  .select({ view: variables["Airtable View"] })
  .all();

const promises = data.map((record) => {
  return processFields(
    record.fields,
    variables["Airtable TableId"],
    0,
    RECURSION_DEPTH
  );
});

return await Promise.all(promises);

/////////

async function findRecord(tableId, recordId) {
  if (recordsCache.hasOwnProperty(recordId)) {
    return recordsCache[recordId];
  }

  recordsCache[recordId] = await base(tableId).find(recordId);
  return recordsCache[recordId];
}

async function fetchNestedrecords(field, tableId, currentDepth, maxDepth) {
  if (currentDepth >= maxDepth) {
    return field;
  }

  if (typeof field !== "string" || !field.startsWith("rec")) {
    return field;
  }

  const record = await findRecord(tableId, field);
  return await processFields(
    record.fields,
    tableId,
    currentDepth + 1,
    maxDepth
  );
}

async function processFields(fields, tableId, currentDepth, maxDepth) {
  const fieldKeys = Object.keys(fields);

  async function processArrayField(field) {
    return fetchNestedrecords(field, tableId, currentDepth, maxDepth);
  }

  for (let key of fieldKeys) {
    if (Array.isArray(fields[key])) {
      fields[key] = await Promise.all(fields[key].map(processArrayField));
    }
  }
  
  return fields;
}

Example 2: Airtable to CSV

In this example, a flat table is exported. This means that linked / referenced tables are not resolved.

const Airtable = require("airtable");
const converter = require("json-2-csv");

const base = new Airtable({ apiKey: variables["Airtable API"] }).base(
  variables["Airtable BaseId"]
);

const data = await base(variables["Airtable TableId"])
  .select({ view: variables["Airtable View"] })
  .all();

const records = data.map((i) => i.fields);

return await converter.json2csv(records, {});