Sunday, 8 April 2012

Maintaining a dynamic list of research publications using google spreadsheet as a database:

Well friends, when i was designing a website for my department i was looking for a way to keep the publications page dynamic than to load the information in the HTML codes. Then i found a way to do that and i thought it will be helpful for many people like me, if i shared this piece of information.

Note: For understanding this method at-least a beginner level of knowledge in HTML is necessary.

Well let's say that you have a webpage where you want to make a list of publications not just by typing everything in the HTML code but to keep the list somewhere else (for instance in google spreadsheet) and just the content should be fetched from that list and displayed in your webpage in a dynamic way i.e., whenever you edit (add or delete some publications) it should be reflected in your webpage. This can be done in many ways and i'll share the method which i found after weeks of searching in the internet.

Part:1

Open a Gmail account(if you aready have well and good)

Sign in your Gmail account and Go to google docs and make a new spreadsheet.

First row of the spreadsheet is for headings( or ids ) which is very important since that is going to define how the list of publications should appear.

So make the headings as in the below picture.
(serial, author, title, citation, pubmed-id)

Note:(I'm showing you a simple example, you can add how many ever headings you want)

Ok now to start with add a few publications to the spreadsheet. Make sure that you add the datails of the publications under the correct heading. To get a clear idea see the the screenshot below,

Ok now you have a spreadsheet(which is your database) with the list of publications which you want to be displayed in your webpage of interest.
Now go to file menu in your spreadsheet, click the publish option and select the csv format as shown in the screenshot below.

Now you'll see a key in the menu as shown in the screenshot below which you should copy and add to the html code of your webpage.

Well, still now we got a csv format output from our google spreadsheet. Next step is to convert this in to JSON format data and later we will convert this JSON to HTML through a javascript plugin called Tempo to display it in the webpage in whatever style we need.

Part2: CSV to JSON

To do this conversion we need a bit of PHP codes,[note:i'm not the owner of this code and i got it from someother site]
The code is as follows

I'll explain the above structure in detail.
No.1 is linking the tempo plugin which i've explained previously.
No.2 is html codes to display your publications.
Here is a simple example. you can write your own codes but the ids which you use should be the same as the one which you used as headers in your google spreadsheet.

Here i've created a nested list(list inside a list). You can create whatever you like(ex., you can display it as a table) but use the same ids(author, title, citation) that you used as headers in the spreadsheet. The term data-template in the code is essential, since it says tempo where to load the data in the webpage.For furthe details visit Tempo plugin documentation page http://tempojs.com/

No.3 is javascript part(codes between script tags). It has two parts.
first part is to place your PHP code to fetch JSON data.
As shown below ,
type "var data="(the one within the quotes)