Until recently I, like the vast majority of Google Apps users, believe that Google’s word processing application is a mediocre reflection of Microsoft Word. And honestly, it is. But as lacking as it is in terms of its inability to compete head-on with the most advanced and widely adopted desktop word processing application (ever), it is also much more than a mediocre alternative if streamlined collaboration and workflow automation matter in your business solutions.

And why is this the case?

Because Google Docs as we know it, is a simple and elegant application veneer that rests on top of two key infrastructures – the Google Drive platform, and the Google Apps scripting environment. To be clear, the Google Docs word processing application – and for that matter –all Google Apps represent a very small slice of the potential capabilities that Google has assembled since its humble beginning in 2007.

My consulting practice is largely focused on building automation services using Google Apps Script (GAS). I design and implement script-based solutions for large enterprises like KFC, and small ones as well like Summit Express. Most of my projects entail the development of automated solutions involving data capture and integration, analytical processes, and workflows that include the generation of HTML and PDF documents. A good many of my projects also leverage Google’s graphical and data visualization SDKs. But until recently, no single project required the integration of all these capabilities in a single reporting solution.

Personalytics

This is a term I coined to describe the solution my client needed. It is a combination of personas and analytics – a delicate blend of subjective content and facts about a “user”. In this case, the user is one of many enterprise workers that the client wants to understand better to improve IT support and enterprise application development.

The Personalytics solution is relatively straightforward. It is designed to capture data about various user personas in an enterprise and generate a common report for each persona. This approach offers management the ability to generate report averages from multiple respondents who perform like roles as well as individual reports for specific users.

The client expressed one clear requirement about this solution – it must generate a final multi-page persona report that includes both text and graphics in a PDF document. Simple enough, right?

Is Native Google Docs a Candidate?

As I considered the prime objective, my first thoughts concerning an implementation approach did not place Google’s word processing application on the short list. I knew that the Docs application could be controlled through GAS, but I was well aware of its limitations in terms of formatting and other precision issues that would be needed to generate a quality report.

However, lacking any reasonable integration approaches and the likelihood that any approach that requires calling out to other document or report generation services would add complexities to the final deliverable. I like to build solutions that are sustainable, and this is best achieved by creating services that are no more complex than they need be.

Templates

For many reasons, I felt that this solution needed a templated approach; a way to establish a common report format that could be easily maintained without calling programmers, and which would deliver a high-performant generation process. Performance is important not because there are lots of documents to generate; to the contrary. This solution – when in full production – was likely to generate at tops, about 250 multi-page reports per month. The impetus for achieving high performance is the fundamental limitation of GAS; scripts may not run for more than six minutes per triggered event.

Lastly, the GAS script SDK for building Google word processing documents was understood to be pretty slow. In other projects involving simple report generation and eventual output to PDF format, I knew that generating native Google documents from scratch was a very sluggish proposition. Designing and pre-fabricating the report format as a template might save a lot of script steps.

So a template architecture made a lot of sense.
Personalytics reports represent a mixture of data visualizations and textual content formatted with some degree of precision – all of it captured from enterprise workers through Google forms.

And because I was able to achieve layout precision, I decided to move Google Docs to the short list and attempt fabricate the template in a native word processing document.

The native Google Doc template for this application looks like this.

The document template uses basic document formatting with tables that serve to create columns. Content substitution variables are defined by wrapping them in % characters. This approach provides a highly maintainable model that is warmly familiar to enterprise workers.

And by avoiding calling out to external reporting solutions, the automation approach with a native document template is ridiculously simplified; copy the template, replace the variables, save the report to PDF.

Building the template and tagging the target variables is the easy part. Now the work begins.

Content and Data

As I mentioned earlier, the information about the personas flow into the process through a Google form and initially into a spreadsheet. Despite the fact that a third of the content in a persona is textual in nature, Google forms handles this pretty well.

All of the data in the Personalytics system is managed in a spreadsheet. The report generation logic draws on the spreadsheet to calculate charting and other report features.

But before we start replacing variables, an essential step is to make sure the report logic takes a copy of the template. Google Apps Script makes this very easy to do.

The final line of code in this fragment retrieves the new native document’s body and we’re ready to start substituting variable tags with real data.

Transforming the Information

Happily for lazy developers like me, Google Apps Script provides everything you need to replace tagged variables in the native document template onto the real data.

As you can see in this closeup of the header of the Personalytics report template, the variables are nicely aligned in tables. Each variable is wrapped in % markers making it particularly simple to search and replace the variables with appropriate content meant for these locations in the report.

Text Transformations

The script to achieve variable substitution for text items is also pretty simple. In the business logic of the report generation process, function calls like this perform the variable substitutions.

I created a function whose sole purpose it is to transform any variable with a textual field value.

Given a field name – even one with embedded spaces – this function will reduce the field name to a non-space variable name, seek it out in the spreadsheet, and replace the content with a lookup to the specified field name, i.e., psnField_(fieldName).

The psnField_() function is a simple lookup process that hunts for the field name in the spreadsheet and returns the value for that field. The final output once the variables are transformed is complete.

Chart (Graphical) Transformations

Of course, transformation text variables is a straightforward. Graphical objects, however, require a little more effort. But once you have a pattern, exploiting this capability across many image and charting resources becomes commonplace.

The first page of the report also includes two pie charts and these can serve as good examples for this type of variable substitution.

Like the text transformation approach, variables can also be substituted with graphical objects – images specifically. This is a little more tricky because the script must first create the charts in memory and from scratch, and then we must take a snapshot of the chart as an image and embed it where the variable for this chart is located in the template.

This close up of the template shows the two pie chart targets for page one of the report.
Unlike text variables, which may occur repeatedly in a document (such as a company name), chart and image objects are typically going to be rendered one time. As such, you can use a the FindText() method to locate a single variable. Only a location object is returned by the FindText() method, so the replace approach requires different steps.

With the chart [presumably] configured and fully defined in the report logic, the next code fragment locates the replacement variable (i.e., token), and replaces it with a place holder image object which is then substituted with the actual chart object.

Lastly, we set the image height and width to ensure that the formatting range is maintained in the template’s intended table layout.

Conveyance of the Report

Now that we have the completed report, we need only make one last transformation before sharing or sending to reporting stakeholders.

Once again, GAS comes to the table well-prepared. You recall the earlier segment where the reporting logic adds the final report to the Google Drive folder. With the docID variable, we can transform the native document report into a PDF document with a single line of code.

With a PDF file object, we can save it to a shared folder or send it as an attachment using the MailApp SDK.

Conclusions

It’s apparent from the success of this project that we can learn many things about Google’s native doc format and it capacity to serve as an automated reporting solution.

• Report templates can be easily created, maintained, and versioned by enterprise workers who are unskilled in scripting services.
• Transformation of text and images – even complex charts are possible with minimal scripting effort.
• Reporting systems can be automated from the inception of data to the conveyance of the report via PDF.
• When coupled with many of the integral features in Google Docs, all available through scripting services, reports can be designed with precision and may include embedded data visualizations.

Personalytics Code

Personalytics is available for purchase through a consulting engagement or as a licensable automation system for Google Apps.