You too can make dashboards: How to visualise your Airtable data in Google Data Studio

Originally posted on Optimi’s blog.

The world of data tools is quickly becoming more accessible. Which is great news for us! At Optimi, we love working with people who create positive impact in the world, and we would love to see more of these folks using data to take their impact to the next level.

So we’ve written a guide on how you can combine two free tools to centralise your data and draw valuable insights from it. Airtable is a very user-friendly platform for centralising and storing your data, and Google Data Studio adds massive data visualisation power.

This blog post is for you if:

  • You already use Airtable, but you’re frustrated with limitations of the built-in data visualisation features, or
  • You tinker with your data every now and then to track your impact and make decisions, but you would benefit from seeing important information more regularly, or
  • You understand the value of data dashboards but you’ve never been able to find a tool that makes it easy.

Here’s the genius part of it all. Airtable is a great tool for centralising your data and giving you one easy place to run your business from. When your data is all in one tool, you know it’s up to date and you can trust it. And now that you trust it, you can start visualising it for free!
You can build a dashboard that looks something like the picture below, and you can rest easy knowing it’ll stay up to date based on your Airtable data. And if you’re comfortable with the tools we’re using, you can connect Airtable to Google Data studio in under an hour.

Image for post
Image for post
Credit: Google Cloud

The recipe

  1. Sync your Airtable data to Google Sheets
  2. Pull in the data from Google Sheets to Google Data Studio
  3. Start visualising your data!

The key idea here is that we’re not connecting Airtable directly to Google Data Studio. We’re using Google Sheets to facilitate the connection. Google Sheets knows how to talk to Airtable, and it also knows how to talk to Google Data Studio. So it’s the perfect facilitator!

Step 1: Sync Airtable to Google Sheets (the easy way)

The easy option is to use a google sheets add on called “Coupler”. An awesome group of humans called Railsware have developed this handy add-on, and made it available to the world for free! Thanks, Railsware!

If you’d like to go down the “Coupler” path, see this page to get started. The only limitation of the free tier is that you have a maximum amount of data you can sync every month.

If you’re like us and you want more flexibility, then there’s another option. It’s not as easy, but on the plus side, you have full control and you don’t have to pay for it! Here’s what to do.

Step 1: Sync Airtable to Google Sheets (the flexible way)

Copy our Google Sheets template

Add the tables that you’d like to bring in from Airtable

Find the section of the spreadsheet that looks like the image below. Type in the table name of the table you’ve picked, and copy the Airtable view ID from the URL.

Image for post
Image for post
When you’re finished, it should look something like this.

Add in our open sourced code to your spreadsheet

Image for post
Image for post
Delete this text.

We’ve written some code that can sync data from Airtable to Google Sheets, and we’ve made it publicly available (open source). Go and view the code on github, copy it and paste it into your script editor window.

Disclaimer: we are not programmers by trade, so our code is not pretty. If you’re a dev, please suggest your improvements :)

Customise the code to fit you

Image for post
Image for post
Paste your Airtable API key in here.

Next, let’s find the Base ID for the Airtable base you’d like to use. Go to the Airtable API page, and click into the base that you want. Find the part of the page that looks like this image:

Image for post
Image for post
The base ID is the yellow block in this image.

You’ll see your baseID on the API page, it’s the text beginning with “app” and ending with “/”. Copy this, go back to the script editor and paste it in the appropriate place.

To complete your customisation, make sure the cell range in the script editor matches the yellow range in your spreadsheet.

Image for post
Image for post
Make sure the highlighted cell range here matches the yellow range in your spreadsheet.

Woohoo, you are now fully customised! Now let’s make sure everything works.

Test it out

Wait for your spreadsheet to finish loading. If everything has worked, then you will see a new menu option called “Airtable to google sheets sync”.

Image for post
Image for post

Click on the new menu option, and choose “Manually sync all data”.

With any luck, you should see all your tables coming through from Airtable! New sheets will automatically be created for each table. The bottom of your spreadsheet should look something like this:

Image for post
Image for post

Check each sheet to make sure it has the Airtable data that you’re looking for. Everything look ok? Whew! Success!

Set up an automatic refresh (if you want to)

Image for post
Image for post

You’ll be taken to another page — find the “Add trigger” button and click it. Then you can configure how often you want your spreadsheet pull through any changes from Airtable. This example shows a daily trigger:

Image for post
Image for post
Make sure you have chosen the “syncData” function for your trigger.

Hooray! You’re done with syncing Airtable to Google Sheets! Now the fun can really begin.

Step 2: Sync Google Sheets to Google Data Studio

You’ll now have to add each of the sheets (AKA data tables) you want, one at a time. Pick one of the sheets that you’d like to use as a data source. You should see something like this:

Image for post
Image for post

When you’re happy, click “Add to report”, and you’ll be taken to a blank report page. If you’d like to add more sheets, you can click Resource -> Manage added data sources -> Add a data source.

Step 3: Visualise your data!

You might be ready to jump in and start playing already. If not, these resources will help you get started:

Remember, not all data is equal. For example, the chart below would be far less useful than a chart measuring your impact against your three top sustainable development goals!

Image for post
Image for post

Here’s a better example of what your dashboard could look like:

Image for post
Image for post
Credit: Anna Jacobson

What next?

If you’ve read this article and you’ve got stuck following the steps, we can help! Get in touch at optimi.co.nz.

Written by Rupert from Optimi

Written by

Optimi configures and supports systems for the future of work. We empower organisations with systems made from simple technologies. http://www.optimi.co.nz/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store