Google Docs Invoice template with dynamically items row from Google Sheets

Question

I really need your help with this.

I have created an invoice template in Google Docs with databases flowed from Google sheets. The problem is:

• In the template (Google Docs), I only put a specific items line (eg 3 lines).
• When the data is changed, such as the number of items lines are changing, how it's automatically gone through Google Docs if there are more than 3 items lines

Many thanks for your help.

Below is my script to get data from G-sheets to G-Docs template.

    function Invoice() {
  
  let copyFile = DriveApp.getFileById('id URL').makeCopy(),
      copyID = copyFile.getId(),
      copyDoc = DocumentApp.openById(copyID),
      copyBody = copyDoc.getBody()
  
  let activeSheet = SpreadsheetApp.getActiveSheet(),
      numOfCol = activeSheet.getLastColumn(),
      activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
      activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numOfCol).getValues(),
      headerRow = activeSheet.getRange(1, 1, 1, numOfCol).getValues(),
      columnIndex = 0
  
  for (; columnIndex < headerRow[0].length; columnIndex++){
    
    copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', activeRow[0][columnIndex])
  }
  
  copyDoc.saveAndClose()
  

Here is screenshot of the files. Data in G-sheet with the additional item (Item 4)

G-Docs template with specific 3 rows for 3 items lines

When I have 4 items, I must manually amend the G-Docs template. Is there any way to get its automatically.

Answer 1

@Duc I don't think it's possible to pass the new header as placeholder in the GDoc, it sounds like an endless loop.

Unless you pass it as List_ITEM, but I am pretty sure you will lose formatting.