Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This is a Google Apps Script that creates a PDF file using a Google Doc template and the values in a Google Spreadsheet. More details can be found at www.andrewroberts.net/2014/10/google-apps-script-create-pdf/
// dev: andrewroberts.net
// Replace this with ID of your template document.
var TEMPLATE_ID = ''
// var TEMPLATE_ID = '1wtGEp27HNEVwImeh2as7bRNw-tO4HkwPGcAsTrSNTPc' // Demo template
// Demo script - http://bit.ly/createPDF
// You can specify a name for the new PDF file here, or leave empty to use the
// name of the template.
var PDF_FILE_NAME = ''
/**
* Eventhandler for spreadsheet opening - add a menu.
*/
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu('Create PDF')
.addItem('Create PDF', 'createPdf')
.addToUi()
} // onOpen()
/**
* 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 having a % either side, e.g. %Name%.
*
* @return {Object} the completed PDF file
*/
function createPdf() {
if (TEMPLATE_ID === '') {
SpreadsheetApp.getUi().alert('TEMPLATE_ID needs to be defined in code.gs')
return
}
// Set up the docs and the spreadsheet access
var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
copyId = copyFile.getId(),
copyDoc = DocumentApp.openById(copyId),
copyBody = copyDoc.getActiveSection(),
activeSheet = SpreadsheetApp.getActiveSheet(),
numberOfColumns = activeSheet.getLastColumn(),
activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(),
headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(),
columnIndex = 0
// Replace the keys with the spreadsheet values
for (;columnIndex < headerRow[0].length; columnIndex++) {
copyBody.replaceText('%' + headerRow[0][columnIndex] + '%',
activeRow[0][columnIndex])
}
// Create the PDF file, rename it if required and delete the doc copy
copyDoc.saveAndClose()
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))
if (PDF_FILE_NAME !== '') {
newFile.setName(PDF_FILE_NAME)
}
copyFile.setTrashed(true)
SpreadsheetApp.getUi().alert('New PDF file created in the root of your Google Drive')
} // createPdf()
@EsmeW

This comment has been minimized.

Copy link

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

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

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

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

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

commented May 25, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.