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. It is based on but allows placeholders to be used in the filename.
// 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. Placeholders can also be placed in here.
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] + '%',
PDF_FILE_NAME.replace('%' + 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()
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.