Export to Google Docs from AppSheet

AppSheet Tips & Tricks
21

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)
    }
  })
3 Likes
22

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]);
}
4 Likes
23

Hello @alafontant,

thank you for providing the script, works like a charm - even for beginners with appscript :slightly_smiling_face:

Question: is there a possibility to extend so the script accepts and transform HTML formatted text, or rich text? I have bigger text fields that are formatted (with bullet points). So far the HTML tags are just handed over to the G-Doc as full text without respecting them.

Also any other kind of lists in a text field (as said, e.g. Richtext) would be helpful.

Thx

Guido