Creating a Google Doc from a template with tables in Google Apps Script

Question

This is a Google Apps Script to create a Google Doc from a template and the values in a Google Spreadsheet.

It takes the fields from the active row in your Google Spreadsheet (the one with a highlighted cell or row) and using a Google Doc template (identified by TEMPLATE_ID) creates a doc with these fields replacing the placeholders in the template.
The place-holders are identified by having % around them, e.g. %Name%. It is run using the custom “Create Document” menu that is created in the sheet.

Now, my question is, if I have also a table, with the normal placeholders, (see the image or the link) how could I proceed if I put also the placeholder %Table 1% in the doc template?

In this case, the placeholders %Name%, %Surname%, %Fruit% have to take the values only from the second row. The placeholder %Table 1% have to take the values until the end of the table (row 5).

https://docs.google.com/spreadsheets/d/1l3bCuyRnz_ylMHMfhbRC-_L5bLkWwEkrzGKgy46Kvnk/edit?usp=sharing

function myfunction() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var TEMPLATE_ID = 'xxx';
    //var TEMPLATE_ID = ss.getRange("TEMPLATEID").getValue();
     var ui = SpreadsheetApp.getUi();
      if (TEMPLATE_ID === '') {
        
        SpreadsheetApp.getUi().alert('TEMPLATE_ID needs to be defined in code.gs')
        return
      }
      
      var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
          copyId = copyFile.getId(),
          copyDoc = DocumentApp.openById(copyId)
           var docFile = DriveApp.getFileById(copyFile.getId()); // Get Document as File
      
          var FILE_NAME = ui.prompt('Insert file name:', ui.ButtonSet.OK);
          FILE_NAME.getSelectedButton() == ui.ButtonSet.OK
          copyDoc.setName(FILE_NAME.getResponseText())
          
      var copyBody = copyDoc.getActiveSection(),
          activeSheet = SpreadsheetApp.getActiveSheet(),
          numberOfColumns = activeSheet.getLastColumn(),
          activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
          activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(),
          headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(),
          columnIndex = 0
        
      for (;columnIndex < headerRow[0].length; columnIndex++) {
          var nextValue = formatString(activeRow[0][columnIndex])
          copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', nextValue)                         
        }
      
      copyDoc.saveAndClose()
      
      SpreadsheetApp.getUi().alert('File doc created!!')
      
    }

Answer 1

Solution:

• Define which columns correspond to table data. In the sample below (that's done via tableColumn in the sample below, where the first value corresponds to the first table column, and the second value corresponds to the last column).
• When iterating through the sheet columns, check whether the column corresponds to table values. If it doesn't, just make the same actions as in your original code. If it does:
• Get all values related to those columns (tableValues).
• Use findText(searchPattern) to get the element where `%Table 1% placeholder exists.
• Use getChildIndex to get the index of the element where this placeholder exists, and to remove that element's text (the placeholder).
• Use insertTable(childIndex, cells) to insert the table at the specified index, using the source values.

Code sample:

function myfunction() {
  var tableColumn = [3,5]; // Table header is in column C to E
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var TEMPLATE_ID = 'xxx';
  var ui = SpreadsheetApp.getUi();
  if (TEMPLATE_ID === '') {        
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }
  var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy();
  var copyId = copyFile.getId();
  var copyDoc = DocumentApp.openById(copyId);
  var FILE_NAME = ui.prompt('Insert file name:', ui.ButtonSet.OK);
  copyDoc.setName(FILE_NAME.getResponseText());
  var copyBody = copyDoc.getBody();
  var lastColumn = sheet.getLastColumn();
  var activeRowIndex = sheet.getActiveRange().getRowIndex();
  var activeRow = sheet.getRange(activeRowIndex, 1, 1, lastColumn).getValues()[0];
  var headerRow = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  for (var columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
    if (columnIndex === tableColumn[0] - 1) { // Check if column corresponds to table data
      try {
        var tableValues = sheet.getRange(2, tableColumn[0], sheet.getLastRow()-1,tableColumn[1]-tableColumn[0]+1).getValues();
        var placeholder = `%${headerRow[tableColumn[0]-1]}%`;
        var rangeElement = copyBody.findText(placeholder);
        var element = rangeElement.getElement();
        console.log(element.asText().getText())
        var childIndex = copyBody.getChildIndex(element.getParent());
        console.log(childIndex)
        const elementText = copyBody.getChild(childIndex).asText().getText();
        const [beforeText, afterText] = elementText.split(placeholder);
        copyBody.getChild(childIndex).asText().setText('');
        copyBody.insertParagraph(childIndex, beforeText);
        copyBody.insertTable(childIndex+1, tableValues);
        copyBody.insertParagraph(childIndex+2, afterText);
      } catch(err) {
        continue;
      }
    } else if (columnIndex < tableColumn[0] - 1 || columnIndex > tableColumn[1] - 1) {
      var nextValue = formatString(activeRow[columnIndex]);
      copyBody.replaceText('%' + headerRow[columnIndex] + '%', nextValue);
    }                     
  }
  copyDoc.saveAndClose();
  SpreadsheetApp.getUi().alert('File doc created!!')     
}