A Script for Generating Google Docs from Spreadsheet Data Sources
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.
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 withobfuscate.js)
2. Short introduction to Google Apps Script
Google Apps Scripts can be invoked in two ways
- From Google Driver user interface (Add new… more… Script)
- Directly from Google Apps spreadsheet (there is a menu entry)
- And more
- There is also integrated public snippet sharing service in Google Spreadsheet… though UI really leaves room for improvement here. You can also sell scripts in Google Chrome Web Store.
Because we are not working on the spreadsheet we need to use the former approach.
The script is executed on the server-side and you have a non-fancy localized browser based UI to edit and debug your script.
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):
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)