Image for airtable data post

Get Data from the Airtable API the Easy Way

API, Data, Tech Tips Comments (1)

Airtable is part spreadsheet and part relational database management system. It makes a good, simple tool for analysts to organize basic data. While Airtable provides the option to export to CSV, let’s look at how to get the data in real time through the API.

  1. Sign in to Airtable
    https://airtable.com/login
  2. If you haven’t already, go to your account settings and click “Generate API key”generate API key image
  3. Go to the API hub for your bases
    https://airtable.com/api and select the base where you want to retrieve data.
    Note: use a base that already has some entries or add a few test entries
  4. Scroll down to the Authentication section and note the base id
    $ curl https://api.airtable.com/v0/app0LWmcz9Bjd56KV/Donations \ –H “Authorization: Bearer YOUR_API_KEY”
  5. Now we will use your API key and this base id to form our request

Examples

In the below examples we will get five records from a view called “Main View” and a base called “Donations”.

Python

import requests

headers = {
‘Authorization’: ‘Bearer yourapikey’,
}

params = (
(‘maxRecords’, ‘5’),
(‘view’, ‘Main View’),
)

r = requests.get(‘https://api.airtable.com/v0/app0LWmcz9Bjd56KV/Donations’, headers=headers, params=params)
from pprint import pprint
pprint(r.json())

Excel

  1. Open Excel (comes default in Excel 2016 but otherwise you’ll need to download the Power Query Addon)
  2. Click on Data -> New Query -> From Other Sources -> From Web
  3. Select Advanced
  4. Add the URL for the base to the URL field
  5. Select Authorization for the Header and use “Bearer yourapikey”
  6. Select OK
  7. Convert Into Table
  8. Click on arrows in column header to Expand to New Rows
    Note: repeat this step on any additional columns with arrows in the header

That’s it. Here’s a quick screencast of me doing this in a minute

Power BI

The steps in Power BI are almost identical to Excel.

  1. Open Power BI Desktop
  2. Select Get Data -> Web
  3. Follow the connect and expand steps in the Excel instructions above

R

# See https://github.com/bergant/airtabler for how to use this R package

library(pacman)
p_load(devtools)
devtools::install_github(“bergant/airtabler”) # if not yet installed
p_load(airtabler)
# This won’t work unless you have a one-line file .Renviron in your home directory. To check where your home is, type path.expand(“~”)
# You will get your API key and base id from your Airtable account
base_id <- “pp0LWmcz9Bjd56KV”
dat

curl

$ curl "https://api.airtable.com/v0/app0LWmcz9Bjd56KV/Donations?maxRecords=5&view=Main%20View" \
-H "Authorization: Bearer YOUR_API_KEY"

» API, Data, Tech Tips » Get Data from the Airtable...
On March 22, 2017
By
,

One Response to Get Data from the Airtable API the Easy Way

  1. Tim says:

    This is awesome, thank you so much for writing this up. Question though – have you figured out how to deal with the AirTable pagination limiting the query results to the first 100 records? I haven’t been able to find anything anywhere about how to deal with that in Power Query. Something about using an “OFFSET” but I have no idea how/where I would use OFFSET in the above. Also trying to understand if this means you would have one query for each set of 100 records you need to pull? I hope not — hope it would be one query still that cycles through with this offset until it has grabbed the last record. Any help would be much appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *

« »