Skip to content

Instantly share code, notes, and snippets.

@sp2hari
Last active May 8, 2020 13:35
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save sp2hari/49a74793200e3c7b169b508609c7466c to your computer and use it in GitHub Desktop.
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