Saturday, November 17, 2007

Zoho sheet ,Dapper,and Google Maps

More Weekend Hacking With Zoho Sheets

one common feature request missing in Zoho Sheets is about RSS export ,here i will briefly describe a hack which enables ,live zoho RSS export

I have gone a step further,and integrated Zoho Sheet with Google Maps

The Application:
There is a live excel sheet hosted on Zoho Sheets,which gives info on Radio stations in India for radio Enthusiasts....

Updating the sheet with coordinates will be immediately reflected in the Google Maps

Technology Used:

Zoho Sheet:
Can be embedded onto your blog/webpage (Here it is in my blog),and contains City name and Location

Dapper :
This is the killer app which I used to parse the HTML output from Zoho Sheet and convert to RSS.

Yahoo Pipes:
To extract Geo Encoded data From The RSS feed...

The Steps:

Step 1:

Create a Zoho Sheet as shown I think it is quite easy
(You can see that the left column contains co-ordinates)





Step 2:

Select a range of values as shown below


Step 3:

Click Publish this range

Step 4:

A dialog will popup in that only copy the src=" " URL as shown below...

http://sheet.zoho.com/publishrange.do?id=4e3fbe4fe327b95c95d8ba9f5ce3573f

(This hack only works with the range URL's it doesnt work with on complete publish)



Step 5:

From here you will have to go to the site dapper.com,
Dapper is basically an API Creator which enables us to create API's for HTML pages...



Step 6:

Go to http://www.dapper.net/dapp-factory.jsp
and create a new application as shown



Step 7:

Click next, then add this url from ZOHO ,and press GO ,the click on add to basket...


Step 8:

The next step is a bit complicated you need to select the columns and make 'fields' for these colums as shown
.....It takes practice be patient :)



Step 9:

Now select Atom Format and click 'GO',as you can see dapper also provides other formats like XML,JSON,CSV



Step 10:

Now select the fields that need to be there in the ATOM feed.





Step 11:

You can see that a feed is generated you can also see that we can change the input to this 'Transformer' Dynamically(Eg: later on you can simply give a different ZOHO sheet in the URL itself, and it will Transform the HTML for You...)...Just like XSL....



Step 12:

Go to yahoo Pipes pipes.yahoo.com
and you have to perform some transformations on the feed I cannot give the steps here ,since they are bit long ,I made the pipe public feel free to access it at
http://pipes.yahoo.com/jynn/zohosheet



Step 13:

Publish the pipe and get the RSS feed ,and pass the feed Onto google Maps as a Quer ...q=

I made a tiny URL since it is simpler ...It is as shown below

http://maps.google.com/maps?q=http://tinyurl.com/28toce



The coolest/Wickedest result you have ever seen :

Edit the Sheet and see the changes in the MAP.
Sheet:




Map:




View Larger Map