Skip to content

Automatically Generate Infographic Reports from Spreadsheet Data

6 minute read

dkpurp cloud

Most business people assume that the primary attractor to Google Docs is its near-free price. Many like the fact that it’s a fully cloud-centric collaborative environment. However, the promise of lower cost and better accessibility by teams is just a small part of the growing desire for businesses to escape from Microsoft Office.

Click here to download dashOne for highly automated processes in Google Apps

One key incentive for adopting this platform is its capacity for scripting automation services. Taking this to a higher level, dashOne demonstrates how to automatically generate HTML infographic-style reports from spreadsheet data.

Docs: So Misunderstood

Google Docs is a label that is so misleading and under appreciative of its true nature, that describing it in accurate terms requires lots of diagrams and deep exploration.

By using it for a variety of enterprise-grade data visualization and reporting solutions, I’ve leveraged some of the incentives that have triggered a tsunami of defections from Microsoft Office. But beyond reasoned abandonment of Office and adoption of Google Docs, is the broad blue ocean of opportunities that Google Docs provides.

Introduction

I love automation.

There’s only one thing that excites me more than using Google Docs for automated data processes and that is the ability to add visualizations to create greater comprehension and readability.

I’m not referring to the wealth of charts and graphs available in Google Docs. Rather, I’m excited about visualizations that are far simpler and typically easy to understand for readers who may not need the precision of the many chart types available in Google Docs.

Unlike heavily detailed charts, these are lightweight data visualizations – big bright circles, bars, and drawing objects that create easy-to-understand data points. Visualizations like this.

dashOne Example 1

 

DashOne is not a comprehensive reporting solution; it is a pathway to building comprehensive reporting solutions that meet your specific requirements. The code provided in the dashOne package provides a methodology for building compelling and automated data presentations.

dashOne (Think InfoGraphics)

Highly graphical visualizations are appealing to all business people and I haven’t found a case where they cannot be used to create attention and foster deeper understanding of operational data. This approach is especially useful for high-level awareness of operational data.

This is more like an InfoGraphic and less like a “report”. InfoGraphics are far more compelling than tabular reports and often, somewhat more compelling than complex and overly detailed business charts and graphs.

When I roll out a dashOne solution in a firm, I get the sense that business people are report-weary. A dashOne approach provides a refreshing and unique way to push out the most important elements of operational data. dashOne-style reports are not intended to replace the details that may also be important. Although, even the example Brand Stats report demonstrates how grid data can be combined in a report to provide the high-points and the details behind these numbers.

Furthermore, because my approach is based on open web standards (HTML and CSS) its also possible integrate drill-down links to detailed information sources.
What’s Possible?

This is the sample cover for the outgoing email report. It provides some data to tease the reader.

dashOne Example 2

For my clients, I typically send a small sample of the report data in the cover message and attach a much more detailed collection of analyses in an HTML document.

In this snippet from the dashOne demo code, I blend column charts and grids with large titles to create a simple and easy-to-grasp report layout.

 

dashOne Example 3

I’ve learned that executives like simple reporting presentations. Often, it only takes a few circles to drive home a specific KPI.

 

dashOne Example 4

Inside the eBook

The ebook and example solution scripts are extremely simple to use and understand. It includes a user guide and access to a single shared Google Docs spreadsheet. The spreadsheet includes three additional resources – (i) a form for requesting demo report examples, (ii) a sheet with example data, and (iii) the script source for everything this solution provides.

While the resources included in this package are useful straight out of the zip file, source code is included because there are so many ways to employ this solution and so many different implementation contexts. I intended for you to copy this example and modify it to meet many use cases in your business. Please don’t copy it and give it to other people or other businesses.

If you’re really stumped about how Google Apps Scripts work and need a helping hand, I’m a consultant and available anytime to chat about your objectives.

Solution Architecture

When I design Docs-based solutions for my clients, a key requirement is how lightweight the application is and how sustainable it is.

Cloud systems are typically configured to be sensitive to network constraints as well as mobile devices. Mobile users have little time to read reports and limited connectivity to access or download large or dense documents.

Data movement must be constrained as much as possible and instead of pushing graphical elements by value, they should ideally be delivered by reference. Instead of drawing every pixel on a chart, modern browsers should be leveraged to perform the heavy lifting associated with rendering.

While it’s possible to automatically generate native Google documents through script, I choose HTML as the output format for many projects. HTML is very lightweight and even the most complex reports may be as small as 20K. By referencing images and graphical elements on a server, the report doesn’t have to transmit all the graphical elements bound into the document. And by using CSS, significant portions of reports related to graphical objects can be squeezed out of the implementation.

dashOne is designed using standard HTML tags and everyday CSS (cascading styles) – nothing fancy and all supported standards. It’s easy to read and easy to understand. Best of all, there are millions of developers who understand it and who can help you expand its functionality.

And most important, HTML and CSS looks good in modern mobile climates.

dashOne Mobile Devices

Reporting

In and of itself, the definition of reporting is vague. At a general level, some might argue that the dashOne sheet is in fact a “report”. To use it, one would have to log into Google Docs, select the sheet, and then print it. There’s nothing inherently wrong with this approach, but our challenge is to extend this to achieve automation as well as a simple but compelling report.
Reporting Events
Ideally we’d all like to eliminate humans from the reporting process. Imagine these simple requirements –

When the data in the dashOne sheet changes materially, a series of reporting events must occur. The reports must be transmitted via email to specific target audiences, and copies of the reports must be uploaded to Box and versioned for historical review.

In the case of the example Brand Stats for which dashOne is based on, perhaps an email message alerting certain managers should occur. And perhaps once a month, a summary report is created for management and shared with the sales team.

With Google Docs and Google Apps Scripting services this is not difficult to achieve with an automation script that watches for changes in the data sheet, and then creates formatted messages and documents containing the latest data, dispatching them to predetermined email addresses at the moment they become most relevant to the recipients.

And in this ebook I have provided precisely the basis for a script that can do this.

Automation Script

Using Google Apps Scripting services (GAS), I’ve created an example script that will send email notifications every time the example Brand Stats report is requested through a Google form (this form). And it sends the notification with a snippet of the report data along with the full report attached.

The HTML report generated is simple, but demonstrative of how to format, save, and attach such documents to email messages.

Taking this example and expanding it to format complex data is entirely possible with Google Apps script and your own custom HTML; dashOne is the pattern for implementation success.

HTML Documents

When I first started using Google Docs, I had no idea that (a) the scripting environment could generate HTML documents; (b) the Docs platform could save and manage them; and (c) they could be attached to email messages.

Once this became clear to me, I started to experiment with script-based HTML extensively. My findings are simple – it’s one (of many) very powerful models that extend the benefits of Google’s cloud-based spreadsheet environment while integrating well with nearly any other enterprise cloud service such as Box. And the icing on the cake – the scripting services embrace email just like all its other services.

Conclusion

You need a basic understanding of GAS (Google Apps Scripting) and some time to design your own solutions based on these examples. But the dashOne script is as much a learning tool as it is a blueprint for a reporting approach.

Click here to download dashOne for highly automated processes in Google Apps

Sign up for our newsletter