June 23, 2021

Working with Airtable’s REST API

Airtable is a very popular and easy-to-use “cloud database”. It is basically an online spreadsheet on the web that provides tons of integrations with other services. While most web developers prefer to use a real database as a backend of their web application your clients may appreciate it if you can also pull data from their AirTable’s spreadsheet.

In this article, we will show how to connect to Airtable’s REST API, retrieve data and even update, delete and create new records.

Airtable Account setup

I assume that you already created an Airtable account and uploaded some data there. Proceed to the Airtable Account page and create an API key.

Then proceed to the API page. It will offer you to choose a workspace and will take you to the API manual where you can see all your tables, sample requests, and even the sample results.

PHPRunner/ASPRunner.NET setup

Now is the time to set this connection up in your favorite web application builder.

REST API connection

URL of the workspace can be found on Airtable API page. API key can be found on the Account page. Make sure to prepend the API key with “Bearer ” as shown on the screenshot.

List

List page setup is very easy. Simply specify your table name (“base” name in Airtable’s lingo) in the Resource box and you can run the request right away. Make sure to add all the required fields to the list of fields by clicking the ‘+’ icon. Make sure to add the id column, Airtable creates it for you automatically and it is required for all view/edit/delete operations.

At this point, we would also suggest proceeding to ‘Pages’ screen in PHPRunner/ASPRunner.NET. Select id as a key column name and enable functions like View/Edit/Add/Delete.

Single

Single record configuration is also very simple, just change the resource to Customers/:{keys.id}. Just make sure to use your table name instead of “Customers”.

Delete

Also straightforward, use HTTP method DELETE and Customers as a Resource.

Insert

Unfortunately, insert and update operations will require some coding. Airtable can insert/update multiple records at once, which is nifty, but the format of the JSON request is custom and we have to format the data manually.

For the insert operation, set HTTP method to POST, click ‘Generate PHP (C#) code’ and switch to PHP mode. Make sure to replace field names like ContactName and CompanyName with your own field names.

PHP code

$method = "POST";
$url = "Customers";
//	replace variables in the URL
$url = RunnerContext::PrepareRest( $url );
//	prepare request body
$body = array();
$body[] = array( "name" => "Content-Type", "value" => "application/json", "location" => "header", "skipEmpty" => true);
$body = $dataSource->preparePayload( $body );
$jsonBody =  array ( "fields" => array( 
    "ContactName" => RunnerContext::PrepareRest( ":{ContactName}", false ), 
    "City" => RunnerContext::PrepareRest( ":{City}", false ), 
    "CompanyName" => RunnerContext::PrepareRest( ":{CompanyName}", false ) 
) );
//	do the API request
$response = $dataSource->getConnection()->requestJson( $url, $method, $jsonBody, $body["header"], $body["url"] );
global $restResultCache;
$restResultCache = array();
if( $response === false ) {
	//	something went wrong
	$dataSource->setError( $dataSource->getConnection()->lastError() );
	return false;
}
return true;

C# code

dynamic body = XVar.Array(), jsonBody = null, method = null, url = null, var_response = null;
method = new XVar("POST");
url = new XVar("Customers");
url = XVar.Clone(RunnerContext.PrepareRest((XVar)(url)));
body = XVar.Clone(XVar.Array());
body.InitAndSetArrayItem(new XVar("name", "Content-Type", "value", "application/json", "location", "header", "skipEmpty", true), null);
body = XVar.Clone(dataSource.preparePayload((XVar)(body)));
jsonBody = XVar.Clone(new XVar("fields", new XVar("ContactName", RunnerContext.PrepareRest(new XVar(":{ContactName}"), new XVar(false)), "City", RunnerContext.PrepareRest(new XVar(":{City}"), new XVar(false)), "CompanyName", RunnerContext.PrepareRest(new XVar(":{CompanyName}"), new XVar(false)))));
var_response = XVar.Clone(dataSource.getConnection().requestJson((XVar)(url), (XVar)(method), (XVar)(jsonBody), (XVar)(body["header"]), (XVar)(body["url"])));
GlobalVars.restResultCache = XVar.Clone(XVar.Array());
if(XVar.Equals(XVar.Pack(var_response), XVar.Pack(false)))
{
	dataSource.setError((XVar)(dataSource.getConnection().lastError()));
	return false;
}
return true;

Update operation

Basically, the same thing as an insert operation, but the HTTP method needs to be set to PATCH.

PHP code

$method = "PATCH";
$url = "Customers/:{id}";
//	replace variables in the URL
$url = RunnerContext::PrepareRest( $url );
//	prepare request body
$body = array();
$body[] = array( "name" => "Content-Type", "value" => "application/json", "location" => "header", "skipEmpty" => true);
$body = $dataSource->preparePayload( $body );
$jsonBody =  array ( "fields" => array( 
    "ContactName" => RunnerContext::PrepareRest( ":{ContactName}", false ), 
    "City" => RunnerContext::PrepareRest( ":{City}", false ), 
    "CompanyName" => RunnerContext::PrepareRest( ":{CompanyName}", false ) 
) );
//	do the API request
$response = $dataSource->getConnection()->requestJson( $url, $method, $jsonBody, $body["header"], $body["url"] );
global $restResultCache;
$restResultCache = array();
if( $response === false ) {
	//	something went wrong
	$dataSource->setError( $dataSource->getConnection()->lastError() );
	return false;
}
return true;

C# code

dynamic body = XVar.Array(), jsonBody = null, method = null, url = null, var_response = null;
method = new XVar("PATCH");
url = new XVar("Customers/:{id}");
url = XVar.Clone(RunnerContext.PrepareRest((XVar)(url)));
body = XVar.Clone(XVar.Array());
body.InitAndSetArrayItem(new XVar("name", "Content-Type", "value", "application/json", "location", "header", "skipEmpty", true), null);
body = XVar.Clone(dataSource.preparePayload((XVar)(body)));
jsonBody = XVar.Clone(new XVar("fields", new XVar("ContactName", RunnerContext.PrepareRest(new XVar(":{ContactName}"), new XVar(false)), "City", RunnerContext.PrepareRest(new XVar(":{City}"), new XVar(false)), "CompanyName", RunnerContext.PrepareRest(new XVar(":{CompanyName}"), new XVar(false)))));
var_response = XVar.Clone(dataSource.getConnection().requestJson((XVar)(url), (XVar)(method), (XVar)(jsonBody), (XVar)(body["header"]), (XVar)(body["url"])));
GlobalVars.restResultCache = XVar.Clone(XVar.Array());
if(XVar.Equals(XVar.Pack(var_response), XVar.Pack(false)))
{
	dataSource.setError((XVar)(dataSource.getConnection().lastError()));
	return false;
}
return true;

Working with Airtable’s REST API

Airtable Account setup

PHPRunner/ASPRunner.NET setup

REST API connection

List

Single

Delete

Insert

Update operation

2 thoughts on “Working with Airtable’s REST API”

Leave a Reply

Sign up for the free email newsletter for news about our
database publishing software, tips, and more.

Resources

Company

Airtable Account setup

PHPRunner/ASPRunner.NET setup

REST API connection

List

Single

Delete

Insert

Update operation

Related posts:

2 thoughts on “Working with Airtable’s REST API”

Leave a Reply