Making the Google Work…With Itself

Broken Google-boti hope this post proves helpful to people who find themselves searching (as i did earlier today, for much longer than was necessary) for answers to Google Spreadsheets and Google Sites problems.

It may be that what i was working with is super-specific and therefore the answer is only marginally helpful outside of this one project, but something tells me that there’s enough useful stuff here that it’s worth posting. Also, i’m not a web designer, m’kay?

WHAT I WAS TRYING TO DO was create a filterable list for a client, then embed it in a Google Site. They wanted a user to be able to enter or select the name of their city (within a specific region) and then get a list of technicians that serve that area, with links to their individual bio pages with more information – specifically, contact info. Deceptively simple, and certainly there are plenty of more complex and attractive solutions out there….but part of the point of Google Sites is that what kinds of codes and objects it will allow is pretty limited, so that Jane Anybody could build a reasonably attractive and functional site. with little to no code knowledge.

PROBLEM: The “List” page template in Google Sites is actually quite cool in allowing you to make tables of several columns of data that are all sortable, some of which can be links to other pages…but it doesn’t allow the filtering function that i needed, nor could i code links wherever i wanted.
SOLUTION: With the help of @GoogleSitesWD on Twitter, i figured out that an embedded Google Spreadsheet would give me the functions i needed. So i created a very simple two-column spreadsheet wherein each technician had a line item for each community he serves.

PROBLEM: i needed each instance of each technician’s name to be a link to his own page on the site that listed more details about his services and service area, as well as the all-important contact info. There is no “Link” button in the Google Spreadsheet tools, where one might highlight some text, hit Link, then type or paste a URL….< sarcasm >because that would be WAAAAY too difficult.< /sarcasm >
SOLUTION: Instead, through an inordinately difficult series of searches and slogging through dated Help threads, i found that in order to place a hyperlink, you have to enter the following into the cell in which you want a link:

=HYPERLINK(“http://www.yourURL.com”,”Text of Link”)

Funky, right? No “a href=”, no brackets…just strange punctuation hidden in the depth of Google Help-dom. Anyway, there it is.

PROBLEM: Embedding the spreadsheet in the Google Site just showed the spreadsheet as-is, without any of the cool filtering functionality that is the POINT of all this.
SOLUTION: When embedding the spreadsheet, make sure you select for it to be viewed as a List (not as Spreadsheet or Spreadsheet (Published)). Also, be sure that the Share options for the spreadsheet are set at “Public to the Web” so visitors can see it!

A few other styling selections (Centered, 100% width, etc) and viola: check this embedded spreadsheet out in action at 805tuners.com!
To make the link above, i highlighted it and clicked the “link” button, then typed in the URL. Easy.

Have you used Google Sites or Google Spreadsheets? Anything you wanna share about the experience?

This entry was posted in web design and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge