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/app0LWmcz9Bjd56KV/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"
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!
Hey Tim! Wondering if you figured this out, relevant to something I’m currently working on as well!
Thanks for the tip. Does this work the same way for Tableau?
Hi Miguel, I haven’t used Tableau in about 18 months but they used to require connectors for web data sources like this one (https://community.tableau.com/thread/179254). Basically, any ETL tool that can connect to a web source and accept JSON will work with the above method.
Hi !
Thanks you for the demo.
I tried to connect my airtable base to excel and power BI but it is not working…
I cant expand value to get my data. (your 8. ). For value, I have a drop down list and not an expand button…
Do you know what I did wrong ?
Thanks a lot,
Hi Nathan,
I tried to connect my airtable base to Excel and Power BI to generate more comprehensive dashboards.
At your step 8. , I do not get and expand button for value. I got a drop down list…
Do you know what I did wrong ?
Thanks a lot,
Hi Hugo, Any chance you can post a screenshot of what you’re seeing? Is this a list of checkbox items? If so, then check all of them and click Expand. You’re using PBI or Excel Get & Transform?
Hi Nathan,
Thanks for the tips. On step 8, airtable’s API documentation states server only return max of 100 records, if there are more than 100, the response will contain an offset (row 1 with records as list, row 2 with offset as some unique id).
what’s the best way to go around it?
Thanks heaps,
If you want to know a better way to do this, check out my how-to article.
It works for over 1000 records:
https://community.airtable.com/t/airtable-link-to-excel-via-power-query/18675
Thanks, Matthew. The pagination script works for me, too. Nicely done.
Hi Nathan,
Thanks for this post. I was able to connect to a AirTable using your post and solve the pagination issue using Matt’s comment. However the query does not refresh and shows the same count of rows as it did when originally configured.
I am linking the Milken Covid-19 airtable https://api.airtable.com/v0/appfSBJGSgkE7a6tF/COVID-19%20Tracker?api_key=YOUR_API_KEY
I am very new to all of this and any help would be greatly appreciated
Hey Nathan,
I was able to connect an Airtable to excel using your post and solve pagination using Matt’s reply. However, the data does not seem to be refreshing. I am linking the Covid-19 table https://api.airtable.com/v0/appfSBJGSgkE7a6tF/COVID-19%20Tracker?api_key=YOUR_API_KEY
Any help is greatly appreciated. I am a novice user.
Regards,
Bharat
Hello Nathan,
Hope you are well!
Would you be kind enough to update the Excel integration process because the above steps are no longer working for Excel?
Thank you,
Ashish
Agreed, if you could update Excel instructions that would be amazing.