0

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.

Duc Nguyen
  • 7
  • 3
  • Unfortunately, I cannot understand about your current issue from `The problem is:`. I apologize for this. In order to correctly understand about it, can you provide your current script and sample Google Document and Google Spreadsheet including the input and output situation you expect? – Tanaike Mar 12 '21 at 03:30
  • @Tanaike: I just updated my query. Could you please help have a look again? I want G-Docs template will auto-update when the data get more item. Much appreciated. – Duc Nguyen Mar 12 '21 at 04:25
  • 1
    Thank you for replying and adding more information. From your additional information, I cannot still understand about your goal because I cannot understand about your sample Spreadsheet and sample Document. Your sample images are the sample input situation? If it's so, where can I see the sample output situation you expect? I apologize for my poor understanding. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon. – Tanaike Mar 12 '21 at 04:55

1 Answers1

0

@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.

Cristian M
  • 23
  • 4
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 24 '22 at 01:54