Google Apps Script that merges the values from a Google Spreadsheet into a Google Doc template (does a mail merge) to create a GDoc or PDF. More details can be found at www.andrewroberts.net/2014/10/google-apps-script-create-pdf/. Go to https://tinyurl.com/yd8v2do2 if you would like me to set it up for you.

createPDF.gs

//
// Create a PDF by merging values from a Google spreadsheet into a Google Doc
// ==========================================================================
//
// Demo GSheet & script - http://bit.ly/createPDF
// Demo GDoc template - 1QnWfeGrZ-86zY_Z7gPwbLoEx-m9YreFb7fc9XPWkwDw
//

// Config
// ======

// GDoc Template
// -------------
//
// Replace this with ID of your template document: "https://docs.google.com/document/d/YOUR_GDOC_TEMPLATE_ID_HERE/edit"

// var GDOC_TEMPLATE_ID = ''
var GDOC_TEMPLATE_ID = '1QnWfeGrZ-86zY_Z7gPwbLoEx-m9YreFb7fc9XPWkwDw' // Demo template

// PDF File or GDoc
// ----------------
//
// If set to false the merged file is left as a GDoc

// true or false
var PDF_FILE_CREATE = true 

// New Merged File Name
// --------------------
//
// You can specify a name for the new PDF file here, or leave empty to use the default name
// of the form "Merge - [YYYYMMdd_hhmmss].pdf", e.g. "Merge - 20200431_112132.pdf".
// Alternatively one of the columns can be used to name the new file

// This has priority over NEW_FILE_NAME, set to '' to ignore
var HEADER_TO_USE_FOR_FILE_NAME = ''

// Set to '' to ignore, '.pdf' will be added on
var NEW_FILE_NAME = ''

var NEW_FILE_NAME_DEFAULT = 'Merge' // + timestamp

// Email
// -----
//
// Specify the column header to use for email and whether or not to send emails

// true or false
var EMAIL_SEND = false // true or false

var EMAIL_FIELD_NAME = 'Email'

var EMAIL_SUBJECT = 'The email subject ---- UPDATE ME -----'
var EMAIL_BODY = 'The email body ------ UPDATE ME ---------'

// PDF Folder
// ----------
//
// Specify the ID of the folder that the PDF will be put into

var NEW_FOLDER_ID = ''

// Code
// ====

function onOpen() {
  SpreadsheetApp
    .getUi()
    .createMenu('Mail Merge')
    .addItem('Merge data from active row to create new file', 'mailMerge')
    .addToUi()
} 

/**  
 * Take the fields from the active row in the active sheet
 * and, using a Google Doc template, create a PDF doc with these
 * fields replacing the keys in the template. The keys are identified
 * by being wrapped in curly brackets, e.g. {{Name}}.
 *
 * @return {Object} the completed PDF file
 */

function mailMerge() {

  var copyFile        = null 
  var activeRowIndex  = null 
  var activeRowValues = null
  var headerRow       = null
  var copyBody        = null
  var copyDoc         = null
  var newFile         = null
  var recipient       = null
  var newFileName     = null
  var newFileFolder   = null
  
  var ui              = getUi()
  
  if (!gotGDocTemplate()) {return}
  getSheetData()
  if (isHeaderRow()) {return}  
  replacePlaceholders()  
  locateNewFile()
  setFileName()
  sendEmail()
  displayFinalDialog()
  return
  
  // Private Functions
  // -----------------

  function getUi() {
    var ui = SpreadsheetApp.getUi()
    return {
      UI: ui,
      TITLE: 'Create PDF',
      BUTTONS: ui.ButtonSet.OK
    }
  }

  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()
  }

  function gotGDocTemplate() {
  
    if (GDOC_TEMPLATE_ID !== '') {return true}    
    ui.UI.alert(ui.TITLE, 'GDOC_TEMPLATE_ID needs to be defined in Code.gs.', ui.BUTTONS)
    return false
  }

  function isHeaderRow() {   
  
    if (activeRowIndex <= 1) {
      ui.UI.alert(ui.TITLE, 'Select a row below the header row.', ui.BUTTONS)
      return true
    } else {
      return false
    }
  }

  function replacePlaceholders() {
  
    for (var columnIndex = 0; columnIndex < headerRow[0].length; columnIndex++) {    
    
      var nextHeader = headerRow[0][columnIndex]
      
      // Replace any non-alphanumeric values in the header and make it case-insenstive
      var nextPlaceholder = '(?i){{' + nextHeader.replace(/[^a-z0-9\s]/gi, ".") + '}}'
      
      var nextValue = activeRowValues[0][columnIndex]        
      
      if (EMAIL_SEND && nextHeader.toLowerCase() === EMAIL_FIELD_NAME.toLowerCase()) {
        recipient = nextValue
      }  
      
      if (HEADER_TO_USE_FOR_FILE_NAME !== '' && 
          nextHeader.toLowerCase() === HEADER_TO_USE_FOR_FILE_NAME.toLowerCase()) {
        newFileName = nextValue
      }
      
      copyBody.replaceText(nextPlaceholder, nextValue)                         
    }  
    
    copyDoc.saveAndClose()
  }

  function locateNewFile() {
  
    var copyFileParentFolder = copyFile.getParents().next() // Assume just one parent  
    
    if (NEW_FOLDER_ID !== '') {
      newFileFolder = DriveApp.getFolderById(NEW_FOLDER_ID)
    } else {
      newFileFolder = copyFileParentFolder
    }
          
    if (PDF_FILE_CREATE) {
    
      var blob = copyFile.getAs('application/pdf')
      newFile = newFileFolder.createFile(blob)         
      copyFile.setTrashed(true)
      
    } else {
    
      newFile = copyFile        
    }
    
    if (NEW_FOLDER_ID !== '') {
      
      // make an orphan
      copyFileParentFolder.removeFile(newFile) 
      
      // then add to new folder, so never in two places
      newFileFolder.addFile(newFile)
      
    } else {
      // The file is already in the right place
    }
    
  } // mailMerge.locateNewFile()

  function setFileName() {  
  
    if (HEADER_TO_USE_FOR_FILE_NAME) {
      if (newFileName === null) {
        throw new Error('Could not find header "' + HEADER_TO_USE_FOR_FILE_NAME + '" for file name')
      }
    } else {
      if (NEW_FILE_NAME !== '') {
        newFileName = NEW_FILE_NAME
      } else {
        var timeZone = Session.getScriptTimeZone()    
        newFileName = NEW_FILE_NAME_DEFAULT + ' - ' + Utilities.formatDate(new Date(), timeZone, 'YYYYMMdd_hhmmss')
      }
    }
    
    newFileName = newFileName + (PDF_FILE_CREATE ? '.pdf' : '')
    newFile.setName(newFileName)
  }

  function sendEmail() {
  
    if (recipient === null) {return}
    
    MailApp.sendEmail(
      recipient, 
      EMAIL_SUBJECT, 
      EMAIL_BODY,
      {attachments: [newFile]})
      
    ui.UI.alert(ui.TITLE, 'New file emailed to ' + recipient + '.', ui.BUTTONS)
  }
  
  function displayFinalDialog() {  
  
    var message = 'the same folder as this GSheet'
    
    if (NEW_FOLDER_ID !== '') {
      message = '"' + newFileFolder.getName() + '" folder'
    }
    
    var userInterface = HtmlService
      .createHtmlOutput(
        '<a href="' + newFile.getUrl() + '" target="_blank">' + 
          'New file "' + newFileName + '" created in ' + message + '.</a>')
      .setWidth(300)
      .setHeight(80)      
      
    ui.UI.showModalDialog(userInterface, ui.TITLE)
  }
} 

This doesn't seem to actually pick up the variables in the template.

Hey Andrew,
Thanks for this. It was very useful.

Just one point, the body.replaceText() function works on regex, and there were several ? chars on my form and they don't play nicely with regex.

I added another function

// Some of the string end in question marks, and this
// messes up the regex. So we perform some simple operations
// to make it regex safe
// Ex: 'How are you?' becomes 'How are you\?', which now gets
// caught by regex as expected.
function regexSafe(s) {
  return s.replace('\?', '\\\?')
}

Then replace the

copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', 
                         activeRow[0][columnIndex])

with

copyBody.replaceText('%' + regexSafe(headerRow[0][columnIndex]) + '%', 
                         activeRow[0][columnIndex])

and it all works perfectly.

Thanks again.

Thanks for the adjusted script.
However, I noticed that in the created pdf several field values are wrongly formatted.

Example Spreadsheet:
in the spreadsheet there's a cell formatted as Financial (i.e. 34,50), a cell as date (i.e. 24-10-17) and as a day (i.e. Friday).

The created pdf has the values suddenly weirdly formatted:
34,50 became 34,5
2-2-18 became Fri Feb 02 2018 09:00:00 GMT+0100 (CET)
Friday became Fri Feb 02 2018 09:00:00 GMT+0100 (CET)

Any idea how to make sure the right cell formatting (like column.setNumberFormat("dddd");) is maintained when the value is copied to the pdf?

@AlbertTCB
activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getDisplayValues()

This fixed that issue for me

I am not getting where to click and how to generate pdf can someone please show the step in image.

If i want to protect this pdf file by any password. What can i use for that?

This is great... works awesome and will save me so much time making contracts now. Thank you so much for creating and sharing this script! I just have to remember to click on row 2 to start...

I have 2 questions that would make it even better for me though:

1. How can this be set up to create the PDF with the File Name of the template (like it currently does) and a company name from the spreadsheet ('ClientName' is the column header)? And would this also prevent it from having a 'Copy of' at the front like it currently does?
   ie - File Name - Company
2. How can you change where the file is saved? I'd like it to save to somewhere other than the main My Drive folder.
   ie - My Drive / Business / Administration / Client Files

I tried to figure it on my own, but all I did was break the whole thing...

Hi Tamarissa,

Setting the file name

Where the headers are being processed, in the loop between 59 and 63:

if (headerRow[0][columnIndex] === 'ClientName') { var clientName = activeRow[0][columnIndex] }

And anywhere after activeSheet is defined:

var sheetName = activeSheet.getParent().getName()

Then to set the name replace 71 to 74 with

newFile.setName(sheetName + ' - ' + clientName)

Setting location

// Assuming there is only one folder called "Client Files" that you have access to
DriveApp.getFoldersByName("Client Files").next().addFile(newFile)

If you share your script with me (andrew@roberts.net) I can check it over for you.

@AlbertTCB
activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getDisplayValues()

This fixed that issue for me

Fantastic tip!

Wish I'd remembered it earlier - I've just spent ages creating a formatString() function!

Thanks, @AlbertTCB, so much for your effort & for sharing the script. It's a great script and works like a charm. I've just one more question.
How can I have an option to select multiple rows simultaneously to create a PDF for them instead of choosing just one row?

@shekoweb Take a look at https://www.andrewroberts.net/2017/08/pdf-creator-send-multiple-pdfs-based-selected-rows/

Thank you so much @andrewroberts for this. It works great at my end - just a few hiccups.

My Google sheets generates PDF successfully, then i shared the Google sheets as an Editor with someone with a Google account and they granted Sheets & scripts all account access.

When they click on "create PDF" they get the following error:
Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it

How do people that have editor access to the sheets be able to create PDF ?

I would guess that it is more likely that they don't have view access to the GDoc template used to create the PDF.

…

On Mon, 25 Apr 2022 at 14:22, Victor Nwachukwu ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Thank you so much @andrewroberts <https://github.com/andrewroberts> for this. It works great at my end - just a few hiccups. My Google sheets generates PDF successfully, then i shared the Google sheets *as an Editor* with someone with a Google account and they granted Sheets & scripts all account access. When they click on "create PDF" they get the following error: Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it How do people that have editor access to the sheets be able to *create PDF* ? — Reply to this email directly, view it on GitHub <https://gist.github.com/21bc8b1b3fc7d3b40e6b#gistcomment-4144341>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABD4XXJT4Q2BRH5DEMC2IGTVG2MBLANCNFSM4HPVPU2Q> . You are receiving this because you were mentioned.Message ID: ***@***.***>

@andrewroberts it worked! I gave them editor access to the template doc and pdf folders. God bless you🙌🏾