Generate different PDFs with mailmerge depending on cell value - Google Sheets

Question

I'm running an Apps Script in Google Sheets to Mail merge and generate a PDF file which is then store in Drive.

I have the following variable which stores the Google Doc that serves as template for the new PDF being generated.

var GDOC_TEMPLATE_ID = '1olnBowCh-1apK3jWN-Lr2Ty3UT1NK_dmoJOVBjnzgO8'

I would like to have a function that modifies the value of this variable so that it can point to a different file.

What I have been trying to do is the following script but haven't accomplished at all:

• If "selected row's column N cell is empty, use this file, if cell is not empty, change to a different file ID.

Note: My mail merge script runs by selecting row and gets all the place holders from the selected row, so when one of those fields is empty, I'm trying to point to a different template that suits better.

This is the script being used, it is public in GitHub and has instructions for each part of the process

Thanks

Answer 1

I believe your goal is as follows.

• You are using a script of https://gist.github.com/lcenchew/5bb4f7f23a159d66dc9a14f38d222fcf.
• In your script, first, it selects a cell. And, run the script. By this, the row of the selected cell is used as the value.
• Under this condition, when the value of column "N" is empty, you want to use the file ID of 1olnBowCh-1apK3jWN-Lr2Ty3UT1NK_dmoJOVBjnzgO8 as GDOC_TEMPLATE_ID. When the value of column "N" is not empty, you want to use the other file ID as GDOC_TEMPLATE_ID.

In this case, how about the following modification? Please modify getSheetData() as follows.

From:

function getSheetData() {
  
  copyFile = DriveApp.getFileById(GDOC_TEMPLATE_ID).makeCopy()
  var copyId = copyFile.getId()
  copyDoc = DocumentApp.openById(copyId)
  copyBody = copyDoc.getActiveSection()
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var numberOfColumns = activeSheet.getLastColumn()
  activeRowIndex = activeSheet.getActiveRange().getRowIndex()
  var activeRowRange = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns)
  activeRowValues = activeRowRange.getDisplayValues()
  headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues()
}

To:

Please replace ### other ID ### with your file ID.

function getSheetData() {
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var numberOfColumns = activeSheet.getLastColumn()
  activeRowIndex = activeSheet.getActiveRange().getRowIndex()
  var activeRowRange = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns)
  activeRowValues = activeRowRange.getDisplayValues()
  headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues()

  var templateIds = ['1olnBowCh-1apK3jWN-Lr2Ty3UT1NK_dmoJOVBjnzgO8', '### other ID ###'];
  GDOC_TEMPLATE_ID = templateIds[activeRowValues[0][13] ? 1 : 0];

  copyFile = DriveApp.getFileById(GDOC_TEMPLATE_ID).makeCopy()
  var copyId = copyFile.getId()
  copyDoc = DocumentApp.openById(copyId)
  copyBody = copyDoc.getActiveSection()
}

• By this modification, when the value of column "N" of the row of the selected cell is empty, 1olnBowCh-1apK3jWN-Lr2Ty3UT1NK_dmoJOVBjnzgO8 is used as GDOC_TEMPLATE_ID. When the value of column "N" is not empty, the other file ID ### other ID ### is used as GDOC_TEMPLATE_ID.