Learn more about sending email, using templates, and using expressions with workflow in the Workflow: Sending Email section.

Email Templates

You can control the content and format for your email body and attachment with a template, contained in either a Google Docs or Microsoft Word document.. Your template may include:

  • Text
  • Images
  • Variables that refer to column values in your tables
  • Expressions that yield computed values

The email body or attachment will contain your template with all variables and expressions evaluated and replaced with the resulting values, as described below.

You can use template files for both event-triggered and scheduled reports. The templates for both are largely identical, differing only in the start expression used to select the rows being displayed.

Template Data Sources

We support Google Docs template files stored on these data sources:

  • Google Drive

We support Microsoft Word template files stored on these data sources:

  • Box
  • Dropbox
  • Google Drive
  • Microsoft Office 365
  • Microsoft OneDrive

If you wish to use a workflow template, your account must include one or more of these data sources. Add a new data source from the Account > Data Sources tab.

Creating a Workflow Template

You can ask AppSheet to create a sample template file that displays the rows in your tables that you can then customize as you see fit. This makes template creation much easier.

Create a sample template file as follows:

  1. Add a new workflow or report and save it for the first time. Alternatively, open any existing workflow or report.
  2. Click the Create button following either the Attachment Template or Email Body Template property. When you click Create, AppSheet will create a sample template and write it to your default data source.
  3. The template will be given a unique name based on the workflow or report name, the template type (i.e., Attachment or Body), and the date and time you click Create, ensuring every template has a unique name.
  4. Click View to view and edit the new template file.

The Email Body Template always refers to a single Google Docs or Microsoft Word doc template file. You cannot use an expression to specify the Email Body Template file. If you need to choose between two or more template files, define a separate workflow rule for each template file and specify an expression in the Condition property to select which workflow rule to invoke.

Controlling the Workflow Template Data Source

By default, the template is created on your default data source. For example, if your default data source is Google, the Create button will create a Google Docs template file on Google Drive. If your default data source is Box, Dropbox, Microsoft Office 365, or Microsoft OneDrive, the Create button will create a Microsoft Word doc template file on your default data source.

The Editor fields Email Body Template Data Source and Attachment Template Data Source indicate which data source contains your template files.

You can control which data source is used by clicking the Browse icon and selecting a Google Docs or Microsoft Word doc template file using the Get data from ... dialog box. If you click the Create button after doing so, the created template will be saved on the data source you selected while browsing.

Viewing or Editing a Workflow Template

Once the template is created, click the View button to view or edit the template.

The View button works well for Google Docs.

The View button does not work well for Microsoft Word docs because of built-in limitations in the way Microsoft Word docs can be opened and edited from the web. Instead, login to your data source and edit the template file there.

Template Formatting Tips

The following tips may help you control the appearance of your PDF attachments.

In Google Docs:

  1. Under Format > Line spacing, reduce the line spacing to Single if it is currently larger, such as 1.5, Double, etc.
  2. Under Format > Line spacing, remove Add space before paragraph and Add space after paragraph.
  3. Under Format > Paragraph styles, change the header formatting to Normal Text rather than Heading 1, Heading 2, etc. Then apply formatting to control the header's font size, color, boldness, etc.
  4. Remove any excess blank lines.
  5. Under File > Page setup..., check the page size and margins. For example, consider setting the page size to A4 and reducing the top and bottom margins.

In Microsoft Word:

  1. Try the ideas described for Google Docs.

Viewing the Template in HTML Format

The Google Docs or Microsoft Word template is read in HTML format when it is retrieved by AppSheet. AppSheet then replaces the template variables in the HTML template with the data values you specify. The resulting HTML document becomes the email body or attachment. For email attachments, we convert the HTML document to PDF format and add the result as the attachment.

When you are designing your template, it can be helpful to see what your template looks like in HTML format. This allows you to fine tune your Google Docs or Microsoft Word template to yield better looking HTML.

In Google Docs:

  1. Open the template in Google Docs.
  2. In the Google Docs File menu, click Download as and select Web Page (.html zipped).
  3. If you are using the Chrome browser in Windows, the browser will download the file as a zip file and display a message at the bottom of your browser window.
  4. Click the up arrow and select Show in folder.
  5. Right-click on the zip file and select Extract All .... This will extract the HTML file from the zip file.
  6. Double-click on the extracted HTML file to view it in the browser.

Controlling Template Image Sizes

Image and Thumbnail columns included in the workflow email body template are displayed as images. By default, the displayed image size depends on the column type:

  • Thumbnail column: 200 x 200
  • Image column: 600 x 600

You can control the displayed image size by creating a format rule in the UX > Format Rules tab of the app editor. To create a format rule:

  1. Choose the table containing the thumbnail or image.
  2. Pick the column or columns containing your thumbnails or images.
  3. Optionally specify a condition.
  4. Choose one of the following image sizes: Tiny (50 x 50), Small (200 x 200), Medium (600 x 600), or Large (1000 x 1000).

Page Breaks in PDF Attachments

You can include page breaks in the PDF file created from an attachment template file by manually inserting a page break in the template.

In Google Docs:

  1. Position the cursor where you want the page break to occur.
  2. From the Google Docs Insert menu, select Page break.

In Microsoft Word:

  1. Select the Insert tab.
  2. Position the cursor where you want the page break to occur.
  3. From the Microsoft Word Pages menu, select Page Break.

Page Headers and Page Footers

Workflow templates do not support the Google Docs or Microsoft Word page header and footer features.

Using Custom Fonts

You can use custom fonts in your Google Docs attachment template file. The resulting PDF attachment file will use these custom fonts.

Do this as follows:

  1. Open your Google Docs template file in Google Docs.
  2. From the fonts dropdown on the Google Docs menu bar, select "More fonts ...".
  3. In the "Fonts" dialog box that is displayed, look at "My fonts" on the right side of the dialog box.
  4. If the font you wish to use is not displayed, use the "Search" box to find the font. For example, you can use the "Libre Barcode" fonts. To choose one or more of these fonts, enter "Libre Barcode" in the "Search" box. This should display a list of five "Libre Barcode" fonts. Fonts you are using should be displayed in blue text with a checkmark to their left. If a font is missing, click it and the font should be displayed in blue text with a checkmark to its left.
  5. In your workflow template, select the text you wish to assign a custom font, and select the font name from the "Fonts" dropdown on the Google Docs menu bar.
  6. Trigger your workflow rule and ensure the PDF attachment is using the custom font.

Troubleshoot Custom Fonts

Some customers have reported problems when using custom fonts. This may help you resolve such problems.

When you use custom fonts like "Libre Barcode 128" and "Libre Barcode 39 Extended Text", the HTML file that is returned when we read the Google Docs workflow template must contain an "import url" that refers to these custom fonts. When your workflow rule fires, we read the Google Doc file in HTML format, create the attachment from the HTML template file, and convert the resulting attachment from HTML to PDF. The HTML to PDF conversion service relies on the HTML file containing the right "import url" value. The "import url" must refer to all of the custom fonts that you are using in your PDF attachment. If the "import url" is missing one or more of your custom fonts, the data values in the PDF attachment file are displayed in the default font rather than in the custom font you want.

You can verify that the Google Doc contains the right custom fonts as follows:

  1. Open your Google Docs template file in Google Docs.
  2. On the "File" menu select "Download as" and then "Web Page (.html zipped)".
  3. Google Docs will export your Google Docs template file and display the HTML file name at the bottom of the web page.
  4. Click the ^ and select "Show in folder".
  5. Right click on the .zip file in the folder and select "Extract All ...". This will unzip the .zip file containing you template in HTML format.
  6. Right click on the .html file and select "Rename". Change the file extension from .html to .txt.
  7. Right click on the renamed template .txt file and select "Edit".
  8. Near the top of the template file, look for "@import url". It should look something like "@import url('https://themes.googleusercontent.com/fonts/css?kit=mJ_rGOyyL62_i4eysdBvxCC0kCcps6hmr3Uub3CurLj6HJltGXvViurXJL6xt9sxwzYVTjVaNNEx2pDT4aBU4J6Nc2zktOcTrnnZfLDyCt73rGVtsTkPsbDajuO5ueQw')"
  9. Copy the url value starting from "https:" until just before the closing single quote and paste it into the address line of your browser. 
  10. This should display something like the value below.
  11. Make sure all of the custom fonts you are using are listed. 
/* latin */
@font-face {
  font-family: 'Libre Barcode 128';
  font-style: normal;
  font-weight: 400;
  src: local('Libre Barcode 128 Regular'), local('LibreBarcode128-Regular'), url(https://fonts.gstatic.com/s/librebarcode128/v8/cIfnMbdUsUoiW3O_hVviCwVjuLtXeK_H9AI.woff2) format('woff2');
  unicode-range: U+0000-00FF, U+0131, U+0152-0153, U+02BB-02BC, U+02C6, U+02DA, U+02DC, U+2000-206F, U+2074, U+20AC, U+2122, U+2191, U+2193, U+2212, U+2215, U+FEFF, U+FFFD;
}
/* latin */
@font-face {
  font-family: 'Libre Barcode 39 Extended Text';
  font-style: normal;
  font-weight: 400;
  src: local('Libre Barcode 39 Extended Text'), local('LibreBarcode39ExtendedText-Regular'), url(https://fonts.gstatic.com/s/librebarcode39extendedtext/v7/eLG1P_rwIgOiDA7yrs9LoKaYRVLQ1YldrrOnnL7xPO4jNP6Me7gj.woff2) format('woff2');
  unicode-range: U+0000-00FF, U+0131, U+0152-0153, U+02BB-02BC, U+02C6, U+02DA, U+02DC, U+2000-206F, U+2074, U+20AC, U+2122, U+2191, U+2193, U+2212, U+2215, U+FEFF, U+FFFD;
}
/* latin */
@font-face {
  font-family: 'Libre Barcode 39 Text';
  font-style: normal;
  font-weight: 400;
  src: local('Libre Barcode 39 Text Regular'), local('LibreBarcode39Text-Regular'), url(https://fonts.gstatic.com/s/librebarcode39text/v8/sJoa3KhViNKANw_E3LwoDXvs5Un0HQ1vT-0H0h5K.woff2) format('woff2');
  unicode-range: U+0000-00FF, U+0131, U+0152-0153, U+02BB-02BC, U+02C6, U+02DA, U+02DC, U+2000-206F, U+2074, U+20AC, U+2122, U+2191, U+2193, U+2212, U+2215, U+FEFF, U+FFFD;
}

Sample Email Templates

The sample templates that follow are all based on the Order Capture sample app. This sample includes both an email body template and an attachment template. The variables in these templates refer to the column values in the Order Capture sample app.

Template with Column Name Variables

Let's begin with a very simple template that displays the column values from a row in the Customers table. The template contains formatted text and column name variables. Each column name variable specifies the name of one column value from the Customers row.

The template produces the following output:

Template with Expression Variables

This template contains both column name variables and expressions. It displays column values from the Orders table, column values from its parent Customers table, and computed values from its child Order Details table.

It displays the Customers Name column value using this expression:

Customer Name: <<[Customer Name].[Name]>>

"[Customer Name]" is the "Customer Name" reference column value in the Orders table. It refers to the parent Customers row. "[Customer Name].[Name]" displays the "Name" column value from the parent Customers row.

This template computes the total value of all child Order Details rows using this expression:

SUM(SELECT(Order Details[Total], [Order Id] = [_THISROW].[Order Id]))

The expression computes the sum of all Order Details rows having an "Order Id" column value that matches the "Order Id" in the current Orders row. That is, it computes the sum of all Order Details rows for this Orders row.

Templates with Embedded Templates

You can display a hierarchy of rows using embedded templates. For example, you can display a single Customers row. Beneath the Customers row, you can display all of its child Orders rows. Beneath each Orders row, you can display all of its child Order Details rows.

Each embedded template starts with <<Start:start-expression>> and ends with <<End>>. The start expression following the colon yields the list of rows to be formatted using the embedded template. The <<Start>> and <<End>> elements must match, much like parentheses do within an arithmetic expression.

Order Template in List Format

This template is used when an Orders row is updated.

It contains templates that display:

  1. The single parent Customers row for the updated Orders row displayed in list format.
  2. The single updated Orders row displayed in list format.
  3. The list of child Order Details rows for the updated Orders row displayed in list format.

Order Template in Table Format

This template is used when an Orders row is updated. It's like the previous template, but displays the Order Details rows in table format. It contains templates that display:

  1. The single parent Customers row for the updated Orders row displayed in list format.
  2. The single updated Orders row displayed in list format.
  3. The list of child Order Details rows for the updated Orders row displayed in table format.

The Order Details rows are displayed in table format by the following portion of the template. It contains a header row template element, a data row template element, and a trailer row template element. It produces a table containing one header row, one or more data rows, and one trailer row.

The first cell in the data row template element contains a <<Start:start-expression>> followed by an expression yielding the first data value to be displayed. The start expression following the colon yields the list of child rows to be formatted using the data row template element. In this case, the Start Expression yields the list of child Order Details rows for the parent Orders row. The data row template element values between <<Start>> and <<End>> are used to format each child Order Details row returned by the start expression.

Because the start expression refers to the Order Details table, the variables within the data row template element refer to column values in the Order Details table. For example, <<Quantity>> refers to the Quantity column value of that table. <<[Product Id].[Product Name]>> dereferences Product Id (a Ref column that points into the Products table) to retrieve the corresponding Product Name value (from the Product table).

The last cell in the data row template element contains the name of the last data column value to be displayed. It is followed by <<End>>.

A trailer row template element can be used to display summary information. Here we display the total value of all preceding Order Details rows. The outer enclosing template isn't shown here, but it displays Orders rows. Hence, <<Order Total>> refers to the Order Total column value in the Orders table.

Customer Template in List Format

This template is used when a Customers row is updated.

It contains templates that display:

  1. The single updated Customers row displayed in list format.
  2. All child Orders row for the updated Customers row displayed in list format.
  3. All child Order Details rows for the parent Orders rows displayed in list format.

The part of the template that displays Orders rows starts with this expression:

Start:ORDERBY([Related Orders By Customer Name], [Order Date])

<<Start:start-expression>> signifies the start of an embedded template. The start expression following the colon yields a list of child rows to be formatted using the embedded template. The embedded template is terminated by <<End>>. Between <<Start>> and <<End>>, include the formatted text, variables, and expressions to format each Orders row.

The start expression above includes an ORDERBY() to sort the child Orders rows in ascending (oldest-to-newest) order by the Order Date column value. If order wasn't important, you could omit ORDERBY() and simply specify:

Start:[Related Orders By Customer Name]

You can limit the number of rows displayed by using a TOP() expression with an ORDERBY() expression. The first argument to the TOP() expression must be an ORDERBY() expression. The second argument to the TOP() expression specifies the maximum number of rows to be chosen. This second argument can be either a constant or an expression. For example:

Start:TOP(ORDERBY([Related Orders By Customer Name], [Order Date]), 3)

You can select the child rows to display by specifying a SELECT() expression. For example, to display Orders having an Order Status value of Open, specify this SELECT() expression:

SELECT([Related Orders By Customer Name][Order Id], [Order Status] = "Open")

This template produces the following output:

Customer Template in Table Format

This template is used when a Customers row is updated.

It contains templates that display:

  1. The single updated Customers row displayed in list format.
  2. All child Orders row for the updated Customers row displayed in list format.
  3. All child Order Details rows for the Orders rows displayed in table format.

A Google Docs table is used to display the Order Details rows. The table may contain zero or more header rows, one data row, and zero or more trailer rows.

The Order Details rows are displayed in table format by the following portion of the template. It contains a header row template element, a data row template element, and a trailer row template element. It produces a table containing one header row, one or more data rows, and one trailer row.

The first cell in the data row template element contains a <<Start:start-expression>> followed by an expression yielding the first data value to be displayed. The start expression following the colon yields the list of child rows to be formatted using the data row template element. In this case, the start expression yields the list of child Order Details rows for the parent Orders row. The data row template element values between <<Start>> and <<End>> are used to format each child Order Details row returned by the start expression.

Because the start expression refers to the Order Details table, the variables within the data row template element refer to column values in the Order Details table. For example, <<Quantity>> refers to the Quantity column value of that table. <<[Product Id].[Product Name]>> dereferences Product Id (a Ref column in the Order Details table that points into the Products table) to retrieve the corresponding Product Name value (from the Product table).

The last cell in the data row template element contains the name of the last data column value to be displayed. It is followed by <<End>>.

A trailer row template element can be used to display summary information. Here we display the total value of all preceding Order Details rows. The outer enclosing template isn't shown here, but it displays Orders rows. Hence, <<Order Total>> refers to the Order Total column value in the Orders table.

This template produces the following output:

Did this answer your question?