I’m a fanatic of charts. A week ago, one of my friends in the VC world throw me a use case - He is tracking a bunch of startups which he wants to compare traffic and unique visitors.

Here is the template we come up with:
http://www.editgrid.com/tnc/david/Web2Tracker.new

Neat? This template allows tracking of 12 groups of websites, max. 8 in each group. For each site entered, links to its respective Alexa, Compete, Crunchbase, Del.icio.us search, Google Blogsearch and Technorati search are automatically generated with simple spreadsheet function: hyperlink() and concatenate().

Even more interesting, by entering group number in respective cells in sheet “Alexa Charts” and “Compete Charts“, respective Alexa/Compete charts can be loaded into the spreadsheet. You can configure the options to load different type of charts such as: Alexa’s reach, rank and pageviews; Compete’s unique visitors, attention, average stay and page/visit, etc.. This was done with a EditGrid unique feature called dynamic remote image, which load image according to URL stored in a cell. The URL can be dynamically generated with spreadsheet functions such as concatenate().

Here’s a few interesting notes you may like to take away:

1. The Chart URL in websites (including Alexa, Compete, Y! Finance, Bloomberg, Reuters, etc. etc.) can be easily understood. The parameters are usually embedded in the URL, e.g.
http://home.compete.com.edgesuite.net/www.techcrunch.com_uv_310.png

The above will show the unique visitor (”uv”) chart of Techcrunch (www.techcrunch.com) of width 310px (”310″). You can easily guess it right after playing with the compete site for a few minutes.

2. With understanding of the parameters in URL, you can easily write a concatenate() function to concatenate the “parameters” into the URL. e.g.

=concatenate(”http://home.compete.com.edgesuite.net/”,A1,”_”,A2,”_310.png”)

This will give the above URL if A1 is storing “www.techcrunch.com”, A2 is storing “uv”. If A1 now change to “www.readwriteweb.com”, the URL will be pointing to the respective chart of Read/Write Web instead.

3. To insert dynamic images, you click “insert” -> “images…”, then input the cell reference of the concatenate() result, e.g. B1. So, whenever A1 change to a new domain or A2 change to a new chart type, the images will be reloaded automatically.

4. Loading images from other websites can be sometimes questionable. The web2.0 world has a nice name for it: mash-up. In EditGrid, we just want to provide a generic features for our users to make those charts work for them. I hope this is ok and there won’t be an “Alexaholic incident” happen to us.

5. To copy the above templates for your own use, just add “.new” at the end of the URL and hit return. Yes, turning an EditGrid spreadsheet into a template is as easy as this.

Have fun with EditGrid.

P.S. Some new features currently in our pipeline can make spreadsheet templates like this a few times more powerful. There will be a lot coming from EditGrid in 2008!

Suppose you have a private spreadsheet which contains sensitive payroll and annual leave information, but you’d like to publish some of the information to staff members for their quick reference. How can you do this with EditGrid’s new feature, linked sheet?

(It really is private — if you’d like to verify, check it out here.)

It’s easy to create a linked sheet. First, on the source, private spreadsheet, create a new sheet, say, “Annual Leave Report”, that contains only the publishable data, but no more.

Then, create a new spreadsheet and grant it the necessary permission for staff members to view. Next, insert a new sheet on this to-be-linked spreadsheet — as usual, you may either make use of the (+) button at the sheet navigation bar, or use the sheet context menu as illustrated below.

Now you can see that there’s a new button called “Link from another spreadsheet”. Click it to expand the toolbox.

Select the right source, that is, in our case, my spreadsheet “IBD Payroll” where we can find the sheet “Annual Leave Report”. The new sheet name is automatically detected. You may also give it a name, say, “IBD Payroll#Annual Leave Report”, later on for easier identification. Click [OK] to confirm.

Bravo! The linked sheet is successfully created. Note that the sheet is copied from our private source at once. Note also the “chain” icon in the sheet tab. Now all users with read permission on this linked spreadsheet, “IBD Annual Leave”, can see the publishable data from the private source. You may even build additional reports based on the source data using ordinary cross-sheet formulae:

Now if you have any updates to the payroll and annual leave data, you don’t need to copy the updates into two spreadsheets: just update them on the source spreadsheet, and users of the linked spreadsheet can retrieve the most up-to-date data from the source spreadsheet via the linked sheet using the Update Linked Sheet function accessible at the sheet context menu.

Don’t repeat yourself, any more!

Resources:

• The private source (really private!)
• The linked spreadsheet

P.S. Only a user with read permission to the source spreadsheet and write permission to the linked spreadsheet can create the link sheet, and afterwards all users with read permission to the linekd spreadsheet can update it. Therefore, you won’t be able to retrieve the sensitive data on the “Staff Record” sheet. Try it — to unlock the linked spreadsheet to gain write permission, the password is IBD.

Two weeks ago, Seth Godin asked for a websheet that could take a list of book titles and return the cover art from Amazon. The post also mentioned EditGrid.

Today, I’ve got some free time to re-visit this blog post. Is this possible? I asked one of my colleagues. “Give me an hour”, my colleague replied.

And here is what I have from my colleague. The resulting bookshelf is here:

And the spreadsheet:

http://www.editgrid.com/tnc/david/My_Bookshelf

And the XSL to transform the data in spreadsheet into the bookshelf:

http://www.editgrid.com/tnc/david/My_Bookshelf.covers.html.xsl

Right! This is a good showcase of EditGrid’s “My Data Format” (MDF) feature, which has been available since Public Beta 11 released in late August. If you would like to create your own bookshelf, follow these steps:

1. Login with your EditGrid a/c. (Register if you don’t have one.)
2. Click the following link to clone the spreadsheet. The XSL will be copied as well.

http://www.editgrid.com/tnc/david/My_Bookshelf.copy

3. Save it and input your set of information (you’ll need to give the cloned spreadsheet a name).
4. Your bookshelf is now ready via your spreadsheet’s permalink + “.covers.html”.

Yes, that’s how simple it is! You can easily mashup other web services using EditGrid as the data storage and transformation medium. EditGrid’s MDF has allowed a group of expert users to generate KML files for Google Earth.

MDF enables innovation on EditGrid. And there’ll be more!

P.S.

• We use Amazon’s web service to get the book cover image, so we add a link to link to the book in Amazon. Fair deal, right?
• If you would like to use a serious personal bookshelf service, we recommend Anobii, another Hongkong-based Web 2.0 service.

Let’s demonstrate how to use the Grid2Map Add-on. A picture is worth a thousand words.

Step 1: Put the addresses in a spreadsheet

Step 2: My Workspace → Add-ons → Grid2Map

Step 3: Addresses on the map

Addresses have become much more meaningful!