API Connector Documentation

Print

Import Airtable Data to Google Sheets

Last Updated OnMarch 19, 2024
byAna
< Knowledge Base

This guide will walk through how to pull data from the Airtable API directly into Google Sheets, using the API Connector add-on for Sheets.

We'll first get an API token from Airtable, and then set up a request to pull in Airtable API data to your spreadsheet.

Airtable recently rolled out personal access tokens. If you were previously connecting with an API key, it will continue to work for now, but they will be deprecated in the future. In addition, metadata calls to the /bases and /tables endpoints will work only with tokens, not keys.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get your Airtable API Access Token

  1. While logged into Airtable, click the Account icon in the top right corner.
    airtable-account
  2. Scroll down the page to the Personal access tokens section and click Try it now (or navigate directly to https://airtable.com/create/tokens).
  3. Click Create token
    airtable-createtoken
  4. Give your token a name and select the scopes and workspaces it should have access to. If you are primarily fetching data to Sheets, you'll likely want data.records:read and schema.bases:read. Click Create token
    airtable-tokenconfig
  5. A token will appear. That's it! Copy this and keep it safe, we'll need it shortly.
    airtable-token

Part 2: Pull Data from Airtable to Sheets

The easiest way to get started with the Airtable API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Airtable from the drop-down list of applications
    airtable-application
  3. Under Authorization, enter your API token with the word Bearer in front of it.
    airtable-authorization
  4. Get your table ID and base ID. You can get these through the /bases and /tables endpoints (as long as you added the schema.bases:read scope), or just check the URL bar while logged into Airtable.
    airtable-IDs
  5. Now open the /{base_id}/{table_id} endpoint and fill in the base ID and table ID values you just retrieved. For now you can skip the non-required parameters. If you like, you can use them later to customize your report.
  6. Set a destination sheet, name your request, and click Run to see data from your Airtable base in your sheet.
    airtable-response

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the API URLs shown in the API documentation. Here's an example request setup, just substitute in your own base ID where it says appI8siRzpZYFTq10.

  • Application: Custom
  • Method: GET
  • Request URL: https://api.airtable.com/v0/meta/bases/appI8siRzpZYFTq10/tables
  • OAuth: None
  • Headers:
    • Authorization: Bearer your_token
airtable-custom

By default, the Airtable API will return all the data in your base, which can result in many more columns than you need. To limit the data you return, add a parameter for each field you want with the syntax fields[]=field name, like this: https://api.airtable.com/v0/appI8siRzpZYFTq10/tblduEoPJ4cpKCw3R?fields[]=Name&fields[]=Description

Part 4: Handle Pagination

  1. Airtable limits the number of records returned in each request. By default, only 100 records will be returned.
    airtable-img8
  2. To access more than 100 records, loop through with the offset parameter as described. With API Connector you can do this automatically with cursor pagination handling, like this:
    • Pagination type: cursor
    • Next token parameter: offset
    • Next token path: offset
    • Run until: choose when the request should stop running
      airtable-pagination-cursor

Part 5: API Documentation

Official documentation: https://airtable.com/api

19 thoughts on “Import Airtable Data to Google Sheets”

  1. Is it possible to export from Google Sheets to AirTable?

    Use case:

    I would like to import inventory data from our inventory system into Google Sheets using a schedule, after which the data is migrated to AirTable.

    Ideally, I'd get the data from our API directly into our AirTable Base, however, I cannot seem to find a service which doesn't limit you on calls whilst being down to earth on price!

    Any suggestions will be much appreciated!

    PS
    There are 2000+ rows of data which will be brought in via the API and this will need to be scheduled to run ever 5mins.

    Reply

    • There are definitely ways to get data from Sheets to AirTable but unfortunately I'm not familiar enough with it to recommend anything beyond what I see in a Google search.
      I also see an issue with this plan, which is scheduling every 5 mins. Google limits add-ons to running scheduled calls once an hour, if you need it more frequently you would need to make a custom apps script as those aren't subject to the same limitations. Sorry for the inconvenience but I hope that helps prevent you from wasting time. It sounds like you'll probably be better off using an AirTable add-on (maybe this one: https://datafetcher.io/).

      Reply

  2. Hey Ana,

    Thank you so much for your swift response. Your dedication to the product is admirable :).

    Shame to hear of the limitation's with Google Sheets nonetheless I've decided to switch tac and use Azure Logics Apps to get the data into AirTable directly.

    Thank you once again, Ana :).

    Reply

  3. This is amazing! Thank you! I have a question about the variable name.

    The import is successful, however, the header cell shows 'records » fields » Number of Shares' instead of 'Number of Shares'. Do you by any chance know if there is a way to change this? or it is something with the Airtable API?

    Reply

    • Hi Elisa, the field names reflect the path to the field in the underlying JSON, so that means Number of Shares is nested within the records and fields arrays/objects.
      You can shorten this with the "truncate headers" option in Output Options.
      Update: you can also edit field names through the visual field editor.

      Reply

  4. Hi, it appears that some of my columns are importing as encrypted data. I noticed this is happening for fields that are the unique keys for other tables. Any assistance here?

    Reply

  5. Hi! Is there a way to make the pull come from a specific Airtable grid view vs the base?

    I'm finding that everytime a new column is added to a table / base it shifts column references

    If i can attach it to a grid view in Airtable, I can control column order etc

    Reply

    • I haven't seen anything like that in their API. But you can use API Connector's field editor to control the column order, that way it doesn't matter if the API sends back columns in a different order, does that work?

      Reply

      • Thanks Ana! Works - was trying to solve for sometimes I add new fields, but then through the field editor I have to reset and start again! Will keep trying things i guess!

      • We have some plans in the works to change the flow so you can add fields rather than resetting everything. Please keep an eye out!

      • Update: new fields will now be displayed to the right of existing fields, so you can select and add them to your report instead of starting from scratch.

  6. Does anyone know if it's possible to sort the API output by a certain fields (e.g., sort by Column 1 - Date) - currently feels like a random order that rows appear in!

    Reply

    • Sure, API Connector's Airtable integration provides a sort parameter for sorting fields. Or, if you're making a custom request, you'd manually add the sort[0][field] parameter like this: https://api.airtable.com/v0/app1111111/tb22222222222?sort[0][direction]=asc&sort[0][field]=orders.id

      Reply

  7. Hey! I'm finding that even when I'm pulling data from a table that isn't changing, on a weekly basis the columns are shifting position - what is causing this?

    I'm not adding new columns
    I'm not editing the underlying Airtable table
    Fields are fixed

    It's completely ruining all my anlaysis! Help please!

    Reply

    • That means the Airtable API is sending back data in a different order. To resolve, please use the field editor and make sure you’ve saved your field mapping, that will stop them from moving around.

      Reply

  8. I added a new field to my airtable after setting up my google sheet with the instructions here but I'm not seeing the new field appear when I go to edit fields in the API connector -- any ideas on why that's not working? Thanks!

    Reply

    • Can you please click Edit fields > Show raw response to see what the API is sending back? If your field is there, but not in the field editor, please contact support as that means something isn't working right and we need to fix it :p If the field is not there, please double check the field was added to your base and that you haven't filtered it out (e.g. by excluding it from your fields parameter). Also, by default the field editor will only show the first page of results, so the issue might be that your new field only gets returned for certain records, and none of those are on page 1. In that case please edit your request to return a page containing that field, save the mapping, and then run your original request again.

      Reply

Leave a Comment

Jump To