Software

Channeling MacGyver, Colorado OIT Offers Google Sheets Data Sharing Hack

This simple, low-cost approach gives citizens access to searchable data sets.

Juliet is the senior web editor for StateTech and HealthTech magazines. In her six years as a journalist she has covered everything from aerospace to indie music reviews — but she is unfailingly partial to covering technology.

Looking for an engaging way to share data with your residents, at a fraction of the cost of Salesforce or other large-scale tools? The Colorado Governor’s Office of Information Technology has you covered. Using Google Sheets, the OIT has found a way that state IT departments can enable data sharing on a shoestring budget. The Google Sheets data-sharing tip was featured as a “MacGyver Moment” on the #StateofCO IT blog, which made StateTech’s 2016 list of Must-Read IT Blogs.

“We have situations where we have Excel spreadsheets living in the cloud, they’re Google Sheets. And we want to expose that to customers, where they don’t have access to the spreadsheet, they can’t make any changes to the spreadsheet, but they can look up data and look up results,” says OIT Developer Greg Ostravich in a video on the #StateofCo IT blog.

Basing his work on a co-worker’s solution, Ostravich added a user interface wrapper to Google Sheets to create the searchable database. (You can find an example on the state’s site.) This enables data sharing for smaller data sets without requiring in-house developers to create a Java solution that runs in the Google App Engine using a cloud-based database.

“I think of it as an old-style [Microsoft] Access Database, but it lives in the cloud, and it’s really cheap technology … it’s a great, low-cost way for people to be able to share their data,” Ostravich adds.

To create the sheet, Ostravich notes in his post that he inserted a Google Apps Script and an HTML file into a Google Sheet using the Script Editor function. According to Ostravich, these two sheet-bound files work together to allow users to type search criteria into a typical HTML form that they then submit.

“That action runs Google Apps Scripts that walk through the sheet searching for matching data, and then dynamically generates and populates the results in a div container that lives on the HTML file that's bound to the sheet,” Ostravich said. He adds that because the Google Sheet only uses HTML, it can contain HTML elements, including bulleted lists, and they will render correctly.

Developers can also use Google App Maker, a low-code way of extending features within Google Apps, to create the data-sharing tool.

Google