Airtable API Part 2: Get data from your Airtable account

This is part two of our tutorial series on working with the Airtable API in dashdash. To follow along, make sure you’ve completed the previous tutorials.

Overview

Now that we have all the information in our dashdash Setup view, we can go ahead and get all the entries we have in our Airtable Content Planner view.

In this tutorial , we’ll walk through how to:

  • get data from your Airtable account
  • format the JSON response
  • parse to retrieve the data we want
  • insert the parsed data into a table using UPDATE_DATA

But first, let’s set up our GetAirtableData view by creating the following header rows:

Cell Content
A1 Get
B1 Substitute
C1 Parse
D1 Update

It should look a little like this:

GetAirTableData-view

Right! Now let’s get some data!

Getting data from your Airtable account

To get data from our API account, all we need to do is use the following GET() function.

GetAirtableData-get-function

Add the following content to our GetAirtableData view:

Cell Content
A2 =GET('Setup'!D5, 'Setup'E2)

Now if you click the Data Explorer Data Icon icon in cell A2, you should see this in the data explorer side panel:

Brilliant! But we need to format this data a little in order to make our lives easier later on when trying to update an entry.

Formatting the JSON response

As we saw in the screenshot, each record has an id property and a fields object (there’s also the createdTime property, however, this is something that’s created when we make the API call, and not in our Airtable account). We want to have the id and all the content in the fields object in a single table. To do this, we need to format the JSON using SUBSTITUTE() so that each record in records only has properties and values.

Add the following content to our GetAirtableData view:

Cell Content
B2 =SUBSTITUTE(SUBSTITUTE(A2,'"fields":{',""),'},"createdTime"',',"createdTime"')

GetAirtableData-substitute-function

Great! Now if we have a look at the new data, we’ll see that each record only has properties and values.

Next up - let’s parse this formatted JSON data so that it’s ready to go into a table.

Parsing the data

We only want to retrieve every individual record in the records object. So, we just need to parse the data in cell B2 for records, and it will automatically retrieve each record individually.

Update your GetAirtableData view with the following:

Cell Content
B2 =PARSE(B2,"['records']")

Now when we look in the data explorer, we see that the records object has been removed - we only get each individual record.

All that we need to do now is get all this data into a table.

Inserting parsed data into a table

To create our table with all the entries, we’ll use the UPDATE_DATA function. We’ll need a unique key in order to know which entries need to be added to our table, and which ones just need to have fields updated. Luckily, each entry has an id field that is perfect for this!

Update your GetAirtableData view with the following:

Cell Content
D2 =UPDATE_DATA(C2,"id",A5:5))

And now, your GetAirtableData view should look something like this:

Add some styling to the headers as you like, and you’re finished!

Summary

Well done! We’ve just retrieved all the entries from the Content production view of our Content calendar base, parsed the response to get the data we want, and put all that information in a table. Awesome!

Well, seeing as we know how to get data from Airtable, let’s see how to create entries in our Airtable account.

Up next: Part 3 - Create a new entry in Airtable

1 Like