At On2Air, we’re focused on ensuring companies protect their data with our premium Airtable backups app, On2Air Backups. On2Air Backups exports your Airtable data to Google Drive, Dropbox, or Box on an automatic schedule.
Because of this, the On2Air app used in this tutorial has been shut down.
What if I told you that you can embrace the power of Airtable AND dive into reports and formulas in Google Sheets using all the same synced data?
Since you’re already using both programs, you know how valuable Airtable is for connecting all your data together with relationships. But you also know the frustration of not being able to create enhanced reports, charts, or specific formulas like you can in Google Sheets.
Having a synced version of an entire table from your Airtable base in a Google Sheets file can solve many of the features Airtable lacks that a spreadsheet provides. With the On2Air Google Sheets Table Sync integration, you can sync and integrate entire Airtable tables to a Google Sheets file in a single instance.
In this tutorial, I’ll walk you through how to sync tables from your Airtable base to a Google Sheets table using the On2Air function integration.
In this article
Using Airtable and Google Sheets to Analyze Data
Spreadsheets have long provided a powerful way of storing and analyzing important information in a separated, easy-to-filter and sort method. With Google Sheets, you can do advanced reporting, complex charts, collaborate with users already using Google Workspace, and use formulas on a cell-by-cell basis.
With Airtable, you can create bases that use multiple field types for better data organization, and you can create dynamic links between your records to find everything that’s connected. And despite all the complex functionality, it’s still an app that’s easy to navigate.
When you sync Airtable and Google Sheets, you get to use the best features of both apps.
How to set up the automation sync
Set up your Airtable Base and a Google Sheets file
Next, duplicate the Marketing Plan Airtable template into your workspace. We’ll use this as our synced base.
In the Initiatives table, choose the All Initiatives view. Add a Checkbox field to the Initiatives table labeled Sync and add a checkmark to all the records in that table.
Next, create a Google Sheets file in your Drive and give it a name like Marketing Plan. Then, rename the first tab Initiatives. You can then leave the file blank.
Create the Sync Automation
- Open the Actions App from the On2Air dashboard
- Click Google Functions
- Click Create New Google Function
- Choose Google Sheets Table Sync
Airtable Configuration
- Choose Airtable to Google Sheets as the Sync Mode
- Choose your Airtable Connection in Source Connection – here’s how you connect Airtable to On2Air
- In Source Configuration, choose your Airtable base (Marketing Plan), and the table (Initiatives)
- Choose Checkbox as the Trigger Mode – this is what starts the table sync
- Choose the Sync field as the Checkbox Field
- Choose Checked as the Trigger On option
Google Sheets Function Configuration
- Choose your Google Drive connection
– here’s how to set it up in On2Air - Choose your Source Google Sheets file
-the Marketing Plan spreadsheet you created
- Enter your Source Sheet (Tab) as Initiatives
- Enter A1 as the Table Start Cell
– this is where the table will start - In Headers, choose Create Header from Airtable Fields
– Headers will be created based on your Airtable field name entered in the Table Start Cell
Now, let’s map the Airtable fields to the columns in Google Sheets
I suggest you map them in the same order as your Airtable base, so it’s easier to compare your files.
- Choose Map Fields & Order as your Data Source
- Choose each Airtable field you want displayed in your Google Sheets file
- Click Add new mapping for each one until you have them all added
Displaying Linked Record Names
- In Advanced Options, turn on Linked Record Names
When you use Linked Record Names, you’ll see the actual record name of your Linked Record synced to Google Sheets instead of displaying the Record ID, which is a unique number.
- Click Save Action Function Details
Testing the Google Sheets Table Sync
Before we can start using the function, we need to test it to ensure everything is set up correctly.
- Click the Test Function tab
- Select an API Key – you’ll need this if you use the function in Airtable automations, Airtable scripting, or Zapier
– here’s how to create an API Key in On2Air - Click Start Test
- Click Test Function
View your Airtable data in Google Sheets
If everything is set up correctly and the test ran successfully, you should now see all the records from the Airtable Initiatives table synced to your Google Sheets file!
You’ll also notice two additional columns added – [Airtable Record ID] and [Airtable Last Sync].
The Record ID is the unique ID for that specific record. The last sync shows you the date and time of the last time the function synced, so you’ll always know if it’s up-to-date.
Installing and Using the Function
You’ve done the initial setup and tested your function successfully. Now, we just need to choose how, where, and when you want it to run.
With On2Air, you can use our built-in Scheduler to choose a specific time to process your automation. You can choose to process it instantly, every few minutes, hourly, daily, weekly, or monthly. You can also choose to use the function in an Airtable Automation inside your base, add it to an Airtable Script, use it in a Zapier zap, or in a custom app.
We’re going to use the On2Air Scheduler for this automation.
- Click the Installation tab
- Enable the function
- In the Frequency option, choose Check every X minutes/hours/days/weeks/months
- Choose Week(s) as the Interval and enter 1 in the field to the left of the interval
- Select Saturday as the Day of the Week, 01 as the Hour, 00 as the Minute, and AM as the time period
- Click Run Scheduler
- Click Save Scheduler Settings
Your Airtable to Google Sheets Sync is now active! Every Saturday at 1:00 AM, it will process as long as the Sync checkbox is checked in a record.
Final
Now you know how to sync a table in Airtable to a Google Sheets spreadsheet. Connect Airtable and Google Drive to On2Air, set up the automation, test it, then set the schedule. You get the powerful relational feature of Airtable with the enhanced charts and formulas in Google Sheets. Integrating Airtable and Google Sheets gives you a powerful tool for making your business processes run smoothly.