Skip to content

Instantly share code, notes, and snippets.

@andrewroberts
Last active September 21, 2023 06:56
Show Gist options
  • Save andrewroberts/db1169336df06b047dcad420076f839a to your computer and use it in GitHub Desktop.
Save andrewroberts/db1169336df06b047dcad420076f839a to your computer and use it in GitHub Desktop.
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 = '---- UPDATE ME -----'
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() {
var triggerId = PropertiesService.getScriptProperties().getProperty('TRIGGER_ID')
var menu = SpreadsheetApp
.getUi()
.createMenu('Create PDF')
.addItem('Create PDF (select row first)', 'createPdf')
.addSeparator()
if (triggerId) {
menu.addItem('Disable "Create PDF" on form subimssion', 'deleteFormSubmissionTrigger')
} else {
menu.addItem('Enable "Create PDF" on form subimssion', 'createFormSubmissionTrigger')
}
menu.addToUi()
}
function createFormSubmissionTrigger() {
var spreadsheet = SpreadsheetApp.getActive()
var triggerId = ScriptApp
.newTrigger('createPdf')
.forSpreadsheet(spreadsheet)
.onFormSubmit()
.create()
.getUniqueId()
PropertiesService.getScriptProperties().setProperty('TRIGGER_ID', triggerId)
onOpen()
}
function deleteFormSubmissionTrigger() {
var spreadsheet = SpreadsheetApp.getActive()
var properties = PropertiesService.getScriptProperties()
var triggerId = properties.getProperty('TRIGGER_ID')
ScriptApp.getUserTriggers(spreadsheet).forEach(function(trigger) {
if (trigger.getUniqueId() === triggerId) {
ScriptApp.deleteTrigger(trigger)
properties.deleteProperty('TRIGGER_ID')
}
})
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 range
if (!inTrigger()) {
activeSheet = SpreadsheetApp.getActiveSheet()
if (activeSheet === null) {
throw new Error('Select a cell in the row that you want to use')
}
range = activeSheet.getActiveRange()
if (range === null) {
throw new Error('Select a cell in the row that you want to use')
}
} else {
// There is an event arg so function called from "on form submit" trigger
range = event.range
activeSheet = range.getSheet()
}
var 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]})
}
if (!inTrigger()) {
SpreadsheetApp
.getUi()
.alert(
'New PDF file created in the root of your Google Drive ' +
'and emailed to ' + recipient)
}
// Private Functions
// -----------------
function inTrigger() {
// No event arg so must have been called from custom menu
return event !== undefined
}
} // createPdf()
@andrewroberts
Copy link
Author

Hey that was very helpful. I get the error: "TypeError: Cannot read property 'range' of undefined".

Does anyone know how to fix this?

Thanks!

There was a bug in inTrigger(), should be OK now.

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