Airtable API for Drafts

2018-08-08    Permalink

I experimented last year with using Airtable as my markbook1. For some reason I just find entering data into spreadsheets so incredibly dull, and the results so incredibly uninspiring, but at the same time I fully recognise that recording student data is a valuable thing to do, both for my teaching itself, and for more mundane things like writing reports. When I came across Airtable, it struck me as much more appealing, both visually and functionally. It’s a cross between a spreadsheet and a database, and it supports a large variety of different kinds of data such as files, images, and more. Its use of coloured labels and image thumbnails also makes it a much more visual experience. It also comes with a native app for iOS with native controls like switches and buttons, which makes for a really nice experience.

But as nice as the app is, both on iPhone and iPad, Airtable is primarily a web application, and the iOS app lacks a lot of the features that the web interface has. On top of that, the web interface suffers from the curse of mobile Safari. Even if you hold down on the refresh button and hit “Request Desktop Site”, basic things like scrolling just don’t work.

There are a few features I would like to see coming to the iOS app, but for the most part, it’s just basic things that involve too many taps that I would like to see improve. For example, I have a table in my Airtable base that represents pieces of homework my students have done. When I set a new piece of homework, I want to create a whole bunch of rows with the same topic, but each associated with a different student. On the web app, you can do that quite easily by copying and pasting cells, but on iOS you need to create each new row one-by-one.

What I wanted was to be able to use the Airtable app as a visually appealing front end, but to have more power on the back end of the database to add and manipulate data from iOS. But instead of building the tools to manage my markbook, I decided instead to build the tools to build the tools. Why go to such effort? Isn’t this just adding additional layers of procrastination?2 you might quite reasonably ask – to which I would probably avoid the question and attempt to change the subject. However! I did have a few reasons for wanting to do this.

Firstly, I have been working on my programming skills3, and writing a programme to interact with a database on a server is an incredibly common programming task. It brings with it common challenges such as syncing data between the local client and the remote server, formulating requests to send to the server, and parsing data from its responses into a usable format. Secondly, I wanted to create something that others could make some use of. The tools I will make for my own purposes will be far too specific for that, but building a general-purpose system has more potential use cases for a larger number of people. Thirdly, it would make my own tools easier to maintain and modify as need be, since the meta-tools from which they are built abstract away much of the complexity.4

If you read my blog regularly, you will already know about the wonderful Drafts 55, and you will also know that it offers powerful scripting via JavaScript. Drafts is the perfect app for entering and processing data, and JavaScript is probably the language that I am most familiar with at the moment, so that’s where I decided to build it. A little while after I had begun, I saw a post from Greg Pierce, the developer of Drafts, asking for input on what services people most wanted integration with in the app. Drafts already supports a large number of services, basically on two possible levels. One is as Action Steps, which are little Workflow-style drag and drop blocks, with native UI switches and buttons and text fields. These have the advantage of being easy to use, and they are great for building simple actions, but they lack flexibility, as well as some of the basic things required for programming such as conditional statements, loops, and variables. The other way that apps and services can be integrated is via scripting, and here the list of integrations is much more extensive. Being part of a fully-featured JavaScript environment means that these integrations can be part of complex programmes.

There are basically two ways that Drafts can integrate with an app or service. One is via a URL scheme, where data is sent locally on the device from one app to another. The other is via a web API, where the Drafts app sends a message to a server on the web, which then responds in some way. This is the way that Airtable works. That server might also send that information back down to its own app on the same device, or on another device. Both of these abilities – to build URL schemes, and to send API requests – are both made possible within the Drafts JavaScript environment. What struck me about Greg’s post was how many different services people were asking for in the replies, and how many of them were perfectly possible but just waiting to be built. It occurred to me that instead of asking Greg to build them all himself, the number of integrations available might increase more quickly if users created them themselves.

User-built integrations are already perfectly possible to create, and in fact a few people have created them already. Special mention goes to Oliver Guerriat, who created two really useful – though not very well documented – ones which I don’t think enough people know about. One allows easy interaction with the Bear URL scheme, and the other adds easy interaction with many of Drafts own features6. I’d like to see more of these in the future, with documentation as good as that in Greg’s Scripting Reference. I wonder if Greg would even consider linking to some of these user-build integrations in the Drafts Scripting Reference as optional add-ons?

I wanted to try to make something like this myself, and my Airtable API is the result. Using the ideas about Object Oriented Programming that I’ve been learning about recently, I’ve created a set of classes and methods for interacting with a base in Airtable. This is very much beta software, so I am sure there are bugs, and I am sure that people will suggest additional features that would improve it. One thing I would like to add in the near future is file uploads and downloads.

