Thursday, January 5, 2017

MacGyver Moment #1: Data sharing for the rest of us

We’ve got a new series for the #StateofCO IT blog to highlight state employees innovating in government—MacGyver Moments!

Our first MacGyver Moment comes from one of the state’s developers Greg Ostravich. Check out his video interview with Digital Transformation Officer Brandon Williams, and read what he says below.

If you've been around technology for a while then you probably remember Microsoft Access Database—a solution that was popular in the 90s enabling customers to easily share data.

At the State of Colorado we have the “big three” of solutions: SalesForce, Perceptive, and Google. These tools are great for large scale projects, but what do we do if we want a solution for a smaller dataset? Or if we want something that doesn't require the licensing of SalesForce or Perceptive? What if we want something that doesn't require in-house developers to come up with a Java solution that runs in the Google App Engine using a cloud-based database?

Well, there is a niche solution that does work for these types of scenarios. Enter Google Sheets.

There are two ways to do this:

  1. The newer option is using Google App Maker, a tool that is still bubbling up. It is a low code way of extending features within Google Apps.
  2. The other option, that I used, is to bind an HTML form and Google App Script to a Google Sheet to create a way for users to search content without having a heavy developer-dependent solution. The solution lives in the cloud, doesn't require any costs for deployment, and allows "read-only" sharing of content for a customizable set of users.
Without going into too much detail, here’s what I did: |

Took a Google Sheet and under the "Tools" menu clicked on the "Script Editor".

From the Script Editor, I inserted a Google App Script ( and an HTML file.

These two files, which are bound to the Google Sheet, work in conjunction with each other to allow the user to access a traditional HTML form where they type in their search criteria and submit the form. 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 that lives on the HTML file that's bound to the sheet. Because it's just HTML, the Google Sheet can even contain HTML (bulleted lists in one example I worked on) and they will render correctly.

I’ve put together this example to show you how it works here.

Thank you Greg for an awesome example of building a technology solution on a shoestring budget, and without an over complicated architecture. What do you guys think?


  1. Nice, Thanks for sharing and taking the time to share.

  2. Thanks for taking the time to share the ideas...gregg

  3. hmm... interesting way to expand our data availability

  4. Thanks. This is a solution we could possibly apply to our school. Thanks for sharing.


OIT encourages open discussion, and we invite you to share your opinion on our issues. By commenting on this blog, you are agreeing to our commenting policy, outlined below.

We reserve the right not to publish comments on our blog containing any of the following elements: profanity, misinformation, spam, off-topic/irrelevant (including self promotional posts not having to do with IT or the organization), personal attacks, promotion of violence, or the promotion illegal or questionable activities.

If you repeatedly violate this policy, you will be blocked from commenting.

If you have a question regarding this blog or anything on it, please email us at

We appreciate your cooperation and support, and look forward to connecting with you!