Email a PDF of a Google Form submission.

EmailPDFOfFormSubmission.gs

/*

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()

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

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

Does anyone know how to fix this?

Thanks!

Are our running it from the form submission trigger or the menu?

From the menu

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.