google-apps-script google-sheets google-docs

Insert a table from a sheet tab to middle of a doc template

I want to add a table from a Google sheet, Tab "LG001 - Table 1" to a replacement tag of "Table 1" in a doc Template.

PS: Thanks so much for your help, I realize there might be some mistake in other steps, thus I attach the whole code here so that you have a better context of what is going on.

This code is in a sheet - Script

Here is what I have been trying:

 function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();

}

function createNewGoogleDocs() {
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('1ljyhJ11v90WQJTIV9nrjC0uq80HYK_U1aY7-dgMtNUk');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('1dsrpxUihbYQQkdKx3uD5v0IDvv_CSUOS')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('HĐMBHH')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[16]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy("LG.001 " +" "+row[15], destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText("{{b Company name}}",row[2]);
    body.replaceText("{{Party B Address}}",row[3]);
    body.replaceText("{{Party B Tel}}",row[4]);
    body.replaceText("{{b Rep}}",row[5]);
    body.replaceText("{{B Position}}",row[6]);
    body.replaceText("{{B Tax Code}}",row[7]);
    body.replaceText("{{dot1}}",row[8]);
    body.replaceText("{{chu1}}",row[9]);
    body.replaceText("{{word1}}",row[10]);
    body.replaceText("{{dot2}}",row[11]);
    body.replaceText("{{chu2}}",row[12]);
    body.replaceText("{{word2}}",row[13]);
    body.replaceText("{{bh}}",row[14]);
    body.replaceText("{{Contract ID}}",row[0]);
  

  function inserttable1(){
  let sheet1 = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("LG001 - Table 1");
  let numRows = sheet1.getLastRow();
  let numColumns = sheet1.getLastColumn();
  let data = sheet1.getRange(1, 1, numRows, numColumns).getValues();
  // the id of the document file
  let placeHolder = body.findText("{{Table 1}}");
  let paragraph = placeHolder.getElement().getParent();
  let index = body.getChildIndex(paragraph);
  body.insertTable(index+1,data);
  body.removeChild(paragraph);}

  //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    const pdf = convertToPdf_(doc, destinationFolder); // Convert the doc to a PDF file.
      const url = pdf.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index + 1, 17).setValue(url)

  function convertToPdf_(doc, folder) {
  const blob = doc.getAs('application/pdf');
  const file = folder.createFile(blob);
  return file
    }})}

Any help would be much appreciated! Thanks in advance

Solution

Here is an example of how to insert a table after your marker. I then delete the marker.

function inserttable1(){
  let sheet1 = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sheet1");
  let numRows = sheet1.getLastRow();
  let numColumns = sheet1.getLastColumn();
  let data = sheet1.getRange(1, 1, numRows, numColumns).getValues();
  // the id of the document file
  let doc = DocumentApp.openById("1......................................");
  let body = doc.getBody();
  let placeHolder = body.findText("{{Table 1}}");
  let paragraph = placeHolder.getElement().getParent();
  let index = body.getChildIndex(paragraph);
  body.insertTable(index+1,data);
  body.removeChild(paragraph);
}

Reference