A Google Apps Script that uses the values from a Google Sheet to construct a PDF from a GDoc template. It allows you to specify a name for the file and email it to someone. This is a demo sheet: https://docs.google.com/spreadsheets/d/1jLpPtmUS8__PceJx9z5iSSaLSfENojWK7hfsH6uHa9Y/edit#gid=0. It is a development of the CreatePDF script (https://gis…

CreatePDFRenameAndEmail.gs

/*

PDF Create - with rename and email
==================================

When you click "Create PDF>Create PDF" this script uses the data from 
the active row 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 = '1wtGEp27HNEVwImeh2as7bRNw-tO4HkwPGcAsTrSNTPc' // Demo template
// Demo script - http://bit.ly/createPDF

// 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 = 'The email subject ---- UPDATE ME -----'
var EMAIL_BODY = 'The email body ------ UPDATE ME ---------'

// 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', '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() {

  var ui = SpreadsheetApp.getUi()

  if (TEMPLATE_ID === '') {
    
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }

  // 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,
      headerValue,
      activeCell,
      ID = null,
      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]})
  }
  
  ui.alert('New PDF file created in the root of your Google Drive ' + 
        'and emailed to ' + recipient)
  
} // createPdf()

hey andrew, thanks for the code, really helpful! I just keep on getting the error "Document is missing (perhaps it was deleted?)" when running the script. the template google sheet is created, but the values unaltered. do you know what causes this?