Select Page
Function - Google Sheets Create/Export
Function - Google Sheets Create/Export

Function - Google Sheets Create/Export

The Google Sheets Function in On2Air Actions allows you to automatically create a new Google Sheets file and PDF document using data from your Airtable record. You can generate single page and multi-page documents that display a single record or list of records from your Airtable base. It will also upload the newly created file back into your Airtable base in an Attachment file.

About the Google Sheets Function

With the Google Sheets Function in On2Air Actions, you can automatically generate Google Sheets files that contain your Airtable data.

You can create single page documents, multi-page documents, create documents with an entire list of Airtable records, or with a single Airtable record!

image

The basic way to set up your Google Sheets template is to use the {{Airtable Field Name}} placeholder in any cell you want your data displayed.

  • The {{Airtable Field Name}} placeholder setup is most useful for creating a spreadsheet document per each Airtable record, where the spreadsheet will display field data from your Airtable records. This is useful whether you need a single file generated or multiple documents generated at one time that each contain data from their respective Airtable record.
  • If you need to include multiple records on a single spreadsheet and/or you want to customize the formatting, you can use the {{table}} placeholder

The Google Sheets Function contains templates including: Tables, Formulas, Currency, Percent, Numbers, Dates, Images, Links, and Grids. This approach allows the most flexible templating system for Airtable. Use your data inside Google Sheets and customize it with dynamic formatting.

General Function Configuration

  • Click Create New Function ✔
  • Choose your Function
  • Click Add Configuration
  • Set up your
    Common Function Configurations
    Common Function Configurations
    - Source Connection, Source Configuration, and Trigger Mode
  • Set up your Function Configuration

Google Docs Generic Instructions

Google Sheets Function Setup

Use this tutorial to learn how to automatically create Google Sheets files containing your Airtable data. How to Automatically Create Google Sheets Files with On2Air

Google Sheets Function Configuration

Google Connection (Required) - Select a Google Connection to use

Source File Mode (Required) - Select source to use for creating new files

Create Blank Doc - Create a new blank document in Google Docs

Use a Single Template - Use a single template file as your main template to create a new file(s) with your data. This is useful for using a single template to create a merged file with your data each time. This is the most common 'template' option.

Use Dynamic Templates - Use multiple template files based on a unique identifier in your Airtable base data to create multiple new files with your data. Example: Your record contains a Single-select field. You can choose to create a new document with specific information depending on the option you choose from your Single-select field.

Make & Use Copy of a Single Template - Use a single template file as your main template to create a new file(s) with your data. This is useful for making changes to the document and continuing to edit it after a merge.

Make & Use Copy of Dynamic Templates - Use multiple template files based on a unique identifier in your Airtable base data to create multiple new files with your data. This is useful for making changes to the document and continuing to edit it after a merge. Example: Your record contains a Single-select field. You can choose to create a new document with specific information depending on the option you choose from your Single-select field.

Use Existing Airtable Record Field Value - Use a field in Airtable where you already have a source file URL

Existing Source Field (Required for Existing Airtable Record Source Folder Mode) - Choose your Airtable URL field

Source File (Required for Single Template) - Choose your Source Template File to copy and create when the Function processes Must be signed into your Google account

Dynamic Field for Matching Source Files (Required for Dynamic Templates) - Select a field to be used as a unique identifier for creating new files. Once configured, this field will determine which files to create according to your unique identifier.

  • *Match Value **- This is the unique identifier name option you have listed in Airtable. Type each one exactly as seen in your Airtable field options. Example: If your Client Type single-select field contains Easy, Medium, and Hard, you will have 3 Match Values - Easy, Medium, and Hard.

Select Match Source File - For each Match Value, you will choose the template for the file you want create. You can click Remove if you need to remove the selection

Default Source File - You can set files to be copied and created as a default for any values/unique identifiers not listed in Match Values. If you don't choose any files in this options, no file will be copied and created if no match values are found.

Destination Folder Mode (Required) - Select where to save newly created files

OPTIONS:

Save All Files in Single Parent Folder - Save all files in a single root folder

Dynamically Save Files into Different Folders - Save files into different folders BASED ON A UNIQUE IDENTIFIER (example: Single-select option)Useful to combine this one with the On2Air Create Google Folder Function

Destination Folder URL already stored in a Record Field - Choose the field in your Airtable base where the Destination Folder URL is already stored.

Dynamically Save to Folder Name With Field Value - Will auto create folders within the selected parent folder based on the value of a field. Great for dynamic folder creation for large number of values.

Destination Folder (Required for 'Save All Files in Single Parent Folder') - Select the destination folder for newly created files

Dynamic Field for Matching Destination Folders (Required for 'Dynamically Save Files into Different Folders') - Select a field from your Airtable that determines which folder to save files to. Once configured, you will be able to choose an option from the field and files will be saved in their correct folder.

Match Value (For Dynamic Files)- This is the unique identifier name option you have listed in Airtable. Type each one exactly as seen in your Airtable field options. Example: If your Client Setup Type single-select field contains Easy, Medium, and Hard, you will have 3 Match Values - Easy, Medium, and Hard.

Destination Folder (for Matches) - Choose the folder that corresponds with your Match Value. Choose a Destination Folder for each Match Value. Example: If your Match Value is Easy, you might want to save all files related to 'Easy' records in a folder called 'Ready Clients'

