Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save kunalagra/94823c605dad61c170ae21bebcbeb2f4 to your computer and use it in GitHub Desktop.
Save kunalagra/94823c605dad61c170ae21bebcbeb2f4 to your computer and use it in GitHub Desktop.
Google Apps Script to create a PDF from each of the rows in a Google Sheet
/*
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment