We are now focused on our On2Air Backups app, automated backups for Airtable. On2Air Backups exports your Airtable data to Google Drive, Dropbox, or Box on an automatic schedule.
The app mentioned in this tutorial has been shut down.
Airtable and Google Sheets are two powerful tools for viewing, editing, storing, and sharing your company data. Airtable is a relational database with a spreadsheet-like feel. With Airtable, you can connect all your company information together, view it in a variety of ways, and update it quickly.
Google Sheets is an extensive spreadsheet tool that allows you to quickly create lists of data for any purpose – company budgets, inventory, sales projections, and expense reports. With powerful formulas and a well-known table layout, it’s why companies continue to use it in their daily processes.
While both tools are similar, many companies still rely on and continue to use Google Sheets concurrently with Airtable.
In this guide, we’ll show you how you can use Google Sheets and Airtable together plus how to automatically create Google Sheets files containing your Airtable data with On2Air Docs.
Whether you want to sync your data automatically between the two tools, update a cell in Google Sheets after you change it in Airtable, edit your Google Sheets spreadsheet while you’re inside of Airtable, or add Sheets as a PDF, Excel, or CSV in your Airtable base, we’ll go through each of the ways you can use them together.
Table of Contents
How to Connect Airtable and Google Sheets
Using Airtable Data in Google Sheets and Exporting to Google Sheets
How to Automate Google Sheets Files from Airtable
Edit and View Google Sheets Inside Airtable
How to Connect Airtable and Google Sheets
There are several ways to can sync Airtable and Google Sheets.
Manually
– If you only have a few data points that need to be updated occasionally, this is the quickest way. You can set a reminder on your calendar once a month to manually copy and paste the information from Airtable to Google Sheets.
Airtable Automations
– With automations in Airtable, you can set up an automation to automatically add (append) a row to Google Sheets with your information. So, if you add a new row in your Airtable base, you can have an automation that will automatically create a new row in one of your spreadsheets. However, the automation options are limited. You cannot update, change, or search information in any other rows in your spreadsheet.
On2Air Docs – Automated Airtable Documents
With the On2Air Docs app, you connect directly to your Airtable base and automatically create Google Sheets, Google Docs, or Google Slides files.
Related: Airtable Documents with On2Air
Zapier or Make
– Zapier and Make are automation tools that let you send information between programs you use in your business. You can grab information from your Airtable bases and send it to Google Sheets.
Using Airtable Data in Google Sheets and Exporting to Google Sheets
With the On2Air Docs Google Document Automation, you can create Google Sheets files with your Airtable data. You can have it populate with the data from your Airtable records and store the file URL and/or a PDF in your Airtable record.
Instead of manually creating a spreadsheet for every new client or project, you can automatically create a new Google Sheets spreadsheet containing all the details you need from the data in your Airtable record.
You can then save the spreadsheet to a current Drive folder or even create a new folder in Google Drive. You can set it up to automatically export the new spreadsheet to an Attachments field in your Airtable base and add the URL of the file to a URL field.
You can choose to run the automations on a set schedule or you can run it based on a specific trigger, like when a checkbox is checked, or the data enters a specific view.
How to Automate Google Sheets Documents from Airtable with On2Air
To set up a Google Sheets documents automation in On2Air –
- Connect your Airtable workspace and Google Drive account to On2Air in the Integrations section
How to Connect Airtable to On2Air
How to Connect to Google Drive to On2Air
To follow along with this tutorial, copy the Marketing Promotions Airtable base to your workspace and the Promotions Tracker Google Sheets file to your Google Drive.
Create the automation
1. In the On2Air dashboard, open the Docs app
2. Click Create New Function
3. Choose Google Sheets Create/Export
(note: we also have a Google Sheets Table Sync automation for an entire table sync. Here’s a tutorial on how to do a full Airtable table to Google Sheets sync)
4. Click Add Configuration and then choose your Airtable connection, your base, and your table.
5. Choose when you want the spreadsheet to be created. You can use a Checkbox field, when a field is updated, when a particular date is selected, a custom formula, a Last Modified date field, or only on specific records.
In this case, we’re using the Checkbox field, chose “Send to Google Sheets” Checkbox field, and will trigger it when it’s Checked
6. Next, choose your Google Drive connection. Then in Source File Mode, choose Use a Single Template.
If you need files based on conditions or values in your Airtable records, you can use our dynamic option to use different files for different Airtable records. You can create multiple templates that are chosen based on specific values in your base.
Example: If you have a select field called Status with values of New, In Progress, and Completed, you can have 3 Google Sheets templates for each Status. Each contains different information or has a different layout.
7. In Source File, choose your Source/Template file. In this case, choose the Promotions Tracker Google Sheets file you added to your Drive.
Our Promotion Tracker Google Sheets template is already set up with the {{placeholders}} corresponding to the Airtable fields in the Marketing Promotions base
To see how to add placeholders to your own template, see the Google Sheets Template Placeholders Guide
8. Next, choose a folder in Google Drive for your Destination Folder. This is where you want to save the files in Google Drive.
9. Add your File Title – You can use your Airtable field placeholders and text
10. In Merged Output Configuration, choose Always Merge Fields to ensure the automation always creates a file with your data. Then, in Merged Output URL Storage Field, choose a URL field from your Airtable base to save the new file URL.
11. To save the new Google Sheets file as a PDF, you can choose Export Merged File and choose the PDF Format. To send it to an Airtable Attachment field, choose an Attachment field and whether to append the file to the end of the other files in the field or replace them.
12. Test the Function to ensure everything is connected
13. Enable the Automation and Install the Function. Choose the Scheduler option and set the Frequency to Instant (Checks every minute for updates)
This ensures your automation processes as soon as you click the Checkbox
And now!!
14. In your Airtable base, click the Send to Google Sheets checkbox
Watch how your Sheets file is created with your Airtable data, the Sheets URL file is automatically added the the URL field, and the PDF is added to your Attachment field!
Edit and View Google Sheets with Airtable
While automating data back and forth between Google Sheets and Airtable will continue to be a necessity for companies, being able to use both simultaneously is a common need. And many times, you want to be able to quickly reference your spreadsheet when you’re viewing your information in Airtable.
Airtable doesn’t have the ability to view Google Sheets inside your base. You’ll still need to open Airtable and open Google Drive to work with them side-by-side.
With On2Air Forms, you can edit and work directly in your Google Sheets spreadsheet while also viewing, editing, or updating Airtable. With On2Air Forms, your actual spreadsheet or any Google file is connected to your Airtable record. You don’t have to switch back and forth between programs.
You can even view and select files from your Google Drive with On2Air Forms.
Final Thoughts
Connecting Google Sheets and Airtable together helps teams and companies collaborate better and keep all the company data in one place. With On2Air apps, you connect your Google Sheet spreadsheets to any Airtable base to create, edit, and view your files all in one place.