Default Destination Folder (Recommended to Set a Default Folder) - Set a default folder for any values/unique identifiers not listed in Match Values. If you don't choose a folder as default, no files will be created or stored if no match values are found.

New File Title - Create the title for the new file. Use {{FIELDNAME}} placeholders to dynamically insert values from the record. Example: Use the name of the Client listed in your Airtable base as the new file title. If your field name in Airtable is Name, enter {{Name}} with brackets.If left blank, will use the source file name

File URL Storage Field (Optional) - Select URL or Single Line Text Field to store the Google URL for the newly created file

If left blank, then output file will be deleted upon completion of function call (unless exporting as a URL)If you're creating new documents from templates containing Airtable field placeholders, you may want to leave this blank. This URL option will only save your template file URL, not the newly created file that's based on your field placeholders.

If Airtable Field Value Exists (Required if File URL Storage Field is used) - If the URL Storage Field already has a URL value in it (the file already exists) you can choose what to do with it.

OPTIONS:

Finish/Do Nothing - This will ensure no changes or updates are made to the URL Storage Field and will leave it as-is. Will not perform any merge/export either.

Replace with New and Keep Old - Current file will be replaced with new one. Old file will remain in Google Drive. Any Merge/Export will use New File.

Replace with New and Delete Old - Current file will be replaced with new one. Old file will be DELETED. Any Merge/Export will use New File.

Use Existing - No New File will be created and Existing File will not change. Any Merge/Export will use existing File.

Field Merge Output

Merge any data from your Airtable record into a new Google Sheets file

Option to use the destination file as a template to generate a new output file with any {{FIELDNAME}} placeholders populated with field values from the record.

  • In your template file, enter your Airtable field name(s) as {{FIELD NAME}}. You must enter the Field Name exactly as listed in Airtable.

Example: If the Airtable field name you want to use in your Google Doc file is Client Name, enter {{Client Name}} as a placeholder in your template file

OPTIONS:

No Record Merge - Record Merging not needed

Always Merge Record - Perform a Record Merge on every execution (Default)

Merge on File Creation Only - Perform a Record Merge only when a new destination file is created

Merge on Existing File Only - Perform a Record Merge only when there was an existing destination file already stored in record

Merge Output URL Storage Field (Optional) - Select an Airtable field to store the Merged file URL If left blank, then merged file will be deleted upon completion of function call (unless exporting as a URL).

Delete Previous Merged File - When selected, if a previous merge file exists, it will be deleted after the new merged file is created

Create Custom Tables and Other Templates in Your Document (Template Guide)

You can set up a template to dynamically generate custom tables in your Google Sheets file. You can insert data and it will generate a table.

To get started add the table source syntax and field names - {{table source="Field Name|Field Name"}}

You can then create the layout of your table including if you want border, column widths, text and background colors, image sizes, line breaks, paragraphs, and more.

See all table options and a starter simple table template in the template guide below

Google Sheets Function - Template Guide

Google Sheets Function - Template Guide
Google Sheets Function - Template Guide

Image Replacements

Add dynamically generated images into your merged documents

The field type can be an Attachment Field or URL Field

  • Add a placeholder image with the image dimensions needed
  • Once you've added the placeholder image, right-click it and choose Alt Text
  • In the Title field, enter the name of the Airtable field that contains the Attachment image with 2 curly brackets around the name.

Example: If the name of the Airtable field is Sales Rep Image, then type {{Sales Rep Image:first}}If you have more than one attachment in the field, you can specify which image to add by using first, last, random, or the zero-index. For the zero-index, start with 0 as the first image, 1 as the 2nd image, 2 as the 3rd image, and so on.

Export File

Generate an Exported File and Save to an Attachment or URL Field

No Export File- No export needed

Export Merged File - Perform an export of the merged file This option is most useful if you want to save the newly created file in your base that was created from your template file with Airtable field placeholders

Export Saved File - Perform an export of the saved file instead of the merged file

Export Format

OPTIONS:

PDF HTML File Text File MS Word

Exported File Storage Field (Required for Exports) - Select the Field in your Airtable base to store the Exported file(s). Can be Attachment, Single-line Text, or URL Field typeAttachment Fields will download and save the file(s). Single Line/URL fields will save the URL(s)

Export Placement - Upon Export, select how to insert into the Attachment Field

OPTIONS:

Remove all existing attachments then attach new one - Remove any attachments from the field and then attach the new one

Append new attachment(s) to end of existing ones - Add new attachment to the end of all existing attachments

Prepend new attachment(s) to beginning of existing ones - Add new attachment to the beginning of all existing attachments

File Permissions

Set file permissions on the newly created file

OPTIONS:

No Modifications - Leave as-is. Will default to your global Drive or folder permissions Anyone with Link Can Read - People can view, but can’t change or share the file with others. Anyone with Link Can Edit - People can make changes, accept or reject suggestions, and share the file with others. Anyone with Link Can Comment - People can make comments and suggestions, but can’t change or share the file with others.

Advanced Settings

  • Next, choose any Advanced Settings you need ✔

Dynamic Source Match Mode - Determines how to compare match values to determine dynamic source

Exact Match (Not Case Sensitive) - Match values are exactly matched

Partial Match (Not Case Sensitive) - Match values are partially matched

Last Updated - Choose a date field to update each time your Function runs

  • Click Save Action Function Details

Test Your Function

Before using your Function, you must test it.

Install Your Function