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:
- 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.
- 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.
Took a Google Sheet and under the "Tools" menu clicked on the "Script Editor".
From the Script Editor, I inserted a Google App Script (code.gs) 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?