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