Skip to content

Instantly share code, notes, and snippets.

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
// dev:
// Replace this with ID of your template document.
var TEMPLATE_ID = ''
// var TEMPLATE_ID = '1wtGEp27HNEVwImeh2as7bRNw-tO4HkwPGcAsTrSNTPc' // Demo template
// Demo script -
// 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() {
.createMenu('Create PDF')
.addItem('Create PDF', 'createPdf')
} // 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')
// 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] + '%',
// Create the PDF file, rename it if required and delete the doc copy
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))
if (PDF_FILE_NAME !== '') {
SpreadsheetApp.getUi().alert('New PDF file created in the root of your Google Drive')
} // createPdf()

This comment has been minimized.

Copy link

EsmeW commented Aug 13, 2017

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


This comment has been minimized.

Copy link

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] + '%', 


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

and it all works perfectly.

Thanks again.


This comment has been minimized.

Copy link

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?


This comment has been minimized.

Copy link

dascata commented Sep 5, 2018

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

This fixed that issue for me


This comment has been minimized.

Copy link

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.


This comment has been minimized.

Copy link

lalitpatil3296 commented May 25, 2019

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


This comment has been minimized.

Copy link
Owner Author

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 ( I can check it over for you.

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.