Skip to content

Instantly share code, notes, and snippets.

@sp2hari
Last active May 8, 2020
Embed
What would you like to do?
Mail Merge to PDF Files
/*
DO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
Version 2, December 2004
Copyright (C) 2004 Sam Hocevar <sam@hocevar.net>
Everyone is permitted to copy and distribute verbatim or modified
copies of this license document, and changing it is allowed as long
as the name is changed.
DO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
0. You just DO WHAT THE FUCK YOU WANT TO.
*/
//Common functions.
//Read the data from the config file. Maximum limit is 100 rows. No blank rows allowed in between
function getConfigValue(key) {
var config_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config');
var config_values = config_sheet.getSheetValues(1, 1, 100, 2);
for(var i=0; i<config_values.length; i++) {
if (config_values[i][0] == key) {
return config_values[i][1];
}
}
return null;
}
//Returns an array of header rows. Maximum limit 1000 columns. No blank columns allowed in between
function getHeader(data_sheet) {
var fields_from_sheet = []
var header_row = data_sheet.getRange(1, 1, 1, 1000).getDisplayValues();
for (var i=0; i<header_row[0].length; i++) {
if (header_row[0][i] == '') { break; }
fields_from_sheet.push(header_row[0][i].trim());
}
return fields_from_sheet;
}
function getObjectFromArray(data, header) {
var result = {}
for (var i=0; i<header.length; i++) {
result[header[i]] = data[i];
}
return result;
}
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu('JetPack')
.addItem('Mail Merge as PDF', 'generatePDF')
.addToUi()
}
function generatePDF() {
var ui = SpreadsheetApp.getUi();
var data_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
var headers = getHeader(data_sheet);
var data = data_sheet.getRange(2, 1, 5000, headers.length + 1).getDisplayValues(); //Max 5000 rows
for (var i=0; i<data.length; i++) {
if (data[i][0] == '') { break; }
var row = getObjectFromArray(data[i], headers);
var copyFile = DriveApp.getFileById( getConfigValue('Template ID') ).makeCopy()
var copyId = copyFile.getId()
var copyDoc = DocumentApp.openById(copyId)
var copyBody = copyDoc.getActiveSection()
var newFileName = getConfigValue('FileName').replace(/\$\{(.+?)\}/g,(match, p1) => { return row[p1] });
for (var j=0; j<headers.length; j++) {
copyBody.replaceText("\\$\\{" + headers[j] + "\\}", row[ headers[j] ]);
}
copyDoc.saveAndClose()
var newFileFolder = DriveApp.getFolderById( getConfigValue('Output Folder') )
var blob = copyFile.getAs('application/pdf')
newFile = newFileFolder.createFile(blob)
copyFile.setTrashed(true)
newFile.setName(newFileName)
}
ui.alert("Files succesfully generated.")
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment