Trying to display relevant data in a website using Google Spreadsheet along and allowing for an easy way to update the spreadsheet poses quite a few interesting, but not insurmountable challenges.
The background of why resort to such a method is in part 1.
For one, the information should not be hard coded into the site content as the specifications may change as for sure will the Price.
A Google Iframe Gadget (I used one by Scott Johnston) can display only adjacent columns (or rows) and not random columns - for eg., it can display columns A,B,C,D,E but not A,C,E. The solution was to create individual worksheets for each bike (scooter) and tell the gadget to display a1:b20 of relevant sheet by changing the reference ‘gid’ number in the URL. Here is the (old) table named ‘hhtable’ (Google Docs).
Two, we wanted to provide the user the ability to compare the models - like based on Engine Capacity, Brake types, Wheel types or even Price and such. Without access to SQL or any other database it was a vexing hurdle to cross, till I learned about Google Query Language (GQL) and how to use it in this excellent post by Tony Hirst at his blog [http://blog.ouseful.info/2009/05/18/using-google-spreadsheets-as-a-databace-with-the-google-visualisation-api-query-language/]
and the tool he has so thoughtfully provided [http://ouseful.open.ac.uk/datastore/gspreadsheetdb2.php].
Which involved a sub problem of its own. It required the data to be transposed. Earlier the ‘header’ was in Column-A and the data in Columns-B,C,D and so on. Now for GQL to work, the ‘headers’ have to be in Row-1 with the data in Rows-2,3,4 and so on. No problems as just transposing the data (or using an array formula) into a new spreadsheet ‘hhtable1’ (Google Docs) proved good enough.
A suitable link in the home page (Google sites) offering options under ‘Compare Models’ was a hit generating considerable enquirers and the site itself appears 2nd on the Google Maps listing, right after the manufacturing company’s own Chennai office.
:-D
But this August the principals parted ways with their foreign collaborator which meant a site update. Attempting to rework the site highlighted my own shortcoming as a web designer without a lot of foresight.
:-P
The problems were:
The reason?
Updating information, especially Price meant keying 2 prices for each bike - an ‘ex-showroom’ and an ‘on-road’. With 20+ models it meant keying 40+ set of figures in each sheet or close to 100 keyed figures in total translating to about 2 man-hours.
As ‘negligence is the better part of drudgery’, it was no wonder that the site has been left to update itself.
The obvious solution is to combine both the Spreadsheets into one master workbook and recode the links in the site. The problem is that transposing from one sheet to another is possible with formulas, values, formatting etc., but not with links.
Merely transposing ‘values’ will not be enough as it would be back to square one with 2 sheets to update. It must be a live link.
With 24*40 sized array, it would be madness to try and enter the formula by hand in 960+ cells.
Thus it was time not to get mad but get smart. That workaround in part-3 with an interregnum at part-2C (no, there are no parts-2A or 2B)
:-)
The background of why resort to such a method is in part 1.
For one, the information should not be hard coded into the site content as the specifications may change as for sure will the Price.
A Google Iframe Gadget (I used one by Scott Johnston) can display only adjacent columns (or rows) and not random columns - for eg., it can display columns A,B,C,D,E but not A,C,E. The solution was to create individual worksheets for each bike (scooter) and tell the gadget to display a1:b20 of relevant sheet by changing the reference ‘gid’ number in the URL. Here is the (old) table named ‘hhtable’ (Google Docs).
Two, we wanted to provide the user the ability to compare the models - like based on Engine Capacity, Brake types, Wheel types or even Price and such. Without access to SQL or any other database it was a vexing hurdle to cross, till I learned about Google Query Language (GQL) and how to use it in this excellent post by Tony Hirst at his blog [http://blog.ouseful.info/2009/05/18/using-google-spreadsheets-as-a-databace-with-the-google-visualisation-api-query-language/]
and the tool he has so thoughtfully provided [http://ouseful.open.ac.uk/datastore/gspreadsheetdb2.php].
Which involved a sub problem of its own. It required the data to be transposed. Earlier the ‘header’ was in Column-A and the data in Columns-B,C,D and so on. Now for GQL to work, the ‘headers’ have to be in Row-1 with the data in Rows-2,3,4 and so on. No problems as just transposing the data (or using an array formula) into a new spreadsheet ‘hhtable1’ (Google Docs) proved good enough.
A suitable link in the home page (Google sites) offering options under ‘Compare Models’ was a hit generating considerable enquirers and the site itself appears 2nd on the Google Maps listing, right after the manufacturing company’s own Chennai office.
:-D
But this August the principals parted ways with their foreign collaborator which meant a site update. Attempting to rework the site highlighted my own shortcoming as a web designer without a lot of foresight.
:-P
The problems were:
- Change the logo - not much trouble as every Facebook user knows how easy it is to remove the old avatar and upload a new one
- Modifying the manufacturer’s name in the website content - is also easy, as ‘Find > Replace’ took care of it
- Modify the URL of the manufacturer’s website - proved to be a problem as earlier I had put the URL in the website body, which would mean about 25 edits. This time around, I removed the URL reference from the body content and moved them into the footer.
The Spreadsheet has not been updated in more than an year.
8-0The reason?
Updating information, especially Price meant keying 2 prices for each bike - an ‘ex-showroom’ and an ‘on-road’. With 20+ models it meant keying 40+ set of figures in each sheet or close to 100 keyed figures in total translating to about 2 man-hours.
As ‘negligence is the better part of drudgery’, it was no wonder that the site has been left to update itself.
The obvious solution is to combine both the Spreadsheets into one master workbook and recode the links in the site. The problem is that transposing from one sheet to another is possible with formulas, values, formatting etc., but not with links.
Merely transposing ‘values’ will not be enough as it would be back to square one with 2 sheets to update. It must be a live link.
With 24*40 sized array, it would be madness to try and enter the formula by hand in 960+ cells.
Thus it was time not to get mad but get smart. That workaround in part-3 with an interregnum at part-2C (no, there are no parts-2A or 2B)
:-)
No comments:
Post a Comment