Friday, July 25, 2008

Embed Live Spreadsheet into a Web Page

I just had a Geek Moment...

As my family plans our trip to New Zealand, the need arose to have a place on the wiki to record expenses as they are accrued. For example, much to her surprise, my sister just discovered that the hotel rooms she booked for us on Stewart Island have already appeared on her Visa bill.

My first thought was to create a table on the wiki. It wouldn't have formulas, but we can all handle the math. However, as much as I love Wetpaint as a wiki platform, their tables aren't full featured. I can't figure out how to add a new row to a table that has already been created. Tabbing doesn't work. I can't find a hot place or menu that allows me to expand the table. If you know of one, please tell me.

Since that didn't seem to be a good option, I started thinking of ways to embed a spreadsheet into the wiki. I know you can embed a Google Form into a wiki and even show a live chart as people add data, but I didn't find a way to do that with Google Sheets.

Fortunately a web search pulled up an article on the Zoho blog that lead me to this post on the Digital Inspiration blog. It shows step-by-step directions for embedding a live spreadsheet onto a webpage using Zoho Sheet, a free, online spreadsheet. It even provides a link which allows viewers to download a copy of the spreadsheet to their computer.

I already have a Zoho account. I think their suite of online applications is far superior to Google's. So I...
  1. Logged in to ZohoSheet.
  2. Created a spreadsheet. (Or I could have uploaded one from my computer.)
  3. Clicked on the menu item to created code to embed the spreadsheet.
  4. Copied the code to my clipboard.
  5. Using the toolbar on my wikipage, I clicked the tool to allow me to embed a widget.
  6. I pasted the code in the box that appeared, chose whether I wanted to change the size or justification of the spreadsheet on the page, and clicked "OK."

When I saved the wiki page, the live spreadsheet appeared on the page. It is like magic! I can scroll around, add data, click on the tabs. My lovely colors, bolds, filled formulas, etc. are all there because the spreadsheet is embedded using an I-frame - something you web design gurus will understand.

As I experimented, I learned the follow things.
  • If I change the spreadsheet in Zoho Sheet, and save my changes, those changes appear on the wiki when I refresh the wiki page.
  • If I make changes to the spreadsheet on the wiki, those changes are not reflected in Zoho Sheet.
I can forsee a few potential problems embedding spreadsheets this way. First, to embed a spreadsheet you must make it public. That means that even though my wiki is private, it is humanly possible that someone could find the public spreadsheet and mess with our data. However, they would need to know the unpublished URL to get to it. This doesn't seem likely.

Another problem is that Zoho's servers can be a bit slow. That was a big problem in the past, but now I'm finding them to work just fine. I suspect they upgraded to eliminate that problem.

A final problem is that not all blogs or wikis allow you to embed i-frames. I was able to do so in Wetpaint via the "insert widget" button and here in Blogger by clicking the "Edit Html" tab and then pasting the code where I wanted the spreadsheet to appear. Check your platform's editing toolbar to see if you have that option.

So, give it a try. Add some data to my spreadsheet. And let me know if you have found other or better ways to embed a live spreadsheet into a web page.

[UPDATE: Bad news. My sister tried using it on our wiki, and as old math man says here in the comments, the spreadsheet works great until you navigate away from the page. At that point, all your data is lost. So, this is an elegant solution for data that you don't want to keep. For example, making a web site on the solar system and allowing visitors to enter their own weight in pounds or kg to find out how much they would weigh on different planets.

Can you think of any other times you wouldn't want to retain the data after the visitor leaves your site?]