LinkedIn
Twitter
There are some cases when a user needs to export data from an AppSheet app and CSV/PDF may not be sufficiently consumable. This is an example of how to export data from AppSheet to a template in Google Sheets via Apps Script.
Here we have a sales automation app for tracking customer leads assigned to sales reps. Let's say the sales rep has two needs (1) "I need to export all the leads from my region into a Google Sheet" and (2) "I need to generate a quote for a specific customer in Google Sheets." We can accomplish both from an AppSheet app.
Export Example #1: "Export Report" action
Export Example #2: "Generate Quote" action
If you would like to export some data from your AppSheet app to Google Sheets, you can follow the instructions below:
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 exportSheets function to paste columns of data. Select the exportToSpecificCells if you need to put specific data from the app into specific cells in your Sheets template.
| exportSheets | |
| sheetTemplate | The link to your template sheet. For example: "https://docs.google.com/spreadsheets/d/1a9bPwHUoSsM7QDjb7C55_pjdGTnBNign/edit" |
| spreadsheetName | The new name for the spreadsheet after the export. For example: "Export for: " & [customer] |
| sheetName | The existing name of the sheet to paste the data. For example: "Sheet1" |
| userEmails | The recipients email addresses. You can have multiple as long as they are comma-separated. "user1@test.com", "user2@test.com" |
| startRow | The first row where data should be pasted. For example, to start at the second row: 2 |
| startColumn | The start column where data should be pasted. For example, to start at the first column: 1 |
| columns | The columns of data that should be included in the export. This will not include the column headers, so will need to include them in your Sheets template, like this template. For example: LIST( SELECT(Orders[Order ID], ([Customer] = [_THISROW].[Customer])), SELECT(Orders[Customer Name], ([Customer] = [_THISROW].[Customer])), SELECT(Orders[Quantity], ([Customer] = [_THISROW].[Customer])) ) |
| exportToSpecificCells | |
| sheetTemplate | The link to your template sheet. For example: "https://docs.google.com/spreadsheets/d/1a9bPwHUoSsM7QDjb7C55_pjdGTnBNign/edit" |
| spreadsheetName | The new name for the spreadsheet after the export. For example: "Export for: " & [customer] |
| userEmails | The recipients email addresses. You can have multiple as long as they are comma-separated. "user1@test.com", "user2@test.com" |
| content | For every piece of data you want to include, you need a key and then the content. The script works by finding the key in the template and replacing it with the content you have included. In your Sheets template, you will need to put your key in brackets {key}, but you only need to put the key in quotes in the AppSheet expression. Take a look at this template for an example. For example: LIST( TEXT("Customer Name"), TEXT(LOOKUP([reference_id], "customers", "customer_id", "full_name")), TEXT("Company Name"), TEXT(LOOKUP([reference_id], "customers", "customer_id", "company_name")), TEXT("Address"), TEXT(LOOKUP([reference_id], "customers", "customer_id", "address")) ) |
Step 4: Create your Sheets template. You template will vary depending on the function you need.
exportSheets: Your Sheets template only needs to include the column headers for each of the columns that you pass into the function.
exportToSpecificCells: Your Sheets template needs to include the "keys" in every cell where you want to include the data. The key should be between brackets, e.g., {Customer Name}
Please note, this is merely one example of how to use Apps Script to export to Google Sheets and 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
Sheets Report Template
Sheets Quote Template
