Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Email a PDF of a Google Form submission.
/*
PDF Create - Email on form submit
=================================
This script creates a PDF populated with the values submitted in a
Google form.
The "on form submit" trigger needs to be manually created:
1. Resources > Current project's triggers
2. Add new trigger
3. "createPDF", "From spreadsheet", "On form submit"
4. Set notifications to immediate, otherwise you'll only recieve
them once a day
The email can also be sent using the custom menu "Create PDF>Create PDF".
In this case the script uses the data from the active row (the one
with a cell selected) to construct a PDF in your GDrive. The value in the
"File Name" column is used to name the file and - if there is a
value - it is emailed to the recipient in the "Email" column.
*/
// Config
// ------
// 1. Create a GDoc template and put the ID here
var TEMPLATE_ID = ''
// var TEMPLATE_ID = '1k4M-Le05szHQcSQGaTfuIjwJSBOrxZu8GVK3T_c9Dgw' // Demo template
// Demo script - goo.gl/EISKn6
// 2. You can specify a name for the new PDF file here, or leave empty to use the
// name of the template or specify the file name in the sheet
var PDF_FILE_NAME = ''
// 3. If an email address is specified you can email the PDF
var EMAIL_SUBJECT = '--- PUT YOUR OWN EMAIL SUBJECT HERE ---'
var EMAIL_BODY = '--- PUT YOUR OWN EMAIL BODY HERE ---'
// Constants
// ---------
// You can pull out specific columns values
var FILE_NAME_COLUMN_NAME = 'File Name'
var EMAIL_COLUMN_NAME = 'Email'
/**
* Eventhandler for spreadsheet opening - add a menu.
*/
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu('Create PDF')
.addItem('Create PDF (select row first)', '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(event) {
if (TEMPLATE_ID === '') {
throw new Error('TEMPLATE_ID needs to be defined in Code.gs')
}
// Set up the docs and the spreadsheet access
var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy()
var copyId = copyFile.getId()
var copyDoc = DocumentApp.openById(copyId)
var copyBody = copyDoc.getActiveSection()
var activeSheet
var activeRowIndex
var range
if (typeof event === 'undefined') {
// No event arg so must have been called from custom menu
activeSheet = SpreadsheetApp.getActiveSheet()
if (activeSheet === null) {
throw new Error('Select a cell in the row that you want to use')
}
range = activeSheet.getActiveRange()
if (activeSheet === null) {
throw new Error('Select a cell in the row that you want to use')
}
activeRowIndex = range.getRowIndex()
} else {
// Event arg so called from on form submit trigger
range = event.range
activeSheet = range.getSheet()
activeRowIndex = range.getRowIndex()
}
var numberOfColumns = activeSheet.getLastColumn()
var activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues()
var headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues()
var columnIndex = 0
var headerValue
var activeCell
var ID = null
var recipient = null
// Replace the keys with the spreadsheet values and look for a couple
// of specific values
for (;columnIndex < headerRow[0].length; columnIndex++) {
headerValue = headerRow[0][columnIndex]
activeCell = activeRow[0][columnIndex]
copyBody.replaceText('%' + headerValue + '%', activeCell)
if (headerValue === FILE_NAME_COLUMN_NAME) {
ID = activeCell
} else if (headerValue === EMAIL_COLUMN_NAME) {
recipient = activeCell
}
}
// Create the PDF file, rename it if required, delete the doc copy
// and email it
copyDoc.saveAndClose()
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))
if (PDF_FILE_NAME !== '') {
newFile.setName(PDF_FILE_NAME)
} else if (ID !== null){
newFile.setName(ID)
}
copyFile.setTrashed(true)
if (recipient !== null) {
MailApp.sendEmail(
recipient,
EMAIL_SUBJECT,
EMAIL_BODY,
{attachments: [newFile]})
}
var ui = SpreadsheetApp.getUi()
if (ui !== null) {
ui.alert('New PDF file created in the root of your Google Drive ' +
'and emailed to ' + recipient)
}
} // createPdf()
@baliki

This comment has been minimized.

Copy link

baliki commented Dec 4, 2017

Many Thanks for this useful script. I just have two questions:

  1. Is it possible to use the column fields in the email body

  2. Is it possible to set a condition for the createPdf() based on a column field

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.