Airtable Report Dashboard
Using Google Apps Script, Google Big Query, and Google Data Studio
We are working to bring a few prior blog post to Achieve Data Solutions. We will not be including screen shots - though they may be added later. The video explains everything and the source code is available via the link provided below.
OVERVIEW: This blog covers the technologies I used to create a client's Airtable Reporting Dashboard using Google Sheets/Google Apps Scripts, Big Query, and Google Data Studio. It contains links to the javascript and sql I used plus a video that demonstrates the entire process.
LINKS
The Google Sheets/Apps Javascript
& BigQuery SQL Example
kdkdkd
FROM AIRTABLE TO BIG DATA STUDIO
Airtable is amazing and amazingly frustrating. Amazing because you can have a multi-user relational database in operation in a couple hours.
Frustrating because it lacks the ability to create true forms or workflow. It has lousy data validation controls (although last year’s introduction of javascript is helpful) and meaningful reporting is non-existent.
But my client, an international PR and Marketing Company that specializes in influencer and media marketing campaigns uses it to run a robust business. And they need to be able to report on that data.
The goal was a dashboard with reasonably fresh data. By reasonably fresh, data that is refreshed every 3 hours or so is good enough.
I was going to write about the reporting system’s genesis, a prior reporting system using some of the same technologies outlined below, but I am backlogged with some projects so I’m just going to explain what I created for them.
Overview: Data Flow
Here are the basics of how the data flows in the reporting system.
AirTable
Users enter data into a multi-table/multi-sheet Airtable database.
|
Google Sheets / Google App Scripts
A timed script pulls data from Airtable into a series of Google Sheets.
|
Google Big Query
Big Query Views, also on a timer, create/refresh native Big Query data tables.
The queries do data transformation allowing more robust and flexible reporting.
|
Google Data Studio
A dashboard with a series of graphs displaying Big Query data. It is sent weekly to company stakeholders. They can also visit the reports online to filter data and export to Spreadsheets if desired.
Step 1: Airtable to Google Sheets with Google Apps Scripts
The first order of business is getting data from Airtable. I can’t share my client’s data, of course, so I have created a data table in another Airtable Pretty basic but it contains similar characteristics to my client’s data.
I call this base “Sales”. Here are the fields I need:
- Sales_Record_ID:
A formula field showing the record number. (Airtable gives me the Record ID in the json but I define it as a field so I can easily see it in Airtable when needed. - Sales ID Num:
This is an autonumber field that I add for internal tracking - Staff:
The staff person who made the sale - Sales Level:
Sort of like a sales category. You will see later, we wanted to report specific sales level counts in a particular order on the report. We can do it with a pivot table but a query to transform that data is faster and more precise for our reporting - Cash or Credit:
Similar to sales level, I want to report how many sales were of a particular payment type - Source:
And we also want a count of sales by source - online, walk-in, etc - Sales Date:
The date of the sale.
Disclaimer: Remember, this data may not make a LOT of sense as I altered my client’s database for the sake of this blog entry.
The Script:
This script is a modified version of a script that has been posted in various forms on the Airtable forums. I needed it to be a bit more flexible and modular. It only to pull specific fields and paste those fields, in order, into Google Sheets. The original method of putting data into Google sheets was VERY slow.
Also, it has been modified for a flat file - an Airtble view that displays unique rows for each data element.
You will need your Airtable API key, the base ID, the name of your table, and your desired field names. You can also retrieve from a specific view but for the sake of this blog, I will do any filtering I need by passing a filter to Airtable.
To get your api key and base id, while viewing your table, go to the Airtable help and at the bottom, select “API Documentation”. In the upper right of the resulting screen, check, “Show API Key”. Scroll down and you will find the API key and base ID.
Steps to Pull Data Into Google Sheets / Google App Scripts
- Create a Google Worksheet
- Name the first sheet “AT Data Control”
- Add the following column names in A1, B1, and C1
A1: Sheet/Data, B1: Start, C1: Finish
This just time stamps when the script starts and ends. And really isn’t necessary for anything. - Select "Tools->Script Editor"
- Paste the script into the editor and fill in the necessary data:
The API Key, the base ID, the field list, a filter (if needed), the name of the sheet the process will create, and the Airtable table name.
Save and run the script. The first time, you will need to authenticate the script.
If everything is entered correctly - case sensitive - you should have a Google Sheet with your data pretty quickly.
Once you have that data, you can set the script to run on a time-based trigger. In the left menu of the script editor, look for “Triggers” and add a time-based trigger.
Once you have your script running and data going into your Google Sheet, we are ready to go to Big Query.
Step 2: Google Big Query
Big Query is Google’s Business intelligence and cloud storage service. Our use of it will revolve around connecting Big Query to our previously created Google Sheet (an external table) and then defining a query that runs a query that transforms the data in the Google Sheet and creates/updates a Big Query native table.
I’m not covering in detail using Big Query, setting up a project, or even creating a dataset. Those are pretty straight-forward but you will need to authorize the Google Cloud API and enable the Big Query service with your Google account.
Beyond that the steps are as follows:
- Create a Big Query/Google Cloud Project
- Create a Big Query Dataset
- Create a table inside our dataset, connected to our Google Sheet
- Create a SQL view that creates/and overwrites a Big Query native table.
Once that step is completed, we will be able to Connect Google Data Studio to our Big Query Table and create our dashboard report.
Create a Big Query Table from a Google Sheet
What you need:
- The link to the Google Sheet
In Google Drive, right mouse click on the Google Workbook and select, “Get Link”. Select Copy Link from the resulting screen and paste (save it in a text document or somewhere). - The name of the Sheets inside the Google Workbook
Ours is named SALES_DATA.
Create Table Process: (screen shots below)
- Expand Project to display your dataset (or create a dataset)
- Click the menu (dots) to the right of your dataset and select “Open”
- In the dataset console that opens to the right, select “Create Table”
- Under “Source” select “Drive”
- Paste your link into “Select Drive URI”
- Under “File format” select, “Google Sheet”
- Under Sheet Range, Enter your sheet name (“SALES_DATA”)
- Your project and dataset should already be selected
- Under table name enter a name for your Big Query table.
I preface my external Google Sheet tables with GS_, so I used:
GS_SALES - Check, “Auto detect: Schema and input parameters”
- Enter “1” in “Header rows to skip:
(this ensures the table field names are determined from the Google Sheet headers) - Google-managed key is fine for this table.
- Click “Create table”
Big Query Create table from a Google Sheet
You can test the table by running a query against it. It should display the data from your Google sheet.
Now we can create a more complex SQL query to transform the data for simpler, and faster, reporting.
Create a Big Query native table and transform our data
One of our goals is to report numbers based on different values contained in a few different fields. This can be accomplished fairly quickly with a pivot table but gives us far less control over displaying data where no values exist and also performing those counts across multiple fields.
The easier way to do this is with a sql query that uses a series of field value specific case statements.
For instance, we want to show the number of sales for each category so our select and case statements look like this:
::SQL Statement
SELECT *,
case
when Sales_Level='Tier 1' THEN 1 else 0 end as Tier1Ct,
case
when Sales_Level='Tier 2' THEN 1 else 0 end as Tier2Ct,
case
when Sales_Level='Tier 3' THEN 1 else 0 end as Tier3Ct,
case
when Sales_Level='Platinum' THEN 1 else 0 end as PlatinumCt,
case
when Sales_Level='Gold' THEN 1 else 0 end as GoldCt,
case
when Sales_Level='Silver' THEN 1 else 0 end as SilverCt,
case
when (Sales_Level<>'Tier 1' and Sales_Level<>'Tier 2' and Sales_Level<>'Tier 3'
and Sales_Level<>'Platinum' and Sales_Level<>'Gold' and Sales_Level<>'Silver')
OR (Sales_Level is NULL)
THEN 1 else 0 end as OtherCt,
case
when Cash_or_Credit='Cash' THEN 1 else 0 end as CashCt,
case
when Cash_or_Credit='Credit' THEN 1 else 0 end as CreditCt,
case
when Cash_or_Credit<>'Credit' and Cash_or_Credit<>'Cash' THEN 1 else 0 end as NotCashCreditCt
FROM [your big query table reference]
::Ened SQL Statement
We can get significantly more complex in our SQL statements if necessary.
Big Query allows us to save this as a Schedule view and under Query settings, set a destination table for the query.
Once those settings are saved we can then schedule this query/view to run
Once scheduled, you will now have a native Big Query table to work with in Google Data Studio.
Why use a native Big Table Query?
A little word about performance. With my client’s data, the following performance differences were noted:
Querying the entire dataset (2 years of data):
- Google Sheets to Data Studio: 8 to 12 seconds
- Big Query Tables to Data Studio: 1 second
Let’s jump about Google Data Studio
Step 3: Google Data Studio Dashboard
Google Data Studio is NOT Power BI or Tableau. But, it is available on the cloud, free, for the most part, and provides a simple mechanism for creating and distributing reports.
Steps to Connect and Display Data in Google Data Studio
- Create data source - connected to your Big Query table
- Create custom fields for totals
- Create report and select data source created in step #1 above
This is covered in detail in the YouTube video starting at 25:26
SELECT the BigQuery connector
Authorize the BigQuery connector
Select Your Big Query Project, Datasource, and Table
Your Datasource will reflect the table's fields.
This is where you create calculated fields. Watch the video for more details.
CONCLUSIONS
I demonstrate how to layout the report and add a date and drop-down data filter in the video.
If you found this information helpful, have a suggestions, or if you have a question, let me know in the comments here or on YouTube.