Creating a Google Doc with 2 tables in Google Apps Script

Question

Reference to this question see link I would to have the possibility to put 2 tables in my Google Doc from the Spreadsheet. In the code below I can put only 1 table (from col C to col E). My need is to put also a second table from col H to col N.

What I could add to the script?

function generateDoc() {
  var tableColumn = [3,5]; // Table1 header is in column 3C to 5E
  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 doc 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).getDisplayValues()[0];
  var headerRow = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[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).getDisplayValues();
        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).setColumnWidth(0, 215).setColumnWidth(1, 85).setColumnWidth(2, 150);
        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('doc created!!')     
}

Answer 1

My Solution:

function creategoogledoc() {
  var tableColumn = [3,5]; // Table1 header is in column 3C to 5E
  var tableColumn2 = [7,12]; // Table2
  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 google doc 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).getDisplayValues()[0];
  var headerRow = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[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], 6,tableColumn[1]-tableColumn[0]+1).getDisplayValues();
        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).setColumnWidth(0, 255).setColumnWidth(1, 100).setColumnWidth(2, 165);
        copyBody.insertParagraph(childIndex+2, afterText);
      } catch(err) {
        continue;
      }
    } else if (columnIndex < tableColumn2[0] - 1 || columnIndex > tableColumn2[1] - 1) {
      var nextValue = formatString(activeRow[columnIndex]);
      copyBody.replaceText('%' + headerRow[columnIndex] + '%', nextValue);
    }                     
  }
    for (var columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
    if (columnIndex === tableColumn2[0] - 1) { // Check if column corresponds to table data
      try {
        var tableValues = sheet.getRange(2, tableColumn2[0], sheet.getLastRow()-1,tableColumn2[1]-tableColumn2[0]+1).getDisplayValues();
        var placeholder = `%${headerRow[tableColumn2[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).setColumnWidth(0, 135).setColumnWidth(1, 80).setColumnWidth(2, 63).setColumnWidth(3, 95).setColumnWidth(4, 50).setColumnWidth(5, 100).setColumnWidth(6, 155);
        copyBody.insertParagraph(childIndex+2, afterText);
      } catch(err) {
        continue;
      }
    } else if (columnIndex < tableColumn2[0] - 1 || columnIndex > tableColumn2[1] - 1) {
      var nextValue = formatString(activeRow[columnIndex]);
      copyBody.replaceText('%' + headerRow[columnIndex] + '%', nextValue);
    }                     
  }
  copyDoc.saveAndClose();
  SpreadsheetApp.getUi().alert('google doc created!!')     
}

Now, I would to find a way to display the link of the google doc created by the script, in the dialog box.