Skip to content

Instantly share code, notes, and snippets.

@andrewroberts
Last active August 31, 2023 06:38
Show Gist options
  • Star 15 You must be signed in to star a gist
  • Fork 10 You must be signed in to fork a gist
  • Save andrewroberts/e5787a99a63a415bae54025c6be730a0 to your computer and use it in GitHub Desktop.
Save andrewroberts/e5787a99a63a415bae54025c6be730a0 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()
// 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()
@niketa
Copy link

niketa commented Jan 26, 2023

Hi!
How to modify it if I have a column 'Image' with Image (in cell) ?

@andrewroberts
Copy link
Author

andrewroberts commented Jan 26, 2023 via email

@gcor71
Copy link

gcor71 commented Jan 31, 2023

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?

@andrewroberts
Copy link
Author

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?

@gcor71
Copy link

gcor71 commented Feb 1, 2023

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

@gcor71
Copy link

gcor71 commented Feb 2, 2023

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.

@kunalagra
Copy link

kunalagra commented Apr 1, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment