Export to Google Docs from AppSheet

[alafontant]

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: "https://docs.google.com/document/d/1E2vI-24w0N2B83dR6Oa1H-QjXhqaiaBG7XXkDM3_wss/edit"
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: LIST( 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

[SkrOYC]

Could you show an example of a list of values? Similar to how Start: templates work.

Thanks

[alafontant]

Do you mean an example of a list of values of variable size in a table, like below? Or something else?

Customer Name	Revenue	Employees
Cymbal Inc.	$1,200,000	115
ABC Corp.	$891,875	74
Acme Org.	$2,550,000	89
...	...	...

[SkrOYC]

Yes, a way to iterate through a list of records

[alafontant]

Haha, I knew someone was going to ask for this, but I got busy! Let me post the Google Slides export first and then I'll come back and add support for that use case.

[ChristianLi]

Hello @alafontant, any update on iterating on list of records?

Would be great to hear your ideas, and happy to help.

[alafontant]

Apologies it took me so long to get back to this! I have updated the post to include support for tables within a document. Let me know if you have any questions.

[Suvrutt_Gurjar]

Thank you very much @alafontant 

I believe with your this sharing of GAS for populating multiple records in the Google Docs template based on a table key, almost all reports having parent record and associated child records can be created outside the traditional approach of using AppSheet templates.

If required an addition of some GAS for converting the Google Docs filled with data can be converted to a PDF.

Added bonus, I believe is with this GAS approach you shared, the much required  and asked for option of having headers and footers and page numbers for the report pages will also be more conveniently possible. This is so because , I believe we can use the default page number and header footer settings of the Google docs itself.

Will test your shared app and GAS more and revert. Just as a quick question if I may ask, in case the data populated table in the Google doc extends one page because of many records to populate, will the table headers repeat in the next G Doc page?

[alafontant]

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.

[Suvrutt_Gurjar]

Thank you @alafontant. Yes, now got it. Sinnce none of the Google Docs native  settings gets disturbed when we use it as a template with GAS approach, the "pin header..." setting will work to repeat the headers.

[draxdeveloper]

Hello, I have an issue with the solution using tables.
The document I am trying to create uses multiple <<start>> and thus would need more than one table.
To be more exact, it's uses two <<Start>> in two difference points with two different table as a source.
It's is possible with the script?

Maybe repeating adding a second tableKey parameter (tableKey2) and repeating the tableKey loop?

  // Add the rows to the table that matches the table key
  var tables = body.getTables();
  tables.forEach(table => {
    if(table.getCell(0,0).getText() == tableKey){
      nr = table.getNumRows()
      for (key in mapContent){
        tr = table.getChild(nr-1).copy();
        table.appendTableRow(tr)
        for (var j=0; j < mapContent[key].length; j++){
          tr.getChild(j).setText(mapContent[key][j]);
        }
      }
      table.removeRow(nr-1)
    }
  })

Or, for a solution for n tables would be possible create a outer loop and a third function for multiple tables. That way the tableKey would be an string array instead of a simple string?

I still need to test, but here what I changed:

function exportDocsWithMultiTable(templateUrl, docTitle, userEmails, tableKeyArray, columns, content) {

  for (var j = 0; j < tableKeyArray.length ; j = j + 1) {

    tableKey = tableKeyArray[j];

    // Paste the content into the Doc at the defined locations
    for (var i=0; i < content.length; i = i + 2){
      
      // find and replace all the image keys with image urls
      if(content[i].toLowerCase().indexOf("image")>-1){
        var imageBlob = UrlFetchApp.fetch(content[i+1]).getBlob()
        location = body.findText(content[i])
        const paragraph = location.getElement().getParent().asParagraph();
        paragraph.clear();
        const img = paragraph.appendInlineImage(imageBlob);
        const newWidth = getWidthOf(paragraph, body.getAttributes()["PAGE_WIDTH"]) * 1.33;
        const newHeight = img.getHeight() * newWidth / img.getWidth();
        img.setWidth(newWidth).setHeight(newHeight);
      } 
      // find and replace all the keys with text content
      else{ 
        body.replaceText('{' + content[i] + '}', content[i+1]);
      }
    }

  }

[nygianan]

 Is it possible to display a ticked check box in the document? 

[Lucas_Parallada]

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

[Quatz]

Hello. Can help me?

My script is returning a error: 

Exception: Invalid argument: replacement at exportDocs(Code:49:12)

Its create the document but not replace the terms on {}. 

[Mika_CCDA]

Same problem here.

I think it's because the LOOKUP() functions (in the function parameter "content" in the process/bot "Export Document")  doesn't have the "[reference_id]". I don't understand how the link is made between "reference_id" (in Actions table) and "customer_id" (in Customers table). Maybe @alafontant could help us ? 

[alafontant]

The [reference_id] is the way that the key to the "customers" table is stored. When the user clicks "Export Sales Lead", the [customer_id] is made into the [reference_id]. Then when the automation is triggered, we use the LOOKUP function to gather the information from the selected row using that [customer_id].

[smd2022]

I have two questions:

Instead of emailing, can I choose a file path in Drive to save the exported docs?

For "content," can I just put name of table?

[KemAve]

Hi thanks very much for this code. However, I need help. I am using the Export to Table function.

However, text columns keep getting a string format error:

There are no issues with the numerical columns and these are all exported correctly.

Just the string(text) values that is part of the table array gets an error and stops the script midway.

Any ideas? Thanks very much.

[KemAve]

After much experimenting (half a day), I think I have found why it returns me a format error.

What seems to be happening:

The script for the Export Table registers the format attribute of the first column, and applies it for the rest of the listed columns, without regard to the actual format type each column has.

In my case, the first column [No.], is a number format. So seems that it applies "number" format for the rest of the columns. And once the scripts moves to the column that is not a "number", a format error occurs, and the script stops midway. 

SOLUTION THAT WORKED FOR MY CASE:
- Convert non-text formatted columns to text with a virtual column using TEXT() function. 

Now everything is exported without error, except for the [Qty] column, where I forgot to format it as text. [Qty] is originally a "decimal" type with 2 significant digits. But at least, the script didn't stop midway, and still exported [Qty] as an "integer".

If formatted as text, [Qty] should presumably export as "1.00", instead of "1".

------------------

I'm not sure if this is a known issue, but it may help in mentioning as a warning at the original post. As I am quite a beginner in all of this, so it took me time to test things out, and I am not sure if the above case/solution will work for others. I'm no programmer so I dont know if I had it correct above.

Once again, thanks for the great code! Hope can develop it further for the formatting issue. 

This beats the export PDF method because the font, position, page margins that we setup in GDoc template is respected by this script. Export PDF method will mess up the margins and text placement sometimes. So this code really saves a lot of hassle. Brings tears of happiness to my eyes.

[KemAve]

I hope the developer can also consider following functionality 😍. 

Script Development Wishlist:

- Parameter for customizable save folder location in GDrive

- Format monitoring of each column's attribute type to minimize the need for text conversion

[draxdeveloper]

I was not able to solve the issue of adding multiple tables elegantly (It's possible, but I don't have the time right not).
But, you can copy the function exportDocsWithTable, add a copy of the parameters related to the table and the table generation loop, as in:

function exportDocsWithTable2(templateUrl, docTitle, userEmails, tableKey, tableKey2, columns, columns2, content)

...

  // Make the content map
  mapContent = Object.keys(columns[0]).map ( function (columnNumber) {
    return columns.map( function (row) {
      return row[columnNumber];
    })
  })

  Logger.log("mapContent = " + mapContent);

  // Make the second content map
  mapContent2 = Object.keys(columns2[0]).map ( function (columnNumber) {
    return columns2.map( function (row) {
      return row[columnNumber];
    })
  })

...

  // Add the rows to the table that matches the table key
  var tables = body.getTables();
  Logger.log("tables = " + tables);
  Logger.log("tableKey = " + tableKey);
  tables.forEach(table => {
    Logger.log("table = " + table);
    if(table.getCell(0,0).getText() == tableKey){
      nr = table.getNumRows()
      Logger.log("nr = " + nr);
      for (key in mapContent){
        tr = table.getChild(nr-1).copy();
        Logger.log("tr = " + tr);
        table.appendTableRow(tr)
        Logger.log("mapContent[key].length = " + mapContent[key].length);
        for (var j=0; j < mapContent[key].length; j++){
          Logger.log("j = " + j);
          tr.getChild(j).setText(mapContent[key][j]);
        }
      }
      table.removeRow(nr-1)
    }
  })

  // Add the rows to the second table that matches the table key
  var tables = body.getTables();
  Logger.log("tables = " + tables);
  Logger.log("tableKey2 = " + tableKey2);
  tables.forEach(table => {
    Logger.log("table = " + table);
    if(table.getCell(0,0).getText() == tableKey2){
      nr = table.getNumRows()
      Logger.log("nr = " + nr);
      for (key in mapContent2){
        tr = table.getChild(nr-1).copy();
        Logger.log("tr = " + tr);
        table.appendTableRow(tr)
        Logger.log("mapContent2[key].length = " + mapContent2[key].length);
        for (var j=0; j < mapContent2[key].length; j++){
          Logger.log("j = " + j);
          tr.getChild(j).setText(mapContent2[key][j]);
        }
      }
      table.removeRow(nr-1)
    }
  })

[draxdeveloper]

Also, there is a issue that if you receive a content that is blank you will can end having this error:

Exception: Invalid regular expression pattern {1}
    at exportDocsWithTable(Code:130:14)

This happens because the array will break the sequence of key/value.
To fix this you need to send a value special value if it's blank and then convert the special value to "". In other words, you trick the code to believe there is something and then erase the text.

You need to do this treatment ONLY if the field can end being null (a non required field).

First, in the appsheet side, you need to change the field that can be null in the content field of the bot.

instead of:

TEXT("LABEL") ,
TEXT("VALUE")  

you have:

TEXT("LABEL")
TEXT(
  IF(
    ISBLANK("VALUE") ,
    "|!@" ,
    "VALUE"
  )
)

you can use anything in the place of "|!@" just make sure it will never be the value of the field.

Next, you need to go to the script and change the following part:

body.replaceText('{' + content[i] + '}', content[i+1]);

to

if(content[i+1] == "|!@") {
 body.replaceText('{' + content[i] + '}', "");
}
else{
 body.replaceText('{' + content[i] + '}', content[i+1]);
}