How do I extract specific columns from google sheets to google doc using Google Script?

Question

I have a Google Doc with dynamics fields tag like %Name% - %Date of Birth% that take the values from a google sheets in column A and B:

Screenshot of the sheet

As you can see the values inside in the firsts 2 columns is the same and so there is no problem for my "%" tags in the google doc because it takes the same value.
The problem is for the others columns (C,D,E...) where the values on the google sheet are variables.

How can apply in the Google doc a tag that dynamically take the value of these entire columns with the variable values?

Result expected:

In addition, how can I set in the script the columns with fixed values eg. (in yellow) and the columns with variables values eg. (in blue)?

And, in any case, the script not works if I filter the spreadsheet, why?

Answer 1

This is my understanding:

• You want to retrieve the values from Spreadsheet and put to the template Document.
• In your Spreadsheet, all values of columns of "A" and "B" are the same for all rows like john and 25/05/1998.
• You want to create one Google Document by replacing the placeholder by the values from Spreadsheet.
  • The placeholder is enclosed by %.
• You want to achieve this using Google Apps Script.

Flow:

The flow of this sample script is as follows.

1. Retrieve values from Spreadsheet.
2. Create an object for putting to Google Document.
3. Copy a template Google Document.
4. Put the header values to the copied Document using the object.
   • In this case, the placeholder is replaced by the retrieved values.
5. Put the table values using the object.
   • In this case, the retrieved values are directly put to the table in the template Document.

Sample script:

Before you run the script, please set templateGoogleDocumentID.

function myFunction() {
  var templateGoogleDocumentID = "###";  // Please set the template Google Document ID.

  // 1. Retrieve values from Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var values = activeSheet.getDataRange().getValues();
  
  // 2. Create an object for putting to Google Document.
  var object = {headers: {}, table: {}};
  var headerRow = values.shift();
  object.headers[headerRow[0]] = values[0][0];
  object.headers[headerRow[1]] = Utilities.formatDate(values[0][1], Session.getScriptTimeZone(), "yyyy/MM/dd");
  object.table = values.map(r => r.splice(2, 5));
  
  // 3. Copy a template Google Document.
  var copiedTemplateDoc = DriveApp.getFileById(templateGoogleDocumentID).makeCopy();
  var docId = copiedTemplateDoc.getId();
  
  // 4. Put the header values to the copied Document using the object.
  var doc = DocumentApp.openById(docId);
  var body = doc.getBody();
  Object.keys(object.headers).forEach(h => body.replaceText(`%${h.toLowerCase()}%`, object.headers[h]));
  
  // 5. Put the table values using the object.
  // If the table rows of Google Document are less than that of Spreadsheet, the rows are added.
  var table = body.getTables()[0];
  var r = object.table.length - table.getNumRows();
  if (r > 0) {
    for (var i = 0; i < r; i++) {
      var tr = table.appendTableRow();
      for (var j = 0; j < 3; j++) {
        tr.appendTableCell();
      }
    }
  }
  object.table.forEach((row, i) => (row.forEach((col, j) => (table.getCell(i, j).setText(col)))));
  doc.saveAndClose();
  
  // If you want to export the Google Document as PDF file, please use the following script.
  // var newFile = DriveApp.createFile(doc.getBlob());
}

Note:

• In this modified script, please enable V8 at the script editor.

References:

• getCell()
• setText()