I recently built a book review site for #CdnEdChat. I even blogged about it! Here are some of the features, and a slide deck that should help anyone who is curious figure out how it's done. I've listed some of the features I wanted, and given an explanation of how I solved them. For a basic introduction, it's worth checking out John Calvert's Education On Air session (which inspired this project), and the follow-up advanced session (which he graciously hosted to help me learn some of the more advanced stuff).
I like the format, but I wanted to have multiple reviews of the same book appear all in one place. I also wanted to have each unique title appear only once. This seemed like a challenge, but I was able to solve it using a couple of steps:
Create a cell in each row which would contain all the reviews of any other rows where the title matched. That means that each book that appeared more than one time would have more than one row, but each of those rows would have all the reviews.
Sort the rows of data alphabetically. This was done by using a sort function on a new sheet.
Filter the sorted titles to show only unique entries in the title column.
Use a vertical lookup to find the matching row data. This is why we needed to sort the data alphabetically in step 2.
Rather than having only the title appear, I created an additional column for each row. This column creates an HTML table of 1 row, 2 columns, for each book. The title goes the the left cell with left-aligned text. The right cell displays the number of stars on average, and the number of reviews.
Since I'm asking users to submit a link to an image of the book cover, I wanted to include an image attribution automatically. I did this by using a spreadsheet formula to show only the base URL, and link to the image submitted by the user. This will let people quickly see where the image came from.
Since I'm creating a venue for people to suggest good books, I wanted to quickly link to an online retailer. I went with Amazon for a couple of reasons: 1) it's where I usually go when I'm looking for a book, and 2) it's easy to construct an advanced search results page just using URL queries. I used a spreadsheet formula to replace every space character with a plus, and pass that information into the advanced search URL on Amazon.ca. Now when a user clicks on the title of the book, they are taken to the best search results, based on author and title.
After I put the site together, I realized that I needed to show my work. Since this wouldn't have been possible without John's generosity with both his time and his own learning, I wanted to publish what I've done. I sincerely hope that this is an indication to the world how much I value the skills, knowledge, and patience of my network of friends in sharing what they've learned with me. My greatest hope is that this might serve to inspire others to put together their own projects, whether similar or vastly different.