Select Page
Google Sheets Function - Template Guide
Google Sheets Function - Template Guide

Google Sheets Function - Template Guide

With the Google Sheets Function, you can automatically create Google Sheets documents that contain your Airtable data. This template guide will show you how to set up your Google Sheet template for your Airtable data. This template guide is for the

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 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 and conditional formatting.

In this article

The Basics of Setting Up Your Google Sheet Template

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

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 {{

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.

The templates use a syntax called Handlebars. You can create Simple or Complex templates.

Simple Templates: {{template-type options....}}

Complex templates with inner body: {{#template-type options...}} {{inner-types}} {{/template-type}}

Formatting Template Parameters

To format the templates, add the template type name, then a space followed by an =. Finally, surround your data or formatting in parentheses.

Example:

{{date value="{{Airtable date field name}}" pattern="YYYY-MM-DD"}}
date - template type
value= - parameter
"{{Airtable date field name}}" - value```

image

Template Types

Field Placeholder

{{Your Airtable Field Name}} - These are used to insert your data anywhere in the file. Wrap your Airtable field name in curly brackets - {{Your Airtable Field Name}}

Example: If your Airtable field names are Client Name, Client Address, and Client Contact Name, you would add them as {{Client Name}}, {{Client Address}}, and {{Client Contact Name}} in your Google Sheets file where you want them to appear when the document is generated

Table

Tables can be created with data from your Airtable record(s) in a few different ways:

  • Linked Record Field - Use a source of a Linked Record field (to another Table), along with field names of the linked record table.
    • This is useful for generating ‘report’ style documents where you want to include a list of multiple records as a list in a single file, such as on an Invoice or Quote
    • Format: {{table source="Linked Table Name (from Linked Record)" field="Field Name in Linked Table"}}
    • {{table field="Status"}} | {{table field="Image"}} note: the | here represents different cells note: The {{table source}} placeholder template is only used in the first cell to specify the Linked Record Field name

  • MultiSelect Field Values - Create a list of the values from a multiselect selection ex: {{table source="MultiSelect Field"}} Note: This will be a single column table

  • Lookup/Rollup Field Values - Use source with Lookup/Rollup values to display in table For multiple columns, list these in separate cells next to each other: ex: {{table source="Lookup Field 1"}} | {{table source="Lookup Field 2"}} note: the | here represents different cells note: For rollup values, the formula should be: ARRAYJOIN(values,"|")

Additional table parameters:

sort - Ability to sort the data prior to displaying. Inner fields: field and order Ex: {{table sort="field: Name; order: desc"}

type - Each cell can specify a type to format the cell value. Available types: text | number | date | datetime | currency | time | percent | scientific | formula | image | link

rowHeight - specify the row height (in inches) for each of the table rows ex: {{table rowHeight="2"}} - will create 2 inch rows for the table rows

empty - if now rows, this text will be displayed in a merged cell instead of any row data ex: `{{table empty="No Data Available"}}

push - true by default - will push any data under the table columns down accordingly based on the number of data rows. If set to false, then data will override any data in existing cells within the range of the data. Useful to disable if the number of rows is accounted for in the template and already made room for the to be inserted data.

image

Image

Insert images in a specific location

There are 2 ways to insert images - With a Placeholder of your Image Field Name - {{Image Field Name}} or with the {{Image field}} parameter

To use images with a Placeholder of your Airtable field name:

  • Create a general image to be used as your placeholder. It should approximately the same size as your Image. Example:
image
  • Insert your placeholder image into your document where you want it
  • Right-click your image and choose Alt Text
  • In the Title field, add the name of your Airtable Field Name surrounded by brackets Example: {{My Airtable Image Field Name}}

To use images with the {{image field}} parameter:

  • Use {{image}}
  • Add your Airtable field name in the parentheses

Example: {{image url="{{Url}}"}}

Options:

value - add your Airtable field using brackets - {{Your Airtable Field Name}} or static text

index - determines which order the image will be pulled from. Use first, random, or a specific image within the attachments (0 for first, 1 for second, etc)

field - The field name to retrieve the Attachments or URL of the image

Link

This is used to insert your Airtable data as a link

{{link}}

Example:

{{link url="<https://on2air.com>" value="On2Air Website"}} {{link url="<https://on2air.com>" value="{{Name}}"}}

Options:

url - add your static url or your Airtable field using {{ }} brackets

value - add static text or your Airtable field using brackets to display your URL as text - {{Your Airtable Field Name}}

Percent

Format your data as a percentage

{{percent}}

Example: {{percent value="0.67" pattern="00%"}}

Options:

value - add your Airtable field using brackets - ((Your Airtable Field Name}} or static text

pattern - how your percentage is formatted

Currency

Format your data as a currency

{{currency}}

example: {{currency value="5.67" pattern="$00.00"}}

Options:

value - add your Airtable field using brackets - ((Your Airtable Field Name}} or static text

pattern - how your currency is formatted

Formula

Generate a formula using your data. You can specify the cell where you want the data to be inserted.

{{formula}}

Example: {{formula value="IMAGE(A1, {{ValueHere}})"}}

Options:

value - add the formula and then add your Airtable field using brackets - {{Your Airtable Field Name}}

Text

Number

Format your data as a number

{{number}}

Example: {{number value="2.3" pattern="0.00"}}

Options:

value - add your Airtable field using brackets - ((Your Airtable Field Name}} or static text

pattern - how your number is formatted

Date

Format your data as a date

Options:

value - add your Airtable field using brackets - {{Your Airtable Field Name}} or static text

pattern - how your date is formatted, such as YYYY-DD-MM

Grid

Adds all data from a field or linked table and will generate a flexible grid table

{{grid}}

Options:

field - your Airtable field name

source - your Airtable table name

type - you can use text, number, image, and link. If you specify the type, you can format the cells as needed.

rowHeight - the height of your row

colWidth - the width of your column

columns - specify how many columns

Default: 3 columns

Example:

{{grid field="Image" source="Template Data" type="image" columns="2" rowHeight="150" colWidth="125"}}

Advanced

Conditional Formatting

ifEq - only display when the data from this field is equal to a certain value. This parameter needs to be your Field Name in Airtable.

ifVal - only display if this value is present. This parameter needs to be a specific Value from the Field Name you chose.

Example: {{link field=”Record Link” ifEq=”Name” ifVal=”Client A”}}