Here’s a link to the script in the Action Directory, and here’s a link to the script in GitHub. To use this, include an “Include Action” action step before your own script. You will also need to find out your base’s endpoint and API key from the Airtable API documentation.

Airtable API documentation

If you have any suggestions for changes or improvements, or if you find a bug, please let me know on Twitter or by Email. If you know your way around JavaScript and you want to make some changes, feel free to issue a pull request on GitHub. Below is the full documentation I’ve created. I’ve aimed to stick as closely to Greg’s style as possible.

I’m looking forward to trying this for a few other apps or services. I think next on the list might be Working Copy, an app that I absolutely love and which has an amazingly extensive URL scheme.

If you enjoyed this post, please check out my new Amazon recommendations page.

Airtable

Airtable is a web-based spreadsheet and database tool which can be used to organise a large variety of different kinds of data including text, images, files, and more. The scripting interfaces below are convenience wrappers that allow easy interaction with Airtable’s REST API.

While the Airtable API offers extensive read and write access to the data stored, it does not provide metadata about the structure of databases or the types of fields. Users will need to know this information in advance to properly interact with the database.

ATRecord

Represents a single record in an Airtable base.

Class Functions

  • create() -> ATRecord
    • Create a new record object.
  • selectRecords(Array of ATRecord objects, field, options) -> Array of ATRecord objects
    • Present a list of records to the user for them to select one or more
    • Parameters
      • Array of ATRecord objects: all records must have been added to a table and the table updated.
      • field [string or function] : A string denoting the name of the field which should be used to represent the records in the selection list. Alternatively, pass a function which takes each record and returns a string to display. This can be used to combine multiple fields together to create the labels for the selection list.
      • options [object]: a dictionary of options with the following available keys.
        • title [string] (optional): Title to display in the prompt.
        • message [string] (optional): Message to display in the prompt.
        • type [string] (optional): Valid values are “selectMultiple”, “selectOne”, and “selectButtons”.
        • filter [function] (optional): A function to filter the records displayed.

Properties

  • id [string, readonly]
    • The unique id of the record in the Airtable base. Undefined until the record is added to a table and the table is updated.
  • table [ATTable, readonly]
    • The table to which the record belongs.
  • createdTime [date, readonly]
    • The time that the record was created. Undefined until the record is added to a table and the table is updated.

Functions

  • getFieldValue(field) -> object
    • Takes a string with the name of the field, and returns the contents of that field.
  • setFieldValue(field, object)
    • Takes a string with the name of the field, and sets the contents of the field according to the object passed.
  • getLinkedRecords(field) -> Array of ATRecord objects
    • For a field which links to records in another table, this returns all of the linked records. The table containing the linked records must have been added to the base.
  • linkRecord(field, ATRecord)
    • For a field which links to records in another table, this adds a new linked record from the given field. Existing linked records are unaffected. Note that Airtable also supports linked fields which do not allow more than one linked record.
  • update() -> boolean
    • Pushes changes to the base for a record that has already been added to a table. Returns true if successful.

ATTable

Represents a table within an Airtable base.

Class Functions

  • create(name, ATBase) -> ATBase
    • Create a new table object with a given name and associated with a given base. Name must coincide exactly with an existing table on the web.

Properties

  • name [string, readonly]
  • base [ATBase]
  • records [Array of ATRecord objects]
    • All of the records associated with the table.
  • fields [Array of strings]
    • The names of the fields associated with records in the table.

Functions

  • addRecord(ATRecord)
    • Add a new record to the table. Will not be pushed to the web until update() is called.
  • selectRecords(field, options)
    • Equivalent to ATRecord.selectRecords( table.records, field, options).
  • update() -> boolean
    • Push changes to the base. Returns true if successful.

ATBase

Represents an individual Airtable base.

Class Functions

  • create(name) -> ATBase
    • Create new base object with given name.

Properties

  • name [string]
  • tables [Array of ATTable objects]
    • All of the tables associated with the base.

Functions

  • getRecordWithID(id) -> ATRecord
    • Takes the unique id of a record within an associated table and returns the record object.

  1. “gradebook” for you Americans out there ↩

  2. Spoiler: it’s layers of procrastination all the way down. ↩

  3. I’ve just started reading Code Complete by Steve McConnell, and I’ve already learnt a lot of good programming lessons. ↩

  4. Abstracting complexity is essentially what programming is. ↩

  5. See here for my previous posts about Drafts. ↩

  6. Mind-bendingly, it seems to make the creation of actions itself scriptable! ↩

Airtable, Drafts, JavaScript