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.
- Sign in to Airtable
https://airtable.com/login
- If you haven’t already, go to your account settings and click “Generate API key”
- 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
- Scroll down to the Authentication section and note the base id
$ curl https://api.airtable.com/v0/[highlight]app0LWmcz9Bjd56KV[/highlight]/Donations \ –H “Authorization: Bearer YOUR_API_KEY”
- 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
- Open Excel (comes default in Excel 2016 but otherwise you’ll need to download the Power Query Addon)
- Click on Data -> New Query -> From Other Sources -> From Web
- Select Advanced
- Add the URL for the base to the URL field
- Select Authorization for the Header and use “Bearer yourapikey”
- Select OK
- Convert Into Table
- 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.
- Open Power BI Desktop
- Select Get Data -> Web
- 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"
Leave a Reply