LinkedIn
Twitter
AppSheet already provides the ability to automatically generate a PDF document based on a template, but sometimes you many need to generate a Google Doc with a link that can be accessed later. This is an example of how to generate a Google Doc based on a template and return a Google Drive link to the app.
Here we have a sales automation app for tracking customer leads assigned to sales reps. Let's say the sales rep needs a one-pager that gives a quick overview of one of their leads. We can do that from an AppSheet app.
Export Example #1: "Export Sales Lead" action
Export Example #2: "Export Table" action
Instructions to Replicate:
Step 1: Copy the Apps Script file located here
Step 2: Add the Apps Script automation to your app. You will need to authorize the project after you select the file.
Step 3: Select the right function from dropdown list. Select the exportDocs function to paste specific values of text or images into a document. Select the exportDocsWithTable if you need to iterate through a list of records and paste the data into a table in a document.
| exportDocs | |
| templateUrl | The link to your template sheet. For example: "https://docs.google.com/document/d/1PgIRV5hEi-PU2zHceaCXcKOub1cM9mChWu5WeFKlQag/edit" |
| docTitle | The new name for the spreadsheet after the export. For example: "Customer Report : " & [company_name] |
| userEmails | The recipients email addresses. You can have multiple as long as they are comma-separated. "user1@test.com", "user2@test.com" |
| content | The data that should be included in the export. The list is structured in pairs. The first item is the key label that is included in your document template. The second item is the data that you want to paste into the document. The script functions by finding the key and replacing it with the item directly following it in the list. Note that image urls are supported, but not file paths to Google Drive. The key name for an image must have "image" somewhere in the key name to be recognized by the script (e.g., "Company Image" or "Profile-image"). The image must also be contained within a table in the document template to ensure appropriate sizing. For example: LIST( TEXT("Company Name"), TEXT(LOOKUP([reference_id], "customers", "customer_id", "company_name")), TEXT("Full Name"), TEXT(LOOKUP([reference_id], "customers", "customer_id", "full_name")), TEXT("Company Image"), TEXT(LOOKUP([reference_id], "customers", "customer_id", "image")) ) |
| exportDocsWithTable | |
| templateUrl |
The link to your template sheet. For example: |
| docTitle | The new name for the spreadsheet after the export. For example: "Detailed Report for " & [region] & " Region" |
| userEmails | The recipients email addresses. You can have multiple as long as they are comma-separated. "user1@test.com", "user2@test.com" |
| tableKey | The key used to identify the table where data should be appended. This key must be in the first row and first column of your table so that the script can identify the correct table. It should not contain any brackets. For example: "Leads Table" |
| columns |
This contains the list of columns that you want to paste into your table. The columns must be listed in the same order that you want them displayed in the table. For example: SELECT(customers[full_name], [reference_id] = [region].[rep_id]), SELECT(customers[company_name], [reference_id] = [region].[rep_id]), SELECT(customers[email], [reference_id] = [region].[rep_id]), SELECT(customers[num_employees], [reference_id] = [region].[rep_id]), SELECT(customers[Formatted Revenue], [reference_id] = [region].[rep_id]) ) |
| content | The data that should be included in the export. The list is structured in pairs. The first item is the key label that is included in your document template. The second item is the data that you want to paste into the document. The script functions by finding the key and replacing it with the item directly following it in the list. Note that image urls are supported, but not file paths to Google Drive. The key name for an image must have "image" somewhere in the key name to be recognized by the script (e.g., "Company Image" or "Profile-image"). The image must also be contained within a table in the document template to ensure appropriate sizing. For example: LIST( TEXT("sales_rep"), TEXT(LOOKUP([reference_id], "reps", "rep_id", "sales_rep")), TEXT("email"), TEXT(LOOKUP([reference_id], "reps", "rep_id", "email")), TEXT("region"), TEXT(LOOKUP([reference_id], "reps", "rep_id", "region")) ) |
Step 4: Create your Docs template. You template will vary depending on the functions you need.
exportDocs: The template must include the key between brackets. For example, if you wanted to include the number of employees in the document template, you could name a key {Employees} as displayed below. There is no restriction on what you can name your key or the number of key-value pairs that can be included in the document.
Reminder that images must be contained within a table to ensure that it is sized appropriately and must contain "image" somewhere in the key name to function correctly.
exportDocsWithTable: Use this function if you need to iterate through a list of records that need to be pasted into a table. Start by naming the table in your template with a table key - this allows the script to identify the correct table. This key can be named anything as long as it resides in the first column and first row of the table. You will need to provide this "tableKey" as one of the parameters in the automation. Then add the column headers for the data that you plan to paste to your document template. These columns headers should be in the same order as the list you add to the "columns" parameters in the automation. Make sure to add one blank row after your column headers! This will ensure that the table is formatted the way that you prefer.
โ
| Template for "Export Sales Lead" | Template for "Export Table" |
 Template for Export Docs |
 |
Please note, this is merely one example of how to use Apps Script to export to Google Docs. Alternative implementations are certainly possible.
Quick Links
Example App (remember to authorize the apps script project in the automation tab if you copy this template)
Apps Script File
Docs Template
Thanks for the feedback! I'm glad this series of tips has been helpful.
Repeating table headers on each page can be accomplished by selecting "Pin header up to this row" next to the table in the Google Docs template. I have not turned it on in this Docs template, but you can in your version.
Hi Alafontant,
If it were possible, I would like to ask you a question since I do not understand the error I have.
The file is created correctly, but when I try to display it I get this error.
Any idea what it could be?
Thank you for this post
