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

  // Create a PDF for each row

  allRows.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 headerValue
      var activeCell
      var ID = null
      var recipient = null
      var copyFile
      var numberOfColumns = headerRow.length
      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
     
      for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
        
        headerValue = headerRow[columnIndex]
        activeCell = activeRow[columnIndex]
        activeCell = formatCell(activeCell);
                
        copyBody.replaceText('<<' + headerValue + '>>', activeCell)
        
        if (headerValue === FILE_NAME_COLUMN_NAME) {
        
          ID = activeCell
          
        } else if (headerValue === EMAIL_COLUMN_NAME) {
        
          recipient = 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()

Hi, I get "Exception: The document is inaccessible. Please try again later." at line 113: var copyDoc = DocumentApp.openById(copyId) I can't work out why (tried different share settings on the template file, tried it in different folders including my own home drive etc). The copy (line 111) and the getID (line 112) work correctly as far as I can see - both in the debugger and in Drive where the copied file is created. Feeling I'm missing something obvious but can't see what. Any suggestions, please?

Is the template file a Google Doc, rather than, say, a Google sheet?

Yes, it's a Google Doc in the same Drive as the Sheet.
The COPY seems to work with a new ID being retrieved, it's having a problem accessing the new file, I think.

This is the Execution Log:

9:09:42 PM	Notice	Execution started
9:09:48 PM	Info	invoice-census-template.docx
9:09:58 PM	Info	1Q0pdRzaedHjutcy_LyHmpefugjE3RJwT
9:10:08 PM	Error	
Exception: The document is inaccessible. Please try again later.
createPdf	@ Code.gs:115
(anonymous)	@ Code.gs:90
createPdfs	@ Code.gs:88

---

This is the relevant part of the .gs

111      var copyFile = templateFile.makeCopy() 
112      Logger.log(copyFile)     
113      var copyId = copyFile.getId()
114      Logger.log(copyId);
115      var doc = DocumentApp.openById(copyFile.getId())

Hi - I realised (belatedly) that the doc I'd used as the template was saved as DOCX on the Drive. Converted it to Google Doc and the error (obviously!) went away. Thanks for your example, it's been really instructive.

We can use the destructuring assignment in App Script, so we can do something along: https://gist.github.com/kunalagra/94823c605dad61c170ae21bebcbeb2f4#file-pdfcreator_emailallresponses-L85-L127 to simplify it.