Autofill a DOCUMENT template from a Google sheet and email as a PDF using Google Scripts

Here's a fast, and very cool script for auto filling out a google doc template with data in a spreadsheet. As the title implies, it also conveniently sends you, or anyone you wish, a PDF copy of the new document. I could have made it inconveniently send you 1000s of copies of the document... but conveniently... i did not. This script is especially useful if you have contracts, or invitations etc that you need to fill out quickly. Follow instructions below to setup:

Step 1

Open THIS google drive folder, then go to File -> Make a Copy

Once you've done that you will have a folder that contains:

1. Google Document titled "Template Document" - this will serve as your template from which the script will generate autofilled versions.

2. A folder titled"Output folder" - which is where your generated files will be saved

3 A google sheet titled "Template Generator", which will contain the data used to populate the template doc, as well as the script to make it all happen!

They should look like this

STEP 2

Open the template document and add the content that you require. Make sure the fields that you want to auto-fill are surrounded by "%" symbols.

STEP 3

Open the google sheet, where you'll find 2 rows:

  1. A row of labels which you can add to if you need more.
  2. A row of data.

Go ahead and fill out the user data you wish to auto-fill your template with

STEP 4

In the Google sheet, go up to TOOLS -> SCRIPT EDITOR

Paste in the script below:

(NOTE: Scroll to the right to view the whole script)

function onOpen() {
  var menuEntries = [ {name: "Create Autofilled Template", functionName: "AutofillDocFromTemplate"}];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("Create", menuEntries);
}

function AutofillDocFromTemplate(){
  var templateid = "ADD YOUR DOC ID HERE"; // this is the template file id. You can find this in the URL of the google document template. For example, if your URL Looks like this: https://docs.google.com/document/d/1SDTSW2JCItWMGkA8cDZGwZdAQa13sSpiYhiH-Kla6VA/edit, THEN the ID would be 1SDTSW2JCItWMKkA8cDZGwZdAQa13sSpiYhiH-Kla6VA
  var FOLDER_NAME = "NAME OF DESTINATION FOLDER"; // Enter the name of the folder where you want to save your new documents. for example, this could be "Output Folder".
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
 
  var usernamefordoctitle = sheet.getRange(2, 1, 1, 1).getValues(); 
  var newDoc = DocumentApp.create("Template for " +usernamefordoctitle);
  var file = DriveApp.getFileById(newDoc.getId());
  var folder = DriveApp.getFolderById("ID HERE"); // INSERT the destination folder ID. Once again, this can be found in the URL once you have the folder openned in your browser
  folder.addFile(file)
  for (var i in data){
    var row = data[i];
    var docid = DriveApp.getFileById(templateid).makeCopy().getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();
    body.replaceText("%NAME%", row[0]);
    body.replaceText("%DATE%", row[1]);
    body.replaceText("%PHONE%", row[2]); // To add more auto gen fields, add them below along with the column number
    
    appendToDoc(doc, newDoc);
    
    doc.saveAndClose()
    newDoc.saveAndClose()
    var message = "Attached is an auto generated template. O YEA!"; // Customize message
    var emailTo = "myemail@gmail.com" // replace with your email
    var subject = "My auto generated template"; // customize subject 
    var pdf = DriveApp.getFileById(newDoc.getId()).getAs('application/pdf').getBytes();
    var attach = {fileName:'Autogenerated template.pdf',content:pdf, mimeType:'application/pdf'}; // customize file name: "Autogenerated template"
    MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
    
    DriveApp.getFileById(docid).setTrashed(true);
    
  }
  ss.toast("Template has been complied. It worked. it's alive. IT'S ALIVE!!");
}

function appendToDoc(src, dst) {
  for (var i = 0; i < src.getNumChildren(); i++) {
    appendElementToDoc(dst, src.getChild(i));
  }
}

function appendElementToDoc(doc, object) {
  var type = object.getType();
  var element = object.removeFromParent();
  Logger.log("Element type is "+type);
  if (type == "PARAGRAPH") {
    doc.appendParagraph(element);
  } else if (type == "TABLE") {
    doc.appendTable(element);
  } 
}

Now go through comments and instructions in the code and customize it as required.

STEP 4

To create your autofilled template, open your spreadsheet. Select the custom menu, then select CREATE.

And you're done :)

Danny Baker

Danny Blaker, Melbourne

Danny has a wealth of experience in the start-up and technology sectors spanning over 10 years, and is the founder and co-founder of numerous companies and initiatives, such as Unudge, & Geartooth.

Danny's diverse skill set encompasses disruptive marketing strategy, business strategy, product design, audio production, data analysis (R, R Markdown, Python), graphics design (photoshop, Indesign, Illustrator), communications, social media marketing, project management, growth strategy, UX design, front-end web development (WP, Joomla, JS, Python, HTML, CSS), and corporate law.

Danny is also a spreadsheet expert and an online instructor, teaching at Udemy.com. His courses have amassed over 11,000 students to date.

He also blogs regularly – you can find his posts at www.dannyblaker.com/blog.

You can reach Danny on twitter @DannyBlaker