Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
//
// 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)
}
}
@EsmeW

This comment has been minimized.

Copy link

@EsmeW EsmeW commented Aug 13, 2017

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

@samhattangady

This comment has been minimized.

Copy link

@samhattangady samhattangady commented Oct 30, 2017

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.

@AlbertTCB

This comment has been minimized.

Copy link

@AlbertTCB AlbertTCB commented Feb 5, 2018

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?

@dascata

This comment has been minimized.

Copy link

@dascata dascata commented Sep 5, 2018

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

This fixed that issue for me

@Engineerumair

This comment has been minimized.

Copy link

@Engineerumair Engineerumair commented Sep 8, 2018

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

@lalitpatil3296

This comment has been minimized.

Copy link

@lalitpatil3296 lalitpatil3296 commented May 25, 2019

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

@Tamarissa

This comment has been minimized.

Copy link

@Tamarissa Tamarissa commented Dec 8, 2019

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

@andrewroberts

This comment has been minimized.

Copy link
Owner Author

@andrewroberts andrewroberts commented Dec 9, 2019

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.

@andrewroberts

This comment has been minimized.

Copy link
Owner Author

@andrewroberts andrewroberts commented Apr 12, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment