Lesson 30: Scripting Airtable

30.1 When a Spreadsheet Isn't Enough

The club's spreadsheet has done a lot. But you've seen its weak spot: the sheets refer to each other by typing the same value in two places. Shifts refer to members by email, and nothing stops someone from typing an email wrong, or from changing a member's email on one sheet and not the other. Every script that connects two sheets has to match them up itself, as HOURSFOR did in Lesson 16. Custom Functions in Sheets.

Airtable is a tool that looks like a spreadsheet but works more like a database. Its key feature is the linked record: instead of typing a member's email into a shift, you link the shift to the member's record itself. Airtable keeps the link even if the email changes, and from a member's record you can see all their shifts. It has a JavaScript scripting feature built in, and in this part of the book, you'll script it.

30.1.1 How Airtable organizes data

• A base is like a spreadsheet file: one project's data. The club gets one base.
• A table is like a sheet: Members, Shifts, Beds.
• A field is like a column, but with a fixed type: text, number, date, checkbox, single select (a choice from a list), email, and link to another record.
• A record is like a row: one member, one shift.
• Each table has one primary field, its first field, which Airtable uses as the record's name wherever the record appears, including in linked record fields.

30.2 Building the Club's Base

Create a free account at airtable.com, then create a new base from scratch, named College Community Garden.

1. Members. Rename the first table Members. The easiest way to fill it is to copy the data from the Members sheet in Lesson 10. College Community Garden: Case setup: College Community Garden: Case Setup (its copy button can give you the data to paste) and paste it into Airtable's grid; or save it as a CSV file and import it. Make Email the first field, the primary field, by dragging its column to the left. Emails are unique, and names aren't.
2. Shifts. Add a table named Shifts the same way, from the Shifts sheet, with the fields Date, Task, Member Email and Hours.
3. Link them. In Shifts, rename Member Email to Member, then change its field type to Link to another record, choosing the Members table. Because the column holds emails, and emails are the Members table's primary field, Airtable matches each shift to the right member automatically.
4. Field types. Make Hours a Number field (with one decimal place), Date a Date field and Task a Single select, with the five tasks as its options.

Now open any member's record: it shows their shifts, linked. That's the relationship the spreadsheet could only imitate.

30.3 The Scripting Extension

Airtable's Scripting extension runs JavaScript on your base. Open it from the base's toolbar: click Extensions, then Add an extension, and choose Scripting. A panel opens with a code editor and a Run button, and output appears in the panel.

Your learner profile needs a new environment line, the first change of this part; the updated profile is at the end of this lesson. Here's the first request, in a new chat with it:

Read. The assistant began by listing the new pieces, and they fit together in a familiar way:

• base.getTable("Shifts") gets a table, like getSheetByName.
• await shiftsTable.selectRecordsAsync() loads its records. It's asynchronous, like fetch in Lesson 21. Asynchronous JavaScript, so it's awaited. The result's records property is an array of records.
• record.getCellValue("Hours") reads one field of a record, by the field's name. An empty field gives null.
• A linked record field gives an array of objects, one for each linked record, each with an id and a name. A shift links to one member, so the code uses memberLinks[0].
• output.table(outputRows) shows an array of objects as a table in the panel. There's also output.text(...), for a line of text.

And one thing the assistant pointed out: an Airtable script isn't a function you choose from a menu. It runs from top to bottom, and await can be used at the top level, outside any function.

Look at the comment in the loop, though: memberLinks[0].name is described as "Ava". That's only true if the Members table's primary field is First Name, which is what the request implied, since it listed First Name first. The name of a linked record is always its primary field. So what happens with duplicate names?

That's the heart of this lesson. A name is a label; an ID identifies. Every Airtable record has an id, a unique string like rec8Hj2..., that never changes, even if every field in the record does. The fix keys the totals by ID, and keeps the name alongside, as an object, for showing.

The club's base avoids part of the problem by design: its primary field is Email, which is unique. But the lesson applies everywhere. Whenever you total, count or group records, group them by something unique, not by a name. You met the same idea in Lesson 10. College Community Garden: Case setup: College Community Garden: Case Setup, where sheets referred to beds by ID and members by email.

Here's the script for the club's base, in the book's style. Because the primary field is Email, the linked record's name is an email, so the script reads the Members table too, to show each member's full name:

const membersTable = base.getTable("Members")
const shiftsTable = base.getTable("Shifts")

// load only the fields the script uses, which is faster for big tables
const membersQuery = await membersTable.selectRecordsAsync({ fields: ["First Name", "Last Name"] })
const shiftsQuery = await shiftsTable.selectRecordsAsync({ fields: ["Member", "Hours"] })

// total hours by member record ID, because IDs are unique
const hoursById = {}
for (let i = 0; i < shiftsQuery.records.length; i++) {
  const shift = shiftsQuery.records[i]
  const memberLinks = shift.getCellValue("Member")
  const hours = shift.getCellValue("Hours")
  if (memberLinks !== null && hours !== null) {
    const memberId = memberLinks[0].id
    if (hoursById[memberId] === undefined) {
      hoursById[memberId] = 0
    }
    hoursById[memberId] += hours
  }
}

const rows = []
for (let i = 0; i < membersQuery.records.length; i++) {
  const member = membersQuery.records[i]
  let hours = hoursById[member.id]
  if (hours === undefined) {
    hours = 0
  }
  rows.push({
    "Member": `${member.getCellValue("First Name")} ${member.getCellValue("Last Name")}`,
    "Hours": hours
  })
}
output.table(rows)

This version passes { fields: [...] } to selectRecordsAsync, so Airtable loads only the fields the script needs. Airtable's documentation recommends it, and on a big table it's much faster, the same idea as loading only rowCount in Lesson 27. Building an Application in Excel. It also lists every member, including Jordan with 0 hours, because it goes through the Members table rather than only the members who appear in Shifts. The totals should match the Volunteer Hours you've seen all along: Maya 24, Ava 12, and so on.

30.4 Adding Records

Continuing the chat, a script to log a shift:

Read.

• input.textAsync("...") shows a text box in the panel and waits for an answer; input.buttonsAsync("...", [...]) shows a button for each choice. Both are awaited, because the script pauses for you.
• The member is found by looping through the Members records and comparing emails, and the script stops early, with a top-level return, if there's no match. That's allowed in an Airtable script, which is really the inside of a function Airtable runs for you.
• The date check splits the text on - and tests each part. It's careful work, though it would still accept February 31.
• createRecordAsync(fields) creates the record from an object of field names and values. The linked Member field takes an array of objects with an id: [{ id: memberId }].

The notes at the end are the part to check, because one of them is wrong. For a single select field, Airtable doesn't accept a plain string like "watering". It expects an object naming the option: { name: "watering" }. So in the club's base, where Task is a single select, createRecordAsync would stop with an error saying the Task field can't accept the value. The assistant said it would work "if it's a single select field with those exact option names," which sounds right and isn't.

This is where documentation earns its keep. Airtable's scripting documentation lists, for each field type, the shape of the value getCellValue returns and the shape createRecordAsync accepts, and they're not always the same: reading a single select gives { id, name, color }, and writing one takes { name }. When a write fails with a message about a field not accepting a value, look up that field type.

The fix is one line:

const newFields = {
  "Date": dateText,
  "Task": { name: task },
  "Member": [{ id: memberId }],
  "Hours": hours
}

One more thing to consider: the email comparison is exact, so Ava.Lopez@example.com wouldn't match ava.lopez@example.com. Emails aren't case-sensitive in practice, so a friendlier script would compare them in lowercase, with toLowerCase() on both sides.

30.5 Limits to Know

• Batches of 50. To create, update or delete many records, use createRecordsAsync, updateRecordsAsync or deleteRecordsAsync, which take up to 50 records at a time. A script that creates 200 records does it in four batches, in a loop.
• Automations. Airtable can also run scripts automatically, when a record is created or on a schedule, through Automations, with a "Run script" action. At the time of writing, running scripts in automations may require a paid plan; check the current terms. Scripts in automations have no input or output.table, because nobody is there to see them.
• The extension panel runs as you. Anyone who can edit the base can run a script in it, with their own permissions.

30.6 Your Learner Profile

The environment line changes, and your knowledge of Office.js and JADE comes along, in case you go back to Excel.

30.7 Summary

Airtable organizes data into bases, tables, typed fields and records, and a linked record field points to records in another table instead of copying their values. A table's primary field names its records everywhere they appear, so choosing a unique one, like Email, prevents confusion. In the Scripting extension, a script runs from top to bottom with await allowed anywhere: base.getTable gets a table, selectRecordsAsync loads its records, and getCellValue reads a field, with linked records arriving as arrays of { id, name }. input asks for answers, output shows results, and createRecordAsync adds a record, with each field type expecting its own shape of value, such as { name } for a single select. Group and total records by their IDs, which are unique, rather than by names, which aren't. Next, you'll script a very different kind of data: notes.