THOSE WHO DO NOT WANT TO IMITATE ANYTHING PRODUCE NOTHING!

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

  1. Open a Gmail account(if you aready have well and good)
  2. Sign in your Gmail account and Go to google docs and make a new spreadsheet.
  3. 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.
  4. 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

<?php
/*
 * Converts CSV to JSON
 * Example uses Google Spreadsheet CSV feed
 * csvToArray function I think I found on php.net
 */

header('Content-type: application/json');

// Set your CSV feed
$feed = 'https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&
key=0Akse3y5kCOR8dEh6cWRYWDVlWmN0TEdfRkZ3dkkzdGc&single=true&gid=0&output=csv';

// Arrays we'll use later
$keys = array();
$newArray = array();

// Function to convert CSV into associative array
function csvToArray($file, $delimiter) { 
  if (($handle = fopen($file, 'r')) !== FALSE) { 
    $i = 0; 
    while (($lineArray = fgetcsv($handle, 4000, $delimiter, '"')) !== FALSE) { 
      for ($j = 0; $j < count($lineArray); $j++) { 
        $arr[$i][$j] = $lineArray[$j]; 
      } 
      $i++; 
    } 
    fclose($handle); 
  } 
  return $arr; 
} 

// Do it
$data = csvToArray($feed, ',');

// Set number of elements (minus 1 because we shift off the first row)
$count = count($data) - 1;
  
//Use first row for names  
$labels = array_shift($data);  

foreach ($labels as $label) {
  $keys[] = $label;
}

// Add Ids, just in case we want them later
$keys[] = 'id';

for ($i = 0; $i < $count; $i++) {
  $data[$i][] = $i;
}
  
// Bring it all together
for ($j = 0; $j < $count; $j++) {
  $d = array_combine($keys, $data[$j]);
  $newArray[$j] = $d;
}

// Print it out as JSON
echo json_encode($newArray);

?>

note: since we use php code, we need a host which has PHP server, else this code will not work.

Ok now just replace the the key which is highlghted in the code with the key which you copied from your spreadsheet.

So now one part of the work is over. When you add this code to your webpage and run the php script, JSON data will imported as follows:

[ { 
"serial":"1",
"author":"Silva Junior JM, Rezende E, Amendola CP, Tomita R,
 Torres D, Ferrari MT, Toledo DO, Oliveira AM, Marques JA",
"title":"Red blood cell transfusions worsen the outcomes even 
in critically ill patients undergoing a restrictive transfusion strategy.",
"citation":"Sao Paulo Med J. 2012;130(2):77-83.","pubmed-id":"PMID: 22481752",
"id":0},
{"serial":"2",
"author":"Kyo S, Takakura M, Nishida S, Ozaki S, Oda M, Inoue M.",
"title":"Massive hemothorax due to diaphragmatic endometriosis after
 a laparoscopic cystectomy of an ovarian endometrioma in a patient without 
a history of thoracic endometriosis.",
"citation":"Arch Gynecol Obstet. 2012 Apr 6. [Epub ahead of print]",
"pubmed-id":"PMID: 22481493","id":1},
{"serial":"3",
"author":"Spichiger E, M\u00fcller-Fr\u00f6hlich C, Denhaerynck K,
 Stoll H, Hantikainen V, Dodd M.",
"title":"Prevalence and contributors to fatigue in individuals 
hospitalized with advanced cancer: A prospective, observational study.",
"citation":"Int J Nurs Stud. 2012 Apr 3. [Epub ahead of print]",
"pubmed-id":"PMID: 22480997 ",
"id":2}]

Part:3
JSON to HTML

So now the next step is to convert this JSON data to HTML to display it in your webpage in the style of your wish.
For this you need a javascript plugin called Tempo.
Download link

How to add this plugin to your site?

Its very simple, like linking your css file to your html page. Just add the following line in the head of your html code. (link the js file according to the directory)

<script src="libs/tempo.js" type="text/javascript"></script>

How to link your JSON data and tempo?

So the code structure is something like the one below

<html>
<head>
1.-------link to tempo.js------
</head>
<body>
2.---html codes to display your publications---
3.<script>
var data=
<?php ***************codes************** ?>
;
****tempo code to render your JSON data****
</script>
</body>
</html>

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.

 <ol id="publications" >
     <li data-template >
  <ul>
                        <li>{{serial}} </li>
   <li>{{author}} </li>
   <li>{{title}}</li>
   <li>{{citation}}</li>
                        <li>{{pubmed-id}}</li>
  </ul>
  </li>
  
 </ol>

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)

<html>
<head>
1.-------link to tempo.js------
</head>
<body>
2.---html codes to display your publications---
3.<script>
var data=

</body>
</html>

- paste the php code(with your key replaced)

<html>
<head>
1.-------link to tempo.js------
</head>
<body>
2.---html codes to display your publications---
3.<script>
var data=
<?php>
*******
?>


</body>
</html>

type";" (this is important for code to work)

<html>
<head>
1.-------link to tempo.js------
</head>
<body>
2.---html codes to display your publications---
3.<script>
var data=
<?php>
*******
?>
;


</body>
</html>

So when the PHP code runs, you'll get the codes like the following :

<head>

**********
**********
*********
 
<script src="libs/tempo.js" type="text/javascript"></script>

</head>

<body>

<ol id="publications" >
     <li data-template >
  <ul>
   <li>{{author}} </li>
   <li>{{title}}</li>
   <li>{{citation}}</li>
  </ul>
  </li>
  
 </ol>

<script>

 var data=
[ { 
"serial":"1",
"author":"Silva Junior JM, Rezende E, Amendola CP, Tomita R,
 Torres D, Ferrari MT, Toledo DO, Oliveira AM, Marques JA",
"title":"Red blood cell transfusions worsen the outcomes even 
in critically ill patients undergoing a restrictive transfusion strategy.",
"citation":"Sao Paulo Med J. 2012;130(2):77-83.","pubmed-id":"PMID: 22481752",
"id":0},
{"serial":"2",
"author":"Kyo S, Takakura M, Nishida S, Ozaki S, Oda M, Inoue M.",
"title":"Massive hemothorax due to diaphragmatic endometriosis 
after a laparoscopic cystectomy of an ovarian endometrioma in a patient
 without a history of thoracic endometriosis.",
"citation":"Arch Gynecol Obstet. 2012 Apr 6. [Epub ahead of print]",
"pubmed-id":"PMID: 22481493","id":1},
{"serial":"3",
"author":"Spichiger E, M\u00fcller-Fr\u00f6hlich C, Denhaerynck K,
 Stoll H, Hantikainen V, Dodd M.",
"title":"Prevalence and contributors to fatigue in individuals 
hospitalized with advanced cancer: A prospective, observational study.",
"citation":"Int J Nurs Stud. 2012 Apr 3. [Epub ahead of print]",
"pubmed-id":"PMID: 22480997 ",
"id":2}]
;
**************
</script>
</body>

So now only one thing is left out.
add the following line of code after your PHP code within the script tags.

Tempo.prepare('publications').render(data);

so it should look like the one below.

<head>
************
</head>
<body>
 

 <ol id="publications" >
     <li data-template >
  <ul>
   <li>{{author}} </li>
   <li>{{title}}</li>
   <li>{{citation}}</li>
  </ul>
  </li>
  
 </ol>

 <script>
 
 var data=
 
 <?php
 /*
  * Converts CSV to JSON
  * Example uses Google Spreadsheet CSV feed
  * csvToArray function I think I found on php.net
  */

 header('Content-type: application/json');

 // Set your CSV feed
 $feed = 'https://docs.google.com/spreadsheet/pub?
key=0AgP1_CALsy4zdHFyWXVmY3hNY3hVR0gxNTdUNVg5NWc&output=csv';

 // Arrays we'll use later
 $keys = array();
 $newArray = array();

 // Function to convert CSV into associative array
 function csvToArray($file, $delimiter) { 
   if (($handle = fopen($file, 'r')) !== FALSE) { 
     $i = 0; 
     while (($lineArray = fgetcsv($handle, 4000, $delimiter, '"')) !== FALSE) { 
       for ($j = 0; $j < count($lineArray); $j++) { 
         $arr[$i][$j] = $lineArray[$j]; 
       } 
       $i++; 
     } 
     fclose($handle); 
   } 
   return $arr; 
 } 

 // Do it
 $data = csvToArray($feed, ',');

 // Set number of elements (minus 1 because we shift off the first row)
 $count = count($data) - 1;

 //Use first row for names  
 $labels = array_shift($data);  

 foreach ($labels as $label) {
   $keys[] = $label;
 }

 // Add Ids, just in case we want them later
 $keys[] = 'id';

 for ($i = 0; $i < $count; $i++) {
   $data[$i][] = $i;
 }

 // Bring it all together
 for ($j = 0; $j < $count; $j++) {
   $d = array_combine($keys, $data[$j]);
   $newArray[$j] = $d;
 }

 // Print it out as JSON
 echo json_encode($newArray);

 ?> 
;
Tempo.prepare('publications').render(data);
</script>
</body>
</html>

This will be rendered as codes below when your PHP scripts run.

<head>

**********
**********
*********
 
<script src="libs/tempo.js" type="text/javascript"></script>

</head>

<body>

<ol id="publications" >
     <li data-template >
  <ul>
   <li>{{author}} </li>
   <li>{{title}}</li>
   <li>{{citation}}</li>
  </ul>
  </li>
  
 </ol>

<script>

 var data=
[ { 
"serial":"1",
"author":"Silva Junior JM, Rezende E, Amendola CP, Tomita R,
 Torres D, Ferrari MT, Toledo DO, Oliveira AM, Marques JA",
"title":"Red blood cell transfusions worsen the outcomes even 
in critically ill patients undergoing a restrictive transfusion strategy.",
"citation":"Sao Paulo Med J. 2012;130(2):77-83.","pubmed-id":"PMID: 22481752",
"id":0},
{"serial":"2",
"author":"Kyo S, Takakura M, Nishida S, Ozaki S, Oda M, Inoue M.",
"title":"Massive hemothorax due to diaphragmatic endometriosis 
after a laparoscopic cystectomy of an ovarian endometrioma in a patient
 without a history of thoracic endometriosis.",
"citation":"Arch Gynecol Obstet. 2012 Apr 6. [Epub ahead of print]",
"pubmed-id":"PMID: 22481493","id":1},
{"serial":"3",
"author":"Spichiger E, M\u00fcller-Fr\u00f6hlich C, Denhaerynck K,
 Stoll H, Hantikainen V, Dodd M.",
"title":"Prevalence and contributors to fatigue in individuals 
hospitalized with advanced cancer: A prospective, observational study.",
"citation":"Int J Nurs Stud. 2012 Apr 3. [Epub ahead of print]",
"pubmed-id":"PMID: 22480997 ",
"id":2}]
;
Tempo.prepare('publications').render(data);
</script>
</body>

And the output will be the one which you see in the screenshot below. You can apply whatevet css style you want and change the appearance.

0 comments:

Post a Comment