Auto Fill a Google Doc Template from Google Sheet Data with Google Apps Script

Auto Fill a Google Doc Template from Google Sheet Data

function onOpen() {                                         /* this function will run when Google Sheets (Gsh) loads, it is a trigger to allow us to add a menu to the Gsh UI */
  const ui = SpreadsheetApp.getUi();                        /* returns an instance of the Gsh UI that we can use to add a menu to our Gsh UI */
  const menu = ui.createMenu('AutoFill Docs');              /* creates the 'Autofill Docs' menu label */
  menu.addItem('Create new docs','createNewGoogleDocs');    /* creates the 'Create new docs' menu item */
  menu.addToUi();                                           /* adds the menu and the menu label to Gsh UI */
}

function createNewGoogleDocs() {                                                                    /* will loop through the Gsh rows and generate a new Gdoc if the Document Link Column is empty */
const googleDocTemplate = DriveApp.getFileById('1UPn9oGcibQ-17fFSVHncGHpS4-8d-Nk_c_Nj-mhk34o');   /* get the spreadsheet Id */
const destinationFolder = DriveApp.getFolderById('1mXE3imOjM5kZW9GBHuC9QsWwkedzQoLc');              /* get the Gdoc's folder Id */
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');                         /* returns the 'Data' sheet of the Active speadsheet */
const rows = sheet.getDataRange().getValues();                                                      /* return a 2d array which size is the max range (columns x rows) where there is data*/


rows.forEach(function(row, index){                       /* iterates throughh each row of the 'rows' array variable and returns the index of each element of the array */
  if (index === 0) return;                               /* skips the head row (en-tête du tableau)ie element with the 0 index*/                                                                       
  if (row[6]) return;
  const googleDocTemplateCopy = googleDocTemplate.makeCopy(`Persona ${row[0]}, ${row[1]}`, destinationFolder);     /* make a copy of the spreadsheet named Persona xxx xxx in Destination Folder*/
  const openedgoogleDocTemplateCopy = DocumentApp.openById(googleDocTemplateCopy.getId());                         /* open it */
  const openedGoogleDocTemplateCopyBody = openedgoogleDocTemplateCopy.getBody();

  /* pass the 2 arguments the replaceText function needs */
  /* the first is the replacement tag {{ .... }} as a string to the replaceText function */
  /* the second is the data value of the array ie row[0] ... row[6] that must replace the replacement tags inside the Gdoc*/
  /* we do that for each replacement tag */
  openedGoogleDocTemplateCopyBody.replaceText('{{Persona number}}', row[0]);
  openedGoogleDocTemplateCopyBody.replaceText('{{Persona name}}', row[1]);
  openedGoogleDocTemplateCopyBody.replaceText('{{Generic Motivation To Lose Weight}}', row[2]);
  openedGoogleDocTemplateCopyBody.replaceText('{{Reason For That Motivation}}', row[3]);
  openedGoogleDocTemplateCopyBody.replaceText('{{Pain Point That Triggered the Desire To Take Action Urgently}}', row[4]);
  openedGoogleDocTemplateCopyBody.replaceText('{{Problems Detailed}}', row[5]);

openedgoogleDocTemplateCopy.saveAndClose();                      /*make changes permanent*/
const url = openedgoogleDocTemplateCopy.getUrl();                /* get the url of the copy*/
sheet.getRange(index + 1, 7).setValue(url);                       /* set the 'cursor' in the 'Document Link' column and then write the url*/
})

}