Using Airtable with Drafts

Integration Guides
,
1

CleanShot 2023-02-28 at 08.45.39@2x
CleanShot 2023-02-28 at 08.45.39@2x1642Ă—512 52.7 KB

Drafts 37 or greater required for Airtable integration

Airtable is an online database service that allows you to create and maintain tabular data. Drafts integrates with Airtable through its API. The most common use of this integration is to create new rows in an Airtable from text captured in Drafts, for purposes like logging events.

Airtable Action Step

The Airtable action step provides an easy-to-configure step to create a new record in a table in your Airtable account.

This step requires configuration. To get setup, follow these steps:

  • Setup and configure a table you want to add to in your Airtable account. This action requires the table and fields already exist in Airtable.
  • Install the Send to Airtable example as a starter, or create a new action and add an “Airtable” action step.
  • Edit the action, tap on steps, then the “Airtable” step.
  • Assign a base ID. You can select from your Airtable bases using the “Select” button or paste in a base ID you got from your Airtable account (learn about Airtable IDs).
  • Assign a table ID or name. This can be the unique ID of a table or the exact (case-sensitive) name of a table in the base selected above.
  • Configure field names and values. These names and values are configured using Drafts Templates, so you can put static text values or dynamic values provided by tags. Field names should be the exact (case-sensitive) names of existing fields in the table. There is no need to include all fields of a table; fields not included will get default values. See the next section for more details on fields and values, and the section after for a concrete example of how you might configure a step. The step can send up to five fields and values for a record (if you need more, see “Scripting” section below).

Field Types and Values

As database tables, Airtable tables are configured with fields with different data types – like text, rich text, dates, selects, etc. When the Airtable action step sends data to Airtable, it uses the API’s typecast option, which tells Airtable to do the best it can to coerce values passed to fit the data type of the field.

When the action step is sending information to Airtable, it is sending text, so if you wish to send data to different field types, it’s important that your value is in a format that can be interpreted by Airtable. Some notes on common fields:

  • Single line text: Just text. No problem there. Typically you might do something like use the [[title]] tag to set a field to the first line of the draft.
  • Long text: Long text fields can also take just plain text. If the field is configured to support rich text in Airtable, it will convert Markdown input to rich text, making headers bigger, bolding **bold** markup, etc. (If the API is used to read these fields, it will also return a Markdown version).
  • Date: Airtable will successfully convert date strings in the ISO 8601 format to valid date field values. That format can be generated with Drafts’ tags using format strings. For example, to send the creation date of a draft to a date field in Airtable, use [[created|=iso8601]].
  • Single select: Will set the field to the text sent. If the value sent is not already one of the defined options for the field it will add that option in the field’s settings.
  • Multiple select: You can send a comma-separated list of values for this field. For example, if you want to send the tags you have assigned a draft, use the [[tags]] template tag, and those tags will appear as multiple selected values in this field.
  • Checkbox: If you want to create a record with a checkbox field already checked, send a “truthy” text value - like “true”, or “1”. Any other value or the absence of this field will result in an unchecked field.
  • Number: Best I can tell, Airtable will take any text passed to a number field, strip out any non-numeric characters, and try to make it a number. So, if you were to send a value like 1klk3, the number field would be set to 13, so be careful of your input.

A Concrete Example - Logging Weight

Let’s say you are tracking your weight and have decided you want to log those values to a table in Airtable each time you weigh yourself - but you want to save the hassle of opening the Airtable app, finding the right table, and making the entry. You’d rather just launch Drafts and type your weight…then, either send it to Airtable right away or later. This demo video walks through a simple setup like logging.

Scripting Airtable

For more advanced uses, Drafts provides access to the full Airtable API with the Airtable script object. This object has convenience functions for a couple of common requests, like creating records, but also provides the request function to make any API call desired. This opens up the ability to read, write, update records, perform queries, and more – while getting the convenience of Drafts taking care of the authentication and OAuth flow.

Details are beyond the scope of this article, but some sample actions below give you an idea of some of the possibilities.

Example Actions

Browser-Based Actions

Although more of an edge-case, it is also possible to use Airtable’s own JavaScript API library, Airtable.js in Drafts’ advanced HTML Previews. This method is likely only appealing if you have experience with this library and prefer its syntax for performing operations.

Integrating in this way connects directly to the API and does not use the credentials saved in Drafts for Airtable, so be sure to read the example action description for details on configuration.

Example Action

Conclusion

If you have other things you would like to accomplish with Airtable and Drafts, let us know.

2 Likes
2

Very cool! Great demo. I’d be grateful if someone could post a description (with screenshots or screen recording, ideally) of how they’ve used Airtable’s own JavaScript API library with Drafts.

3

How do you authenticate with AirTable when using AT action as part of a script?

I get an error:

} }, Response-Body={"error":{"type":"INVALID_PERMISSIONS_OR_MODEL_NOT_FOUND","message":"Invalid permissions, or the requested model was not found. Check that both your user and your token have the required permissions, and that the model names and/or ids are correct."}}, OAuthSwiftError.response.data={length = 251, bytes = 0x7b226572 726f7222 3a7b2274 79706522 ... 72656374 2e227d7d }, NSErrorFailingURLKey=https://api.airtable.com/v0/app663hkYysraYBTT/tblW3KbgbzUMCo04P, NSLocalizedDescription=}

Code:

// Client's Name

let nameMatch = draft.content.match(/Name: (Mr\.|Ms\.|Mrs\.|Doctor|Dr\.) (.*) (.*)/);

if (nameMatch)	{
	var firstName = nameMatch[2];
	var lastName = nameMatch[3];

// Clinic

let clinicMatch = draft.content.match(/Clinic: (.*)/);

if (clinicMatch)	{
	var clinic = clinicMatch;
	} 	else {
	alert("Missing Clinic");
	context.fail();
	}

// Assessment Type

let assessmentType = draft.content.match(/Assessment Type: (.*)/);

if (assessmentType)	{
	var assessment = assessmentType[1];
	} 	else {
	alert('Missing Assessment Type');
	context.fail();
	}
	
//Set date

var date = draft.getTemplateTag("date")
//alert(date)

// set values from your account
let baseId = "***********" //redacted
let tableId = "***********" //redacted

// create Airtable value
let at = new Airtable()
let record = {
	"fields": {
		"Date": date,
		"Clinic": clinic,
		"Service": assessment,
     "Name": lastName + ", " + firstName,
	},
	"typecast": true
}

let result = at.createRecords(baseId, tableId, record)
if (result) { // success! result contains record info
	console.log("Airtable record created")
}
else { // something went wrong, log error
	console.log(at.lastError)
	context.fail()
}

	
} 	else {
	alert('Missing Client Name'); context.fail();
	context.fail();
}
4

The constructor takes an optional identifier. I think that is what it uses to match up to your Airtable credentials in Drafts - I’ve not tried it, but I think that’s what the docs suggest.

If you omit it, ai th nk it will look for a default, but if you don’t have any credentials then I guess it might default to null.

5

Still can’t figure it out how to combine Drafts code with AT code. Where does the APIKey go in the drafts code?

Here is the code that AT provides as a sample

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'YOUR_SECRET_API_TOKEN'}).base('appABCDEFGHIGK');

base('Service Log').create({
  "Date": "2023-01-16",
  "Clinic": "Advanced Healthcare",
  "Service": "Prescreen",
  "Name": "Smith, John"
}, function(err, record) {
  if (err) {
    console.error(err);
    return;
  }
  console.log(record.getId());
});

Drafts Code:

// set values from your account
let baseId = "appOSVpC6cay6jFbX"
let tableName = "Debug"

// create Airtable value
let at = new Airtable()
let record = {
	"fields": {
		"F1": "Text for Field 1",
		"F2": "Text for Field 2",
		"Complete": true // checkbox field
	},
	"typecast": true
}

let result = at.createRecords(baseId, tableName, record)
if (result) { // success! result contains record info
	console.log("Airtable record created")
}
else { // something went wrong, log error
	console.log(at.lastError)
	context.fail()
}
6

If you are using Drafts’ Airtable object, you would not use an API Key. Drafts’ has it’s own API key, and manages OAuth authentication for you when making requests with it’s scripting interface.

This is totally separate from writing directly to Airtable’s API, which you could do with your own API key - making requests with the HTTP object.

It appears, however, you are trying to write with Airtable’s Javascript API, which is not in Drafts. It’s its own separate thing. You can write with that library by loading in a web preview, but not directly in Drafts’ scripting environment. This is discussed, along with examples, in the Airtable integration guide.

7

Then I go back to my original quesiton, how do I authonticate Airtable object?

“you would not use an API Key. Drafts’ has it’s own API key, and manages OAuth authentication for you when making requests with it’s scripting interface.”

Drafts does not seem to authenticate.

Here is the script:

// set values from your account
let baseId = "redactedForForum"
let tableName = "redactedForForum"

// create Airtable value
let at = new Airtable()
// sample values...need to match the target table
let record = {
    "fields": {
        "Date": "2023-06-07",
        "Clinic": "Advanced Healthcare",
        "Service": "CAT Assessment",
        "Name": "Smith, John"
    },
    "typecast": true
}

let result = at.createRecords(baseId, tableName, record)
if (result) { // success! result contains record info
    console.log("Airtable record created")
}
else { // something went wrong, log error
    console.log(at.lastError)
    context.fail()
}

Here is the error:

Error Domain=OAuthSwiftError Code=403 "" UserInfo={OAuthSwiftError.response.data={length = 251, bytes = 0x7b226572 726f7222 3a7b2274 79706522 ... 72656374 2e227d7d }, Response-Body={"error":{"type":"INVALID_PERMISSIONS_OR_MODEL_NOT_FOUND","message":"Invalid permissions, or the requested model was not found. Check that both your user and your token have the required permissions, and that the model names and/or ids are correct."}}, NSErrorFailingURLKey=https://api.airtable.com/v0/app663hkYysraYBTT/tblW3KbgbzUMCo04P, NSLocalizedDescription=, Response-Headers={
    "Access-Control-Allow-Origin" = "*";
    "Content-Length" = 251;
    "Content-Type" = "application/json; charset=utf-8";
    Date = "Wed, 07 Jun 2023 13:31:18 GMT";
    Etag = "W/\"fb-E5+5oHBiiIUbRT5O+8F5CtGpkBQ\"";
    Server = Tengine;
    "Set-Cookie" = "AWSALB=/kHwbsIf/YZPNh5zwE3/lXuOBDUQilZZ1t7ohNq05kLFo/mMKT9xTpkh+jX8DNi0KOhPpS9dYNzNUE1doovwICNdjskSMWC6MA45g+BqLok8Hz5wHcP0x21EU2OR; Expires=Wed, 14 Jun 2023 13:31:18 GMT; Path=/, AWSALBCORS=/kHwbsIf/YZPNh5zwE3/lXuOBDUQilZZ1t7ohNq05kLFo/mMKT9xTpkh+jX8DNi0KOhPpS9dYNzNUE1doovwICNdjskSMWC6MA45g+BqLok8Hz5wHcP0x21EU2OR; Expires=Wed, 14 Jun 2023 13:31:18 GMT; Path=/; SameSite=None; Secure, brw=brwCNbJnux8QBfxat; path=/; expires=Fri, 07 Jun 2024 13:31:18 GMT; domain=.airtable.com; samesite=none; secure";
    "Strict-Transport-Security" = "max-age=31536000; includeSubDomains; preload";
    Vary = "Accept-Encoding";
    "access-control-allow-headers" = "authorization,content-length,content-type,user-agent,x-airtable-application-id,x-airtable-user-agent,x-api-version,x-requested-with";
    "access-control-allow-methods" = "DELETE,GET,OPTIONS,PATCH,POST,PUT";
    "x-content-type-options" = nosniff;
    "x-frame-options" = DENY;
}, OAuthSwiftError.response=<NSHTTPURLResponse: 0x600003fca3a0> { URL: https://api.airtable.com/v0/app663hkYysraYBTT/tblW3KbgbzUMCo04P } { Status Code: 403, Headers {
    "Access-Control-Allow-Origin" =     (
        "*"
    );
    "Content-Length" =     (
        251
    );
    "Content-Type" =     (
        "application/json; charset=utf-8"
    );
    Date =     (
        "Wed, 07 Jun 2023 13:31:18 GMT"
    );
    Etag =     (
        "W/\"fb-E5+5oHBiiIUbRT5O+8F5CtGpkBQ\""
    );
    Server =     (
        Tengine
    );
    "Set-Cookie" =     (
        "AWSALB=/kHwbsIf/YZPNh5zwE3/lXuOBDUQilZZ1t7ohNq05kLFo/mMKT9xTpkh+jX8DNi0KOhPpS9dYNzNUE1doovwICNdjskSMWC6MA45g+BqLok8Hz5wHcP0x21EU2OR; Expires=Wed, 14 Jun 2023 13:31:18 GMT; Path=/",
        "AWSALBCORS=/kHwbsIf/YZPNh5zwE3/lXuOBDUQilZZ1t7ohNq05kLFo/mMKT9xTpkh+jX8DNi0KOhPpS9dYNzNUE1doovwICNdjskSMWC6MA45g+BqLok8Hz5wHcP0x21EU2OR; Expires=Wed, 14 Jun 2023 13:31:18 GMT; Path=/; SameSite=None; Secure",
        "brw=brwCNbJnux8QBfxat; path=/; expires=Fri, 07 Jun 2024 13:31:18 GMT; domain=.airtable.com; samesite=none; secure"
    );
    "Strict-Transport-Security" =     (
        "max-age=31536000; includeSubDomains; preload"
    );
    Vary =     (
        "Accept-Encoding"
    );
    "access-control-allow-headers" =     (
        "authorization,content-length,content-type,user-agent,x-airtable-application-id,x-airtable-user-agent,x-api-version,x-requested-with"
    );
    "access-control-allow-methods" =     (
        "DELETE,GET,OPTIONS,PATCH,POST,PUT"
    );
    "x-content-type-options" =     (
        nosniff
    );
    "x-frame-options" =     (
        DENY
    );
} }}
undefined
Script step completed.
8

I think I found the problem. When I ran AirTable action from Drafts for the first time, I gave persmission to a single Base rather than all of my bases. I deleted AirTable credentials in Drafts Options and authenticated again giving access to all bases now.

1 Like