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

Google Sheets Function - Template Guide

🔴 On2Air Docs has been shutdown to focus on our On2Air Backups Airtable app Learn more about automated Airtable backups - on2air.com

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, 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.

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"}}

image

Merge Fields

Overview

Merge fields are different forms of placeholders. You can place merge fields in your template document that will then be replaced by the merge values pulled from your Airtable table.

Field Placeholder

{{Your Field Name}}

These are used to insert your data anywhere in the file. Wrap the name of your Airtable Field in two curly braces.

Example

If your Airtable field names are Client Name, Client Address, and Client Contact Name then the field placeholders would look like:

{{Client Name}}
{{Client Address}}
{{Client Contact Name}}

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:

  • Using a Placeholder Image
  • The {{image}} template

Placeholder Image

  • Create/find an image to be used as your placeholder. It should be approximately the same size as your Image. Example:
image
  • Insert your placeholder image into your document where you want it
  • Click on your image. When it is selected three dots will appear in the upper right corner of the image. Click the three dots and then select “Alt text”
    • Note - If your placeholder image has a dark background it can be challenging to see the dots. Feel free to download and use our example placeholder image.
  • In the Title field, add the name of your Airtable Field Name surrounded by brackets Example: {{My Airtable Image Field Name}}

{{image}} Usage and Parameters:

field

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

index

determines which order the image will be pulled from.

Valid options are: first, last, random and numeric indexes starting from 0. “0” is first, “1” is second, etc

Example

{{image field="Quote_Image" index="1"}}
{{image field="Quote_Image" index="last"}}

Both of these templates will pull images from the Quote_Image Airtable field. The first example will get the second image, because the index starts at zero. The second example will get the last image in the Airtable field.

Link

This is used to insert your Airtable data as a link. To use a link field insert the template into a text box. Only one template per text box can be used. For example, if you have two link templates in a single text box, only the first link will display.

{{link}} Parameters

url

The URL you want to link to. This can either be a static value or another merge field.

value

This is the text that will be displayed.

Example

{{link url="https://on2air.com" value="On2Air Website"}}
{{link url="{{Quote_URL}}" value="{{Quote_Explanation}}"}}

The first example has static values for both the url and value. The link will look like: On2Air Website

The second example has placeholders for both values. The display text will be value contained in the Airtable field Quote_Title, and the url will point to the value in {{Quote_URL}}

Percent

{{percent}}

Formats your data as a percentage

{{percent}} Parameters

value

The number to be formatted as a percentage. This can either be a static value, or an Airtable field name wrapped in brackets.

pattern

How your percentage is formatted.

Example

{{percent value = "0.21" pattern ="00%"}}

{{percent value="{{Tip_Percentage}}" pattern="00%"}}

The first example uses a static value of 0.2. When formatted this will display as “20%”.

The second example will take the number stored and the Tip_Percentage Airtable field and similarly format it.

Currency

{{currency}}

Format your data as a currency

value

The number to be formatted as a currency. This can either be a static value, or an Airtable field name wrapped in brackets.

pattern

How your currency is formatted.

Example

{{currency value="5.67" pattern="$00.00"}}
{{currency value="{{Total Costs}}" pattern="$0.00"}}

Formula

{{formula}}

Generate a formula using your data.

{{formula}} Parameters

value

The written formula that you want inserted. Exclude the “=” that normal prefixes Google Sheets formulas. You can use your Airtable values by inserting the field name wrapped in curly brackets - {{Airtable Field}}

Examples

{{formula value="IMAGE({{Quote_Image}})"}}

{{formula value="2 * {{Total Costs}}"}}

The first example will create the formula image and insert the image stored in the Quote_Image field.

The second will multiple the value in Total Costs by 2.

Text

{{text}}

Inserts data from the selected Airtable Field into the text box. Insert a text box and position it where you want the merged data to go. Then enter the {{text}} field and options.

{{text}} Parameters

field

The Airtable field name that contains the text you want to insert

{{text field="Quote_Explanation"}}

The text from the Quote_Explanation Airtable field will be displayed.

value

Set static text that will display in place of the merge field.

Examples

{{text value="This text will display"}}

No Airtable data will display. Instead it will display “This text will display”

Number

{{number}}

This merge field enables you to format data as a number

value

Sets the static text for number that you want to format.

field

The Airtable field that contains the content to be merged.

pattern

Determines your number format. A few common examples:

Number
Pattern
Formatted Number
Explanation
10000
'0,0.0000'
10,000.0000
Insert commas, go out to 4 decimal places
10000.1234
'0[.]00000'
10000.12340
No commas, go to five decimal places. Because the number only has four decimals, a zero is added
-10000
'(0,0.0000)’
(10,000.0000)
Format negative numbers in parentheses. Insert commas. Go out to 4 decimal places.
1000.234
'$0,0.00’
$1,000.23
Inserts the currency sign. Rounds down to two decimal places. Inserts commas.

Note: The Google Docs Template Function uses the “format” parameter, whereas the Google Sheets Function uses the “pattern” parameter.

Numeral.js is used for the formatting. So any valid formats displayed in the Numeral.js documentation will work here.

Examples

{{number value="7910" pattern="0,0.00"}}
{{number field="Total Costs" pattern="0,0"}}
{{number field="Total Costs" pattern="$00.00"}}

Each of these number fields contain the same data but are formatted differently. The outputs are:

  • 7,910.14
  • 7,910
  • $7910.14

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

{{grid}}

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

{{grid}} Parameters

source

The source of the grid. This is your Airtable field that will populate the grid. Similar to how the table works, if this value is a Link Record then you have access to the additional fields on the Linked Record.

field

The Airtable field that you want to display

type

Defines the type of grid.

Valid options are: text, number, image, and link

rowHeight

The height of your row

colWidth

The width of each column

columns

Specifies how many columns will be created in the grid. If there are more items than columns, then the content will wrap to the next row. For example, if you had 4 items and columns was set to “2”, you would create a 2x2 grid. If columns is set to “1”, you will have one column and all of the content will extend down that column.

Example

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

Conditional Formatting

ifEq and ifVal

Used together these fields create a conditional that must be true for the merged field to be populate.

ifEq

The Airtable field that you are checking against.

ifVal

The field from ifEq must be equal to this value for the field to display

Example

{{text field=”Client Name” ifEq=”Client Name” ifVal=”Client A”}}

This text field will only display if the value Client Name is Client A

Go to