Kickin' Library Databases with a Google Sheet

We have a number of database subscriptions and our students use them regularly for research and reference. They each have strengths, some are well suited for biographies for instace, while others excel at zoology. Age appropriateness also varies. While our librarian discusses these attributes with our students, they sometimes need a reminder. Why not create a sortable database for the databases?

Creating something like this is a perfect task for Romain Vialard’s Awesome Table - a very cool Google Gadget that I’ve written about before. Our meta database allows students to filter results by grade levels and goals.

This post is a “how to.” It’s long, but it works well. Our system has a few features that I do not describe in detail below because it would just be too long. We use a second Awesome Table as a dashboard to allow Kate Byrnes, our librarian, to edit submissions and turn entries on and off. Form Mule gives us the capability to edit submissions and email the submitter. I promise to  blog about these enhancements in the future.

What You Need Before You Start

• A google form connected to a Google Spreadsheet. The form should ask for the information needed in your site. This is grouped into two categories: information that will be visible in the awesome table and information that will filter the awesome table. My form uses the following:

Question	Question Type
Subscription Title	Text
Subject Area	Checkbox
Grade Level	Checkbox
Useful For	Checkbox
Icon URL	Text
Description	Paragraph Text
Image URL	Text
Web Link	Text
Password	Multiple Choice (Y,N)
Available Platforms	Checkbox

• A protected Google Doc with your database passwords. We share this document only within our school domain to control access to the passwords.
  
  

Design Your HTML Template

Set Up Your Awesome Table Template

Awesome tables can use an HTML template to render variables from form. This is an incredibly powerful feature that Romain Vialard kind of snuck into his tool. I learned about it from talking to James Peterson, he’s a smart and helpful guy - worthy of following!

AwesomeTables uses HTML to draw the cards for the interface. You can be creative with the HTML. If you aren’t comfortable with HTML, you can use a page in a Google Site to design your card with sample data. Once your composition looks right in sites, click on the “HTML” button in the menu bar. Copy the HTML from the window that appears. Then, return to your Google Sheet and add a page named “Template.” Paste your HTML into cell B1 of the template sheet. I have included my HTML further down the post.

The key to using the AwesomeTable template feature is identifying the variables in your HTML. These are the values that will change from entry to entry, such as the subscription title. Each place in the HTML where these variables are used should be changed to reflect the name of the corresponding column in the Google sheet (shown in red below.) Each variable needs to start with ${“ and end with “} (shown in green below.) The HTML used in my template is:

<table style="width:100%;background-color:#ffffff;border-radius:25px;padding:15px">
<tbody>
<tr>
<td>
<div><img border="0" src="${"Icon URL"}" style="display:inline;margin:5px 50px 10px 10px;background-color:transparent;width:30%">

<h2><a href="
${"Web Link"}
" target="_blank">
${"Subscription Title"}

</a></h2>
<hr>
<div style="line-height:1.5">${"Description"}
<div><br>
<div style="line-height:1.5">
<div>
${"Password Condition"}
</span></div>
</div>
</div>
</div>
</div>
</td>
</tr>
</tbody>
</table>

Creating the Awesome Table

The Awesome table is fed through the form, but the gadget needs row two of the the Google sheet to contain instructions. Drag the freeze frame line under row two, locking the first and second row. In this second row, add instructions for how Awesome Table will filter and show the data from that column. You can learn more about Awesome Table filters here. My system uses the filters shown below. Notice that they all say “Hidden.” This is because the visible bits are being handled by the template. The items that are being used in the template say “nofilter.” These are not showing as a filter on the top of the table because they are expressly used for rendering the database cards. Ultimately, the sheet looks something like this.

Question	Question Type	Purpose	Filter
Subscription Title	Text	Builds cards	nofilter - Hidden
Subject Area	Checkbox	Filters table	csvFilter - Hidden
Grade Level	Checkbox	Filters table	csvFilter - Hidden
Useful For	Checkbox	Filters table	csvFilter - Hidden
Icon URL	Text	Builds cards	nofilter - Hidden
Description	Paragraph Text	Builds cards	nofilter - Hidden
Image URL	Text	Builds cards	nofilter - Hidden
Web Link	Text	Builds cards	nofilter - Hidden
Password	Multiple Choice (Y,N)	Builds cards	nofilter - Hidden
Available Platforms	Checkbox	Filters table	csvFilter - Hidden

Making the Password Link Conditional

Some of the items in our database are subscription based, like World Book, and need a password to access. Other sites are free, like DK Find Out, but are certainly worthy of representation! We are managing our passwords with a protected Google Doc and wanted a way to show the link to the doc when a password is required but read “No password required” when the site was free. This is done with an “If, then” statement.

Title the column on the left of your Form Response sheet “Password Condition.” In row two, set this column to “nofilter - Hidden.” This “If, Then” statement is going to reference the “Password” Yes or No question. If no password is need, it will say simply that.  If a password is required, HTML for the password link will be used. I add this HTML to the Template page, in cell B11. Again, use that Google Sites trick  if you aren’t big on HTML

In row 3, add the function below. This is a nested “If, Then” statement. If this function could talk, this is what it would say, “Okay, I’m an array, so I will apply to whole column (ARRAYFORMULA.) I’m going to look at the timestamp column (A3:A) to tell me whether or not I should work. If there is something there, I’m going to look at the Password column (J3:J.) If this says "No", then I guess I’ll write "No password needed.” But, it might say "Yes," in which case I’ll use the stuff in cell B11 of the Template page.”

=ARRAYFORMULA( IF( A3:A = "",, IF( J3:J="No", "No password needed", IF( J3:J="Yes", Template!B11,))))

Pulling it All Together

Now the sheet is ready to connect to the Awesome Table gadget.

1. Start by editing a Google Site page and inserting a Gadget. Use the “Featured” gadget library to find Awesome Tables.
2. Once added, open the gadget’s preferences window and log into Awesome Tables with your Google Account. Click the “New” button.
3. Then, paste in your sheet URL, the sheet name, and the complete cell range for your data. Remember to share the sheet to the same audience as your table. If the table is meant to public, the sheet also must be public.
4. Next, click the “View” menu and change the default view from “Table” to “Cards.” The variables in this menu require some trial and error. You can play with how many cards are in a row and wide each card is, but first, click the “General Settings” tab and name your table. I like three cards across.
5. Now, click the “Advanced Parameters” tab to connect the HTML template. In the box labeled “Template Range” add the sheet name and the box cell of the HTML code separated by an exclamation point. In my demo file this is “Template!B7.”
6. Set your gadget display height on the bottom of the preferences window. I usually use 2000.
7. Finally, and this is important, click the green “Save” button. Then click “OK” and save your Google Site page to see how it looks. Edit the page again to adjust the settings.

Congratulations for getting this far! I use Awesome Tables all the time and you will too when you unlock how they work. The great thing about something like this is that you can reuse the bones of the system. My next step with this system is to convert it into a Student Bookmarks tool and I’ll let you know how it goes. If you use this post, or if you have any questions, let me know in comments.