Back to Blog

Airtable API – How to Use It For Integrations with Other Tools 

Airtable is a cloud solution that allows you to create your own workflows and virtually manage all kinds of activities. The service can be used by individuals, small businesses, and large enterprises. This low-code platform is extremely flexible and allows you to create workspaces from templates in a few easy steps.

In your Airtable account, you can create bases which consist of tables. Each table consists of fields. You can specify the type of data to be stored in each field. Filtering allows you to view and organize data in the preferred way. You can save frequently used configurations as ‘Views’. Apps and integrations add even more convenience to this platform.

Read on to learn how you can export data from Airtable bases by using the Airtable API.

Airtable REST API overview

In addition to the intuitive and smooth web interface, Airtable provides a REST API which can be used for setting up custom integrations or exporting data. The Airtable API lets you manipulate data in tables by creating, updating, or removing records.

Types of API by Airtable

There are two types of Airtable API: public API and Metadata API.

Metadata API is intended for use by developers. You need to request a client secret from Airtable to use this API.

Public API is available for anyone who has an Airtable account, although there are some limits. For example, you cannot perform more than 5 requests per second per base or manipulate views.

In this article, we will provide recommendations on using the public Airtable API.

What language is Airtable API

Like in any other REST API, you can send requests by using curl. In addition, the Airtable API documentation provides sample requests in JavaScript.

The official API client is the JavaScript one. But you can also use community-built solutions for Ruby, .NET, and Python. In this article, you will learn to create Python scripts for Airtable API integration by using the pyAirtable library.

Airtable API pricing

Unlike the Metadata API, the public API is available for all Airtable accounts by default. There are no extra fees for using the API. You are charged only for the selected Airtable plan. By the way, there is a free option for individuals or small teams, which can be used to explore Airtable features, including its API.

Airtable API limits

As it has already been mentioned, the public version of Airtable API has a limit of 5 requests per second per base. If you exceed the limit, you will have to wait 30 seconds until you are able to perform the next request. This limit is the same for all payment plans and cannot be increased.

Airtable API key

Before you start using Airtable API, you need to obtain the API key:

  • Open the Account Overview page:
1 open account
  • Click Generate API key:
2 generate API key
  1. Click the generated key to view it:
3 generated API key
  1. Copy the generated API key and paste it into the HTTP authorization bearer token header of your request.

Airtable API documentation

The Airtable API documentation is customized to exactly match the configuration of your Airtable account. All the information and sample requests are provided to fit the structure of tables in your Airtable bases. That is very helpful, as you can just copy-paste the sample requests, and they are guaranteed to work with your Airtable data.

To access the Airtable API documentation, click Help, and then select API documentation:

4 open API documentation

How to use Airtable API

Using the Airtable API consists in sending requests and getting responses. In order to write a proper request you need to know the type of the request, the endpoints, and the API key. Every endpoint includes the base id which is an alphanumeric sequence. You can find it in your Airtable API documentation or copy it from the URL of your base when using the web interface – it comes immediately after airtable.com.

Airtable GET API request formula

If you are going to build some integration by using the Airtable API, you may need to retrieve the data stored in your Airtable bases. There are two ways to do that:

  • List all records in a table
  • Retrieve a specific record from the table.

In both cases, you need to issue a GET request. To list all records in a table, send a GET request to the table endpoint:

curl "https://api.airtable.com/v0/[base_id]/[table_id]?[parameters]" \
  -H "Authorization: Bearer [api_key]"

where:

  • base_id is the id of the base
  • table_id is the name of the table
  • parameters are the parameters of the response
  • api_key is your API key

For example, the following request will retrieve the first three records from the ‘Design projects’ table:

curl "https://api.airtable.com/v0/appb55j2l9foJ6Y8Q/Design%20projects?maxRecords=3&view=All%20projects" \
  -H "Authorization: Bearer keyuYDpcUa4FmSW8z"

By the way, you can enable the show API key checkbox in your Airtable API documentation, if you want to include your API key in the sample requests.

To retrieve a specific record, issue a GET request to the record endpoint as follows (you need to know the record id):

curl https://api.airtable.com/v0/[base_id]/[table_id]/[record_id] \
  -H "Authorization: Bearer YOUR_API_KEY"

Can Airtable push data to the API?

If your integration is designed to be a bi-directional one, you need to be able to add or update data in Airtable bases via API. You can use the following methods to make changes to the data stored in Airtable:

  • POST
  • PUT
  • PATCH
  • DELETE

For example, the following POST request will add a record to the ‘Design projects’ table:

curl -v -X POST \ https://api.airtable.com/v0/appb55j2l9foJ6Y8Q/Design%20projects \
  -H "Authorization: Bearer keyuYDpcUa4FmSW8z" \
  -H "Content-Type: application/json" \
  --data '{
  "records": [
    {
      "fields": {
        "Name": "Coffee packaging",
        "Client": [
          "recnXZRUu8QwT16uB"
        ],
        "Category": "Brand identity",
        "Due date": "2020-10-30",
        "Kickoff date": "2020-10-16",
        "Tasks": [
          "recFHk623ZfkFQ0BR"
        ]
      }
    }
  ]
}'

The response will contain the same record object, as a confirmation, with additional data: the id of the newly created record and the creation time.

Be mindful of the difference between PUT and PATCH requests. The PATCH request only updates the specified fields, leaving the rest as they are. The PUT request will delete the information from the fields that were not mentioned in the request. So, using PATCH is a safer way to apply changes to your Airtable data.

Please refer to your Airtable API documentation for more details and sample requests.

Can I use Airtable API to create a table?

Airtable API doesn’t support creating tables. You have to use the web interface to create tables and to add or remove fields.

How to create multiple records by using the Airtable API

As you may have noticed, the PUT request for adding records to a table actually adds a list of records. That means that you can add several records in one request. Here is an example, where two records are added to the ‘Design projects’ table:

curl -v -X POST \ https://api.airtable.com/v0/appb55j2l9foJ6Y8Q/Design%20projects \
  -H "Authorization: Bearer keyuYDpcUa4FmSW8z" \
  -H "Content-Type: application/json" \
  --data '{
  "records": [
    {
      "fields": {
        "Name": "Coffee packaging",
        "Client": [
          "recnXZRUu8QwT16uB"
        ],
        "Category": "Brand identity",
        "Due date": "2020-10-30",
        "Kickoff date": "2020-10-16",
        "Tasks": [
          "recFHk623ZfkFQ0BR"
        ]
      }
    },
    {
      "fields": {
        "Name": "EngineerU brand identity",
        "Client": [
          "recIg3w07k4hLel2f"
        ],
        "Category": "Brand identity",
        "Complete": true,
        "Due date": "2020-10-20",
        "Kickoff date": "2020-10-06"
      }
    }
  ]
}'

Airtable API examples

The following request example shows how you can update only one field (‘Name’) of a record by using the PATCH method:

curl -v -X PATCH https://api.airtable.com/v0/appb55j2l9foJ6Y8Q/Design%20projects \
  -H "Authorization: Bearer keyuYDpcUa4FmSW8z" \
  -H "Content-Type: application/json" \
  --data '{
  "records": [
    {
      "id": "recbkvgtdhzG3r4jw",
      "fields": {
        "Name": "Coffee & tea packaging",
      }
    }
  ]
}'

If you want to delete records in a table by using the Airtable API, you need to provide a URL-encoded array of up to 10 records in your request:

curl -v -X DELETE https://api.airtable.com/v0/appb55j2l9foJ6Y8Q/Design%20projects \
  -H "Authorization: Bearer keyuYDpcUa4FmSW8z" \
  -G \
  --data-urlencode 'records[]=recbkvgtdhzG3r4jw' \
  --data-urlencode 'records[]=recv1JKvHSO8sIdl4'

The response to such a request will contain the ids of the deleted records with ‘deleted’ attribute set to ‘true’:

{
    "records": [
        {
            "id": "recbkvgtdhzG3r4jw",
            "deleted": true
        },
        {
            "id": "recv1JKvHSO8sIdl4",
            "deleted": true
        }
    ]
}

Using Airtable API for integration

Python client for Airtable API

The Python client for the Airtable API is a community-supported library—pyAirtable. You can install it by using pip. If you have Python installed in your system, you most likely have pip, too. Run the following command to install pyairtable:

$ pip install pyairtable

Now you can import the library into your Python scripts. It contains the classes with methods developed for communication with the Airtable API. For details, refer to the pyAirtable documentation.

Getting Airtable data by using pyAirtable

Let’s create a simple Python script that will retrieve data from an Airtable table.

  1. First of all, we need to import the Table class from pyAirtable library to make it available in our script:
from pyairtable import Table
  1. Now, create an instance of the Table class.

We need to provide the API key, the base id, and the table name as attributes:

table = Table('keyuYDpcUa4FmSW8z', 'appb55j2l9foJ6Y8Q', 'Design projects')
  1. Use the all() method to fetch all records from the table:
result=table.all()
  1. We know that the response is a list of records.

Let’s print the first three records:

print (result[:3])

Don’t forget to use your own API key, base id, and table name. Here is what was printed for our sample data:

[{'id': 'rec6DYViFUDqlUkWQ', 'fields': {'Client': ['recO3jNA0JfRvqas0'], 'Due date': '2020-11-01', 'Category': 'Technology design', 'Name': 'Lemon headband', 'Tasks': ['recMjGAzpygMAbfjz', 'recV6f8lKa966r1T0'], 'Kickoff date': '2020-10-18'}, 'createdTime': '2020-06-11T01:30:22.000Z'}, {'id': 'recA68yoBYeCCAO3U', 'fields': {'Client': ['recEq3PydavL7xADv'], 'Due date': '2020-09-29', 'Complete': True, 'Category': 'Brand identity', 'Name': 'Gotham City Parks brand identity', 'Kickoff date': '2020-09-15'}, 'createdTime': '2020-06-11T01:26:15.000Z'}, {'id': 'recHHTxxS7ALI3UR3', 'fields': {'Client': ['recEa9imN0pUrHels'], 'Due date': '2020-10-04', 'Complete': True, 'Category': 'Technology design', 'Name': 'Convertible 3000 laptop', 'Kickoff date': '2020-09-20'}, 'createdTime': '2020-06-11T01:55:41.000Z'}]

You can manipulate the data in any way. For example, get the list of ids for the first three table records:

>>>for i in result[:3]: print (i['id'])
rec6DYViFUDqlUkWQ
recA68yoBYeCCAO3U
recHHTxxS7ALI3UR3

Python script to pull API data into Airtable cell

The pyAirtable library allows you to create, update, and delete records, as well. Let’s create a record with only one field—’Name’:

>>>table.create ({'Name': 'Sample Project'})

If you go to the web interface, you will see a new record with only one field:

5 created new record

Now let’s update the newly created record with several fields by using the update() method. This method requires the record id to be passed as the first parameter. You can obtain the record id as follows:

records = table.all()
for record in records:
   if record['fields']['Name']=='Sample Project': id = record['id']

To update the record with category, due date, and mark it as completed:

table.update(id, {'Category': 'Healthcare design', 'Due date': '2022-01-31', 'Complete': True})

And here is the result in web interface:

6 updated record

Some fields, like ‘Category’, ‘Client’ and others, can be updated with values from a predefined list only. Otherwise, you will get a 422 Client Error. The allowed field values can be changed in the web interface. 

To delete the newly created record, use:

table.delete(id)

Exporting Airtable data to spreadsheets

As we have learned how to export data from Airtable by using its API, we can think about pushing that data further to other applications, for example, Excel spreadsheets. There are plenty of libraries for working with Excel files in Python. But Excel can also open CSV tables, which can be easily generated in Python without additional modules.

Let’s write a script that will export data from Airtable, and then save it to a CSV table.

  1. First of all, let’s import the standard csv module along with the Table class from pyAirtable:
from pyairtable import Table
import csv
  1. Then we will obtain the data from Airtable just as we did in the previous sections:
table = Table('keyuYDpcUa4FmSW8z', 'appb55j2l9foJ6Y8Q', 'Design projects')
data = table.all()
  1. Now let’s create a list of all the fields in the table. We will use it to create the header of the csv table. Otherwise, errors may occur in case some records contain empty cells.
fields = ['Name', 'Category', 'Complete', 'Client', 'Kickoff date', 'Due date', 'Tasks']
  1. Finally, we will use the DictWriter method to create a csv file:
with open('export.csv', 'w') as csvfile:
   writer = csv.DictWriter(csvfile, fieldnames = fields)
   writer.writeheader()
   for record in data:
      writer.writerow(record['fields'])

Now you can open the resulting CSV file in Excel and see the exported data from Airtable:

7 exported table

You can see that every second row is empty and you will have to remove them if you need to work with this data in Excel further. The values in the ‘Client’ and ‘Tasks’ columns are actually record ids. You need to modify the script to get the real values.

Exporting Airtable data to spreadsheets without coding

If such a result is not acceptable for you, we recommend that you have a look at turnkey solutions. They can be set up and configured in a matter of minutes. For example, Coupler.io does not require knowledge of APIs and programming languages, and anyone can use it for Airtable export to Excel, Google Sheets, or BigQuery. Another big advantage is the ability to sync data on a schedule.

airtable to excel

To do the job, you’ll need to complete these three steps:

  • Configure Airtable as a source – provide a shared view link for your Airtable view.
  • Configure the desired destination – Excel, Google Sheets, or BigQuery
  • Configure the frequency for the desired automated data exports

In addition to Airtable, Coupler.io provides a number of other sources, including a customizable connector to REST APIs. If you’re interested, try out Coupler.io for free!

Conclusion: Airtable API vs. other integration tools

In this article, we have discovered the methods of Airtable API and learned how to implement them in Python scripts. That may be useful if you are going to create your own synchronization solution, customized to your specific needs. But at the same time, if you are looking for a quick and easy way to move your Airtable data somewhere else, consider using ready-made integrations such as Coupler.io. That will save you hours, and you can always count on documentation and support.

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free