Google Apps Script to create a PDF from each of the rows in a Google Sheet

PDFCreator_EmailAllResponses

/*

PDF Creator - Email all responses
=================================

When you click "Create PDF > Create a PDF for each row" this script 
constructs a PDF for each row in the attached GSheet. 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.

Demo sheet with script attached: https://goo.gl/sf02mK

*/

// Config
// ------


// 1. Create a GDoc template and put the ID here

var TEMPLATE_ID = '---- UPDATE ME -----'

// var TEMPLATE_ID = '11xbBQKz3wDsp1eP9-1pBFeA4vNk8wApjzxj0Kn2RU_c' // Demo template

// 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 ---------'

// 4. If a folder ID is specified here this is where the PDFs will be located

var RESULTS_FOLDER_ID = ''

// Constants
// ---------

// You can pull out specific columns values 
var FILE_NAME_COLUMN_NAME = 'File Name'
var EMAIL_COLUMN_NAME = 'Email'

// The format used for any dates 
var DATE_FORMAT = 'yyyy/MM/dd';

/**
 * Eventhandler for spreadsheet opening - add a menu.
 */

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu('[ Create PDFs ]')
    .addItem('Create a PDF for each row', 'createPdfs')
    .addToUi()

} // onOpen()

/**  
 * Take the fields from each 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%.
 */

function createPdfs() {

  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 templateFile = DriveApp.getFileById(TEMPLATE_ID)
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var allRows = activeSheet.getDataRange().getValues()
  var headerRow = allRows.shift()
  // Converts 2d array into an object array
  // See https://stackoverflow.com/a/22917499/1027723
  // For a pretty version, see https://mashe.hawksey.info/?p=17869/#comment-184945
  const obj = allRows.map(r => (headerRow.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));


  // Create a PDF for each row

  obj.forEach(function(row) {
  
    createPdf(templateFile, headerRow, row)
    
    // Private Function
    // ----------------
  
    /**
     * Create a PDF
     *
     * @param {File} templateFile
     * @param {Array} headerRow
     * @param {Array} activeRow
     */
  
    function createPdf(templateFile, headerRow, activeRow) {
      
      var activeCell
      var ID = row[FILE_NAME_COLUMN_NAME]
      var recipient = row[EMAIL_COLUMN_NAME]
      var copyFile
      var copyFile = templateFile.makeCopy()      
      var copyId = copyFile.getId()
      var copyDoc = DocumentApp.openById(copyId)
      var copyBody = copyDoc.getActiveSection()
           
      // Replace the keys with the spreadsheet values and look for a couple
      // of specific values
      
      headerRow.forEach(function(col){
          activeCell = activeRow[col]
          activeCell = formatCell(activeCell);
          copyBody.replaceText('<<' + col + '>>', activeCell)
      }) 
      
      // Create the PDF file
        
      copyDoc.saveAndClose()
      var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))  
      copyFile.setTrashed(true)
    
      // Rename the new PDF file
    
      if (PDF_FILE_NAME !== '') {
      
        newFile.setName(PDF_FILE_NAME)
        
      } else if (ID !== null){
    
        newFile.setName(ID)
      }
      
      // Put the new PDF file into the results folder
      
      if (RESULTS_FOLDER_ID !== '') {
      
        DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile)
        DriveApp.removeFile(newFile)
      }

      // Email the new PDF

      if (recipient !== null) {
      
        MailApp.sendEmail(
          recipient, 
          EMAIL_SUBJECT, 
          EMAIL_BODY,
          {attachments: [newFile]})
      }
    
    } // createPdfs.createPdf()

  })

  ui.alert('New PDF files created')

  return
  
  // Private Functions
  // -----------------
  
  /**
  * Format the cell's value
  *
  * @param {Object} value
  *
  * @return {Object} value
  */
  
  function formatCell(value) {
    
    var newValue = value;
    
    if (newValue instanceof Date) {
      
      newValue = Utilities.formatDate(
        value, 
        Session.getScriptTimeZone(), 
        DATE_FORMAT);
        
    } else if (typeof value === 'number') {
    
      newValue = Math.round(value * 100) / 100
    }
    
    return newValue;
        
  } // createPdf.formatCell()
  
} // createPdfs()