Image for airtable data post

Get Data from the Airtable API the Easy Way

API, Data, Tech Tips Comments (14)

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
,

14 Responses 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!

  2. Miguel says:

    Thanks for the tip. Does this work the same way for Tableau?

  3. Hugo says:

    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,

  4. Hugo says:

    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?

  5. Kevin says:

    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,

  6. Matthew Billiodeaux says:

    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

  7. Bharat says:

    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

  8. Bharat Venugopal says:

    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

  9. Ashish says:

    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

Leave a Reply

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

« »