Mail Merge to PDF Files

script.js

/*
            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.")
}