A Cobbled-Together Online Resource Database

One of our smaller file cabinet pages.
One of our smaller file cabinet pages.

When our district started our Common Core transition, we wanted to set up ways for teachers to share resources and files they found with each other. We are a Google Apps district, so my first thought was Google Sites. We set up “file cabinet” pages, gave certain teachers access rights, taught them how to use folders, etc. This worked OK for a while, but once some departments collected a LOT of resources, it quickly became unworkable and frustrating.

A couple of months ago, I came across a great example of a gadget called “Awesome Tables” for Google Sites. (I first saw it on the fabulous MTBoS Directory site.) Inspired by this great site and what Awesome Tables can do, I decided to try it out for a couple of projects: one, a multi-district collaboration of EdTech teachers; two, resource collections for our district’s Science and Math departments. Along the way, I combined it with a Google Form Add-On, a Google Sheets Add-On, and a small bit of custom scripting to make a pretty nice online database system.

I wanted to take advantage of the ability to search by tags, and to have multiple tags for a resource. This is a much more flexible system than using folders, where any file can be in only one folder at a time. But I wanted the system to be flexible, so users could add their own tags and didn’t have to simply choose from a pre-determined set. I solved that problem by using the FormRanger add-on for Google Forms to re-build the list of tags after each form submission.

Here’s the way this works:

1. The user submits the form with the resource or file information. The “Tag” question in the form is either a multiple-choice question (if you only want one tag per resource) or a checkbox question (if you allow more). Either way, “Other” is enabled as an option. That way, if the user doesn’t want to use an existing tag, he or she can enter a new one.

2. The sheet does its work on the form submission. If your table requires formulas to be calculated on new form submissions, you can use the CopyDown add-on. If you are only using one tag per resource, you can use the UNIQUE function in sheets to create a list of unique tags. (I put that list in a separate tabĀ of the spreadsheet, for clarity.) If you are using multiple tags per resource, it’s a bit harder to generateĀ the unique list of tags. That’s where I had to do a quick bit of custom scripting. I wrote a custom function called SPLITTAGS that generates that list.

3. Awesome Table displays the contents of the sheet in the Google Site page, creating filters and search bars as you desire. (The Awesome Table documentation and examples are very good and quite helpful.)

4. FormRanger rebuilds the form based on the list of unique tabs generated by the sheet. That way, the next person to open the form sees the updated list of tags for them to select.

Relationships between form, sheet, site page, and add-ons
Relationships between form, sheet, site page, and add-ons

Here are some links where you can see these files in action:

Hope this is helpful. If you have any questions on this project, I’d be glad to help as much as I can.