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

1. Create a Google Worksheet
2. Name the first sheet “AT Data Control”
3. 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.
4. Select "Tools->Script Editor"
5. 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:

1. Create a Big Query/Google Cloud Project
2. Create a Big Query Dataset
3. Create a table inside our dataset, connected to our Google Sheet
4. 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)

1. Expand Project to display your dataset (or create a dataset)
2. Click the menu (dots) to the right of your dataset and select “Open”
3. In the dataset console that opens to the right, select “Create Table”
4. Under “Source” select “Drive”
5. Paste your link into “Select Drive URI”
6. Under “File format” select, “Google Sheet”
7. Under Sheet Range, Enter your sheet name (“SALES_DATA”)
8. Your project and dataset should already be selected
9. 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
10. Check, “Auto detect: Schema and input parameters”
11. Enter “1” in “Header rows to skip:
    (this ensures the table field names are determined from the Google Sheet headers)
12. Google-managed key is fine for this table.
13. 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

1. Create data source - connected to your Big Query table
2. Create custom fields for totals
3. 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.