Skip to content

Instantly share code, notes, and snippets.

@andrewroberts
Last active November 15, 2020 19:43
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save andrewroberts/ce180b2ca46698b7150ecd92b1f49b68 to your computer and use it in GitHub Desktop.
Google Apps Script for creating and emailing multiple PDFs using data from a Google Sheet and a Google doc template.
/*
PDF Creator - Send multiple PDFs
================================
On selecting a number of contiguous rows and clicking "Create PDFs > Create PDFs
for selected rows" this script constructs a PDF for each selected 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.
The keys are identified by having a << and >> either side, e.g. <<Name>>.
Placeholders in both the body and the header of the template are replaced.
The date/time any emails are sent is stored in the SENT_COLUMN_NAME column.
The folder to store the PDFs can be specified in RESULTS_FOLDER_ID.
Demo sheet with script attached: goo.gl/8aJMjG
*/
// JSHint.com - 20170828 15:37
/**
* Eventhandler for spreadsheet opening - add a menu.
*/
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu('Create PDFs')
.addItem('Create PDFs for selected rows', 'sendMultiplePdfs')
.addToUi();
} // onOpen()
// Config
// ------
// Create a GDoc template and put the ID here - THIS HAS TO BE COMPLETED
//var TEMPLATE_ID = '';
var TEMPLATE_ID = '1kp0AkE90Pxp-dS1xOsI3PcKM9SanrQ1xGS5wlTISv_k'; // Demo template
// Naming the PDF:
//
// - 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 (the FILE_NAME_COLUMN_NAME column)
var PDF_FILE_NAME = '';
// If an recipient email address (EMAIL_COLUMN_NAME) is specified the
// script emails the PDF
var EMAIL_SUBJECT = '---- UPDATE ME -----';
var EMAIL_BODY = '------ UPDATE ME ---------';
// The ID of the folder the PDFs are to be stored in. If left blank they
// are stored in the root of the GDrive
var RESULTS_FOLDER_ID = '';
// The name of the column in which to store the date & time the PDF was emailed
var SENT_COLUMN_NAME = 'Date Sent';
// If there is a value in the column with this name it will be used to name the PDF
var FILE_NAME_COLUMN_NAME = 'File Name';
// If there is a value in the column with this name it will be used to email the PDF
var EMAIL_COLUMN_NAME = 'Email';
// The format used for any dates
var DATE_FORMAT = 'yyyy/MM/dd';
// The format used for any date and time
var DATE_TIME_FORMAT = 'yyyy/MM/dd HH:mm:ss';
// Functions
// ---------
function sendMultiplePdfs() {
var ui = SpreadsheetApp.getUi();
if (TEMPLATE_ID === '') {
throw new Error('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 activeRows = activeSheet.getActiveRange().getValues()
if (activeRows.length === 0) {
throw new Error('No rows are selected');
}
var headerRow = activeSheet.getRange(1, 1, 1, activeSheet.getLastColumn()).getValues()[0];
var numberOfColumns = headerRow.length;
// Create a PDF for each row
var rowNumber = activeSheet.getActiveRange().getRow();
if (rowNumber > activeSheet.getLastRow()) {
throw new Error('The selected row is empty');
}
var sentColumnNumber = getColumnNumber({
columnName: SENT_COLUMN_NAME,
headerRow: headerRow,
sheet: activeSheet,
});
var fileCount = 0;
var emailCount = 0;
activeRows.forEach(function(activeRow) {
createPdf();
rowNumber++;
return;
// Private Function
// ----------------
function createPdf() {
var copyFile = templateFile.makeCopy();
var copyDoc = DocumentApp.openById(copyFile.getId());
var copyBody = copyDoc.getActiveSection();
var copyHeader = copyDoc.getHeader();
var fileName = null;
var recipient = null;
// Replace the keys with the spreadsheet values
for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
var headerValue = headerRow[columnIndex];
var activeCell = activeRow[columnIndex];
activeCell = formatCell(activeCell);
if (headerValue === FILE_NAME_COLUMN_NAME) {
fileName = activeCell;
} else if (headerValue === EMAIL_COLUMN_NAME) {
recipient = activeCell || null;
} else {
copyBody.replaceText('<<' + headerValue + '>>', activeCell);
copyHeader.replaceText('<<' + headerValue + '>>', activeCell);
}
} // for each column
// Create the PDF file
copyDoc.saveAndClose();
var pdfFile = DriveApp.createFile(copyFile.getAs('application/pdf'));
copyFile.setTrashed(true);
if (PDF_FILE_NAME !== '') {
pdfFile.setName(PDF_FILE_NAME);
} else if (fileName !== null) {
pdfFile.setName(fileName);
}
// Put the new PDF file into the results folder
if (RESULTS_FOLDER_ID !== '') {
DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(pdfFile);
DriveApp.removeFile(pdfFile);
}
fileCount++;
// Email the new PDF
if (recipient !== null) {
emailPdf();
}
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);
}
return newValue;
} // sendMultiplePdfs.createPdf.formatCell()
function emailPdf() {
if (sentColumnNumber !== null) {
var sentValue = activeSheet.getRange(rowNumber, sentColumnNumber).getValue();
if (sentValue instanceof Date) {
var response = ui.alert(
'There is already a date in row number ' + rowNumber + ' do you wish to send another PDF?',
ui.ButtonSet.YES_NO);
if (response !== ui.Button.YES) {
return;
}
}
}
MailApp.sendEmail(
recipient,
EMAIL_SUBJECT,
EMAIL_BODY,
{attachments: [pdfFile]});
emailCount++;
if (sentColumnNumber !== null) {
var timeZone = Session.getScriptTimeZone();
var sentDate = Utilities.formatDate(new Date(), timeZone, DATE_TIME_FORMAT);
activeSheet.getRange(rowNumber, sentColumnNumber).setValue(sentDate);
}
} // sendMultiplePdfs.createPdf.emailPdf()
} // sendMultiplePdfs.createPdf()
});
ui.alert(fileCount + ' new PDF files created, ' + emailCount + ' emailed out.');
return;
/**
* Find the column number of the field with this name.
*
* @param {Object} config
* @param {String} columnName
* @param {Sheet} sheet [OPTIONAL, null if no headerRow]
* @param {Array} headerRow [OPTIONAL, DEFAULT = 1]
* @param {Number} rowNumber The row to find the header row[OPTIONAL, DEFAULT = 1]
*
* @return {Number} column 1-based index or null
*/
function getColumnNumber(config) {
var rowNumber;
var headerRow;
var sheet;
var columnName;
if (!config.hasOwnProperty('columnName') || config.columnName === '') {
throw new TypeError('No column name');
}
columnName = config.columnName;
sheet = config.sheet;
if (!config.hasOwnProperty('headerRow')) {
if (!config.hasOwnProperty('sheet')) {
throw new Error('Need sheet if no header row');
}
rowNumber = (config.hasOwnProperty('rowNumber')) ? config.rowNumber : 1;
headerRow = sheet
.getRange(rowNumber, 1, 1, sheet.getLastColumn())
.getValues()[0];
} else {
headerRow = config.headerRow;
}
for (var columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
if (headerRow[columnIndex] === columnName) {
return columnIndex + 1;
}
}
return null;
} // sendMultiplePdfs.getColumnNumber()
} // sendMultiplePdfs()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment