Script for generating Google documents from Google spreadsheet data source

Microsoft Office supports “data sources” to generate e.g. letters, invoices, address stickers and other repeating documents based on Microsoft Word template and Microsoft Excel data. This is a very common small business problem and Office has had a solution for it from mid-90s. Google Apps, the cloud based alternative to Microsoft Office, do not offer similar functionality natively (or at least if they do they hide it really well). However, you can quite easily create your own document generator using Google Apps scripting as long as your are proficient in programming. In this blog post I’ll show an example how to create such a script and learn the basics of Google Apps Scripts.

1. Ingredients of the document generator

We have following the following inputs for our business problem

  • A Google Apps spreadsheet which contains customer data.
  • A Google Apps docs template document. Based on it, we want to generate a document for each customer by filling this template document with the data from the spreadsheet.
  • A Google Drive folder where the resulting documents are stored.
  • A Google Apps script which automatizes the task for us (Javascript based)

In my case the use case was generating contract texts for the customers based on their price and quality of service data. Then I just exported and emailed the resulting Google Docs as PDF.

All of these are stored on your Google Apps account in Google Drive. All editing happens through Google Apps user interface, no external tools needed.

Example of source data (obfuscated with obfuscate.js)

Example of the template document (obfuscated with obfuscate.js). You can see the source labels, unfilled.

Example of the resulting document – labels filled in and no longer in bold (obfuscated with obfuscate.js)

2. Short introduction to Google Apps Script

Google Apps Scripts can be invoked in two ways

Because we are not working on the spreadsheet we need to use the former approach.

Google Apps Script is a JavaScript (ECMAScript version unspecified? Does it run V8?) cloud scripting language that provides easy ways to automate tasks across Google products and third party services. The Google Apps Script has extensive API documentation with examples and tutorials, but they are still much subject to change as almost everything is marked as experimental and already there exist a lot of deprecated methods. The Google Apps Scripts can also access Google Maps, contacts, email, sites, Google Apps domain setting and basically have automation solution almost everything you can do in Google cloud.

The script is executed on the server-side and you have a non-fancy localized browser based UI to edit and debug your script.

The philosophy and UI design patters feel like a step back to 90s, to the Visual Basic scripting environment. Maybe Google Apps developers wanted this… so that Visual Basic developers feel back home. However, coming from a web development, Javascript and general programming background you will find the lack of Firebug / Web Inspector like console disturbing. It does not feel like any other Javascript development, though certainly the syntax is the same.

So my minor complains include, but are not limited to

  • Logging from the applications is possible, but the log trace is very unreadable in UI
  • The program does not have a specific entry point, you need to choose a function using a selection widget. This makes the script feel like a toy.
  • Debugger (and lack of console) does not seem to allow you to modify and dynamically poke objects in run-time (call functions, etc.)
  • Debugger is a bit slow (round-trip to Google servers, a bit), though still pretty much useable
  • Lack of low end user interaction tools in standalone scripting (please see below)
  • API documents and reality did not always match (as everything is still experimental)

Debugger in action

Things could be better, but in the end I managed to get done what I was looking for and I am still not paying a penny for Google Apps, so I am happy. Also, I do not wish to go back to Microsoft Office unless I need to write well-formatted print documents… Google Docs is a toy what comes to heavy and graphically sensitive document authoring like offers…. Or presentations… where Keynote is the king.

3. The generator script

In the beginning of the script you have constants which define on which data to operate. You could build an user interface making the script to full web application, but this is too cumbersome approach for such a small task. The UI builder seemed nice, but definitely an overkill. Though there exist Google Apps Script API methods for performing simple prompt() question in the browser,  for some reason they were not supported in standalone scripting… so the fastest approach to enter data into the script was simply edit the script itself before each run. I sooo started to miss command line… first time in my life.

So, in the beginning of the script you define the source data

  • Spreadsheet id (you can pick it up from URL when you edit the document)
  • Template document id (you can pick it up from URL when you edit the document)
  • Customer id which is the spreadsheet row number, for the current script run
  • The Google Driver folder id where the resulting document will be placed for sharing. Again you can pick the id from URL when opening the folder.

Then the script simply replaces words with data. The keyword to be replaced in the template document are identified as the column labels (1st row) in the spreadsheet data. I am pretty sure there would be more efficient methods to do this, but I did not wish to spend time to go to knee deep to GS to figure out its nuances.

And then the script… please feel free to modify to your own needs (generator.gs):

/**
 * Generate Google Docs based on a template document and data incoming from a Google Spreadsheet
 *
 * License: MIT
 *
 * Copyright 2013 Mikko Ohtamaa, http://opensourcehacker.com
 */

// Row number from where to fill in the data (starts as 1 = first row)
var CUSTOMER_ID = 1;

// Google Doc id from the document template
// (Get ids from the URL)
var SOURCE_TEMPLATE = "xxx";

// In which spreadsheet we have all the customer data
var CUSTOMER_SPREADSHEET = "yyy";

// In which Google Drive we toss the target documents
var TARGET_FOLDER = "zzz";

/**
 * Return spreadsheet row content as JS array.
 *
 * Note: We assume the row ends when we encounter
 * the first empty cell. This might not be 
 * sometimes the desired behavior.
 *
 * Rows start at 1, not zero based!!! 🙁
 *
 */
function getRowAsArray(sheet, row) {
  var dataRange = sheet.getRange(row, 1, 1, 99);
  var data = dataRange.getValues();
  var columns = [];

  for (i in data) {
    var row = data[i];

    Logger.log("Got row", row);

    for(var l=0; l<99; l++) {
        var col = row[l];
        // First empty column interrupts
        if(!col) {
            break;
        }

        columns.push(col);
    }
  }

  return columns;
}

/**
 * Duplicates a Google Apps doc
 *
 * @return a new document with a given name from the orignal
 */
function createDuplicateDocument(sourceId, name) {
    var source = DocsList.getFileById(sourceId);
    var newFile = source.makeCopy(name);

    var targetFolder = DocsList.getFolderById(TARGET_FOLDER);
    newFile.addToFolder(targetFolder);

    return DocumentApp.openById(newFile.getId());
}

/**
 * Search a paragraph in the document and replaces it with the generated text 
 */
function replaceParagraph(doc, keyword, newText) {
  var ps = doc.getParagraphs();
  for(var i=0; i<ps.length; i++) {
    var p = ps[i];
    var text = p.getText();

    if(text.indexOf(keyword) >= 0) {
      p.setText(newText);
      p.setBold(false);
    }
  } 
}

/**
 * Script entry point
 */
function generateCustomerContract() {

  var data = SpreadsheetApp.openById(CUSTOMER_SPREADSHEET);

  // XXX: Cannot be accessed when run in the script editor?
  // WHYYYYYYYYY? Asking one number, too complex?
  //var CUSTOMER_ID = Browser.inputBox("Enter customer number in the spreadsheet", Browser.Buttons.OK_CANCEL);
  if(!CUSTOMER_ID) {
      return; 
  }

  // Fetch variable names
  // they are column names in the spreadsheet
  var sheet = data.getSheets()[0];
  var columns = getRowAsArray(sheet, 1);

  Logger.log("Processing columns:" + columns);

  var customerData = getRowAsArray(sheet, CUSTOMER_ID);  
  Logger.log("Processing data:" + customerData);

  // Assume first column holds the name of the customer
  var customerName = customerData[0];

  var target = createDuplicateDocument(SOURCE_TEMPLATE, customerName + " agreement");

  Logger.log("Created new document:" + target.getId());

  for(var i=0; i<columns.length; i++) {
      var key = columns[i] + ":"; 
      // We don't replace the whole text, but leave the template text as a label
      var text = customerData[i] || ""; // No Javascript undefined
      var value = key + " " + text;
      replaceParagraph(target, key, value);
  }

}

 

4. Translations

Please see Czech translation by Alex Bojik from Bizow.com.

Please see Polish by Valeria Aleksandrova.

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+

28 thoughts on “Script for generating Google documents from Google spreadsheet data source

  1. Hello Mikko
    thanks a lot for this script.
    I found it really useful and I created a modified version titled ‘GoDDaMn’ with some modified features according the needs of the company I work for:
    The differences between ‘Generated Google Docs’ and ‘GoDDaMn’ are:
    – Modified script dubbed GoDDaMn or gddm.gs (Google Docs Data Marge).
    – Modified script published.
    – Added String replacement instead of Paragraph replacement.
    – Modified the replacement string TAG format to ‘:tag:’ .
    – The substituted Strings are set in Bold to be ‘highlighted’ in the output file.
    – Changed ‘customer’ to ’employee’ because this is the use we make of it in Inviqa.
    – If no employee_ID is embedded in the script, then the script sources it from the currently selected line of the spreadsheet.
    – The modified script is meant to be run as an embedded script in a spreadsheet and triggered at any Form submission, in such case the employee_ID is the I of the newly inserted line, and this is a default behaviour of Google Script.

    Ypu can find the published code and documentation at our GitHub account in case you are interested in it: https://github.com/inviqa/SysAdmin/tree/master/goddamn

    Thanks again.

    Marco MC

  2. Why don’t you use the script to convert to PDF and send by e-mail the documents? Sure you have your reasons but, anyway, I let the functions here for anyone interested.
    Convert to PDF: pdfDocument = DocsList.getFileById(targetId).getAs(“application/pdf”);
    Send by e-mail: MailApp.sendEmail(destination, subject, message, {htmlBody: messageHTML, attachments: pdfDocument});

    Just had to do a little script and I didn’t remember you had published this. 🙂

    Cheers,

    Christian.

  3. Hi, thanks for making this! i am having some problems setting it up and making it work though! if you could help me get this setup and running it would be really great! thanks!

  4. Pingback: Google Script: How automatically open a created text document after have create it | Hontap.com

  5. I like this script, but I’m also looking for a way to add attachments to the google spreadsheet, then import that attachment (as a jpg would be fine) to the google doc; maybe as a second page. Any suggestions?

  6. Great article. Is there a way to use Google Forms instead of Google Spreadsheets to get the info from? I would prefer if I could type it in to create contracts for example.

  7. THis is great- just what I am looking for. Although, I am also using forms to populate a spreadsheet and i would like this to automatically export the row of data to a PDF when the form is completed. It would be also wonderful to connect forms with calendars so that When i select a start date for a project, and an end date for a project in a form then a calendar event is created.

    Still, no one else seems to be doing this, so thanks!

  8. Hi Paul – I think what you are looking for are definitely possible with Google Drive API (it was Google Docs by the time writing this article…)

  9. Pingback: Resolved: How to automatically open a created text document after it being created #programming #dev #computers | InfoBot

  10. Thanks for this great script. I have been using it for several months now. Im wondering if you will be doing an update using the Drive API?

  11. Hello,

    Yeah it is really a great script work well but i have little problem it merge just the first 4 column from spreadsheet even i have change columns .length to 13 because i have 13 column in Google form spreadsheet response :
    for(var i=0; i for(var i=0; i<13; i++)
    Anyone have an idea

  12. This works really well! How would you go about changing the script if the row does not end at the first empty cell?

  13. I have an issue where one of my questions asks for a number. If the answer is “0”, it breaks the output. Is there something I can do to fix this?

  14. Well now the script is broken since docslist is depreciated. Ive tried changing over to DriveApp but still can’t get it to work. Any chance of an update?

  15. Hello,

    I have tried to migrate to Driveapp after the Docslist API is deprecated but i have another problem the Google apps script to merge google document from Google spreadsheet data stop firing and just create the document without getting value so any help ?

  16. This is the script update after depreaced Doclist migrated to driveApp :
    Just change createDuplicateDocument function with this code below :

    function createDuplicateDocument(sourceId, name) {
    var source = DriveApp.getFileById(sourceId);
    var newFile = source.makeCopy(name);
    var targetFolder = DriveApp.getFolderById(TARGET_FOLDER);
    targetFolder.addFile(newFile)
    return DocumentApp.openById(newFile.getId());
    }

    If you need any help am here ^_^
    i hope this will be helpful

  17. I have tried to replace DocsList with DriveApp

    and after i got TypeError: cant find function addToFolder in the object undefined agreement. (line66, file “”)

    i replaced this line: newFile.addToFolder(targetFolder);
    with this line: targetFolder.addFile(newFile);

    After that it didnt throw any more error but the fields in the pdf it created were not replaced but the data of the spreadsheet ;(

    Any help?

  18. Hello,

    Thanks for sharing this is a great script. I was wondering what the max number of documents you are able to generate per day using this tool. Have you had an issues generating over 1000 documents per day from this.

    Thanks,

  19. Thank you very much for this!!! I’m not a programmer, but managed to figure out how to go about it. I had to use the new code added from Marco Massari Calderone. and after some silly mistakes on my part I manage to create a nice word document from a database on google spreadsheet! No more copy paste!

    Great write up and samples.

    I did had a bit of trouble figuring out the key format, but with your help and Massari’s script I did it!

    Thanks a lot for sharing

  20. THERE’S NOW A BETTER SOLUTION

    Google now has support for add-ons and there are multiple that do what this article says. All you need to do is open your Sheet, go to Add-ons -> get add-ons and install one. I used autoCrat, worked great – no scripting no hassle.

    It’d be nice if you put this disclaimer on the top of your article, Mikko. As when googling for this feature all i found was this 🙂

  21. Hey Guys, running the script should update the google doc with the table in the googe sheet, correct? nothing happens on my end so want to make sure I am doing it right and that all it takes to autofill the google document is running the script on script.google.com.

    thanks

  22. Do i just need to run the script and it will copy the table in the google document?

  23. Getting Error
    ReferenceError: “DocsList” is not defined. (line 64, file “Code”)

  24. Nice script

    Is it possible that he generates from an html page or html script instead of spreadsheet ? I am trying to make a script that generats to google drive doc/word through html ?

    can you help me with this

Leave a Reply

Your email address will not be published. Required fields are marked *