Use Dojo Grid to Display Google Spreadsheets

Google Spreadsheets is not only a basic spreadsheet product,but also a very powerful tool for web applications. By using the Spreadsheets Data API it can be easily performed as a web-based database to generate a web form, a online shopping cart, a Google Map mashup .... etc.

Dojo DataGrid is a JavaScript library with spreadsheet view function , it supports datastores like Csv, Json, XML, Googlefeeds... etc. However, there is no Google Spreadsheets datastore for Dojo yet. In order to let Dojo Grid easily read Google Spreadsheets cells data, I created a PHP program to generate the CSV file from Google Spreadsheet as a dojox.data.CsvStore.

The PHP Zend Gdata 1.10.0 library is used to talk to the Google Data API, it takes care the client login, file listing, spread sheet files and worksheet tokens retrieving and cells data feed. retrieve the spreadsheets file list and key tokens, once user decide which file to read,

The first part of the PHP is for client login :

view plaincopy to clipboardprint?

1. <div style="color: blue;">  
2. <span style="font-size: x-small;"><i>set_include_path(get_include_path() . PATH_SEPARATOR . '/var/www/html/ZendGdata-1.10.0/library');  
3. require_once 'Zend/Loader.php';  
4. Zend_Loader::loadClass('Zend_Http_Client');  
5. Zend_Loader::loadClass('Zend_Gdata');  
6. Zend_Loader::loadClass('Zend_Gdata_ClientLogin');  
7. Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');</i></span>  
8. <span style="font-size: x-small;"><i>  
9. $user = $_GET["id"];  
10. $pass = $_GET["pass"];  
11. if(isset($_GET["key"]))  
12. $key = $_GET["key"];  
13. else  
14. $key="";</i></span></div>  

<div style="color: blue;">
<span style="font-size: x-small;"><i>set_include_path(get_include_path() . PATH_SEPARATOR . '/var/www/html/ZendGdata-1.10.0/library');
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');</i></span>
<span style="font-size: x-small;"><i>
$user = $_GET["id"];
$pass = $_GET["pass"];
if(isset($_GET["key"]))
$key = $_GET["key"];
else
$key="";</i></span></div>

To read the spreadsheets list :

view plaincopy to clipboardprint?

1. <div style="color: blue;">  
2. <span style="font-size: x-small;"><i>function promptForSpreadsheet1($gdClient)  
3. {  
4.     $feed =$gdClient->getSpreadsheetFeed();  
5.     $i=0;  
6.     $arr = array();  
7.     foreach( $feed->entries as $entry) {  
8.         $currKey = split('/', $entry->id->text);  
9.         $token = $currKey[5];  
10.         $arr[] = array("name" => $entry->title->text, "id" => $token);  
11.         $i++;  
12.     }  
13.     $jsonStr = json_encode($arr);  
14.     echo "{'identifier':'id','label':'name','items':$jsonStr}";  
15. }</i></span></div>  

<div style="color: blue;">
<span style="font-size: x-small;"><i>function promptForSpreadsheet1($gdClient)
{
    $feed =$gdClient->getSpreadsheetFeed();
    $i=0;
    $arr = array();
    foreach( $feed->entries as $entry) {
        $currKey = split('/', $entry->id->text);
        $token = $currKey[5];
        $arr[] = array("name" => $entry->title->text, "id" => $token);
        $i++;
    }
    $jsonStr = json_encode($arr);
    echo "{'identifier':'id','label':'name','items':$jsonStr}";
}</i></span></div>

To read the worksheets list of select spreadsheet :

view plaincopy to clipboardprint?

1. <span style="color: blue; font-size: x-small;"><i>function getFormWorksheet($gdClient)  
2. {  
3.     global $key, $wkid;  
4.     $query = new Zend_Gdata_Spreadsheets_DocumentQuery();  
5.     $query->setSpreadsheetKey($key);  
6.     $feed = $gdClient->getWorksheetFeed($query);  
7.      echo printFeed($feed);  
8.      $input = getInput("\nSelection"); */  
9.     $currWkshtId = split('/', $feed->entries[0]->id->text);  
10.     $wkid = $currWkshtId[8];  
11. }</i></span>  

<span style="color: blue; font-size: x-small;"><i>function getFormWorksheet($gdClient)
{
    global $key, $wkid;
    $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
    $query->setSpreadsheetKey($key);
    $feed = $gdClient->getWorksheetFeed($query);
     echo printFeed($feed);
     $input = getInput("\nSelection"); */
    $currWkshtId = split('/', $feed->entries[0]->id->text);
    $wkid = $currWkshtId[8];
}</i></span>

To read the header and row data and convert them into a CSV formatted file  :

view plaincopy to clipboardprint?

1. <span style="color: blue; font-size: x-small;"><i>function cellsGetCsv()  
2. {  
3.     global $gdClient, $key, $wkid;  
4.     $arr = array();  
5.     $query = new Zend_Gdata_Spreadsheets_CellQuery();  
6.     $query->setSpreadsheetKey($key);  
7.     $query->setWorksheetId($wkid);  
8.     $feed = $gdClient->getSpreadsheetCellFeedContents($query);  
9.     $jsonStr = json_encode($feed);  
10.     $pre_key="A";  
11.     $columns=0;  
12.     $rows=0;  
13.     $header = array();  
14.     foreach($feed as $key=>$entry){  
15.         if (substr($key, -1) <> "1") {  
16.             $columns = convertAlphabetToInt(substr($pre_key, 0, -1));  
17.             $jsonStr = json_encode($header);  
18.             break;  
19.         }  
20.         $header[] = $entry["value"];  
21.         $pre_key=$key;  
22.     }  
23.     $lastkey = end(array_keys($feed));  
24.   
25.     for ($k=1; $k<=strlen($lastkey); $k++){  
26.         if ( is_numeric(substr($lastkey, strlen($lastkey)-$k, 1))==false){  
27.             $rows=(int)substr($lastkey, strlen($lastkey)-$k+1);  
28.         }  
29.     }  
30.   
31.     $head = "";  
32.     $data = "";  
33.     for ($i = 0; $i< $columns; $i++){  
34.         $head .=  $header[$i] . ",";  
35.     }  
36.     $arr = array();  
37.     for ($j=2; $j<=$rows; $j++){  
38.         $line = '';  
39.         for ($m=1; $m<=$columns; $m++){  
40.             /*    $value = $feed[convertIntToAlphabet($m)+$j]->value; */  
41.             if(isset($feed[convertIntToAlphabet($m). (string)$j]))  
42.             $value = $feed[convertIntToAlphabet($m). (string)$j]["value"];  
43.             else  
44.             $value = "";  
45.   
46.             if ((!isset($value)) OR ($value == "")) {  
47.                 $value = ",";  
48.             } else {  
49.                 $value = str_replace('"', '""', $value);  
50.                 $value = '"' . $value . '"' . ",";  
51.             }  
52.             $line .= $value;  
53.         }  
54.         $data .= trim($line)."\n";  
55.     }  
56.     $data = str_replace("\r", "", $data);  
57.     echo "$head\n$data";  
58. }  
59.   
60. function convertAlphabetToInt($alpha_string) {  
61.     $int_wert=0;  
62.     $potenzcounter=0;  
63.     for ($i=strlen($alpha_string);$i>0;$i--) {  
64.         $ordinalwert=(ord(substr($alpha_string,$i-1,1))-64);  
65.         $int_wert+=$ordinalwert*pow(26,$potenzcounter);  
66.         $potenzcounter++;  
67.     }  
68.     return $int_wert;  
69. }  
70.   
71. function convertIntToAlphabet($int_wert) {  
72.     $alpha_string="";  
73.     if($int_wert%26>=1) {  
74.         $alpha_string=chr(($int_wert%26)+64).$alpha_string;  
75.         $alpha_string=convertIntToAlphabet($int_wert/26).$alpha_string;  
76.     }  
77.     return $alpha_string;  
78. }</i></span>  

<span style="color: blue; font-size: x-small;"><i>function cellsGetCsv()
{
    global $gdClient, $key, $wkid;
    $arr = array();
    $query = new Zend_Gdata_Spreadsheets_CellQuery();
    $query->setSpreadsheetKey($key);
    $query->setWorksheetId($wkid);
    $feed = $gdClient->getSpreadsheetCellFeedContents($query);
    $jsonStr = json_encode($feed);
    $pre_key="A";
    $columns=0;
    $rows=0;
    $header = array();
    foreach($feed as $key=>$entry){
        if (substr($key, -1) <> "1") {
            $columns = convertAlphabetToInt(substr($pre_key, 0, -1));
            $jsonStr = json_encode($header);
            break;
        }
        $header[] = $entry["value"];
        $pre_key=$key;
    }
    $lastkey = end(array_keys($feed));

    for ($k=1; $k<=strlen($lastkey); $k++){
        if ( is_numeric(substr($lastkey, strlen($lastkey)-$k, 1))==false){
            $rows=(int)substr($lastkey, strlen($lastkey)-$k+1);
        }
    }

    $head = "";
    $data = "";
    for ($i = 0; $i< $columns; $i++){
        $head .=  $header[$i] . ",";
    }
    $arr = array();
    for ($j=2; $j<=$rows; $j++){
        $line = '';
        for ($m=1; $m<=$columns; $m++){
            /*    $value = $feed[convertIntToAlphabet($m)+$j]->value; */
            if(isset($feed[convertIntToAlphabet($m). (string)$j]))
            $value = $feed[convertIntToAlphabet($m). (string)$j]["value"];
            else
            $value = "";

            if ((!isset($value)) OR ($value == "")) {
                $value = ",";
            } else {
                $value = str_replace('"', '""', $value);
                $value = '"' . $value . '"' . ",";
            }
            $line .= $value;
        }
        $data .= trim($line)."\n";
    }
    $data = str_replace("\r", "", $data);
    echo "$head\n$data";
}

function convertAlphabetToInt($alpha_string) {
    $int_wert=0;
    $potenzcounter=0;
    for ($i=strlen($alpha_string);$i>0;$i--) {
        $ordinalwert=(ord(substr($alpha_string,$i-1,1))-64);
        $int_wert+=$ordinalwert*pow(26,$potenzcounter);
        $potenzcounter++;
    }
    return $int_wert;
}

function convertIntToAlphabet($int_wert) {
    $alpha_string="";
    if($int_wert%26>=1) {
        $alpha_string=chr(($int_wert%26)+64).$alpha_string;
        $alpha_string=convertIntToAlphabet($int_wert/26).$alpha_string;
    }
    return $alpha_string;
}</i></span>

The demo program - MAP123.html which interacts with the PHP file (ssheet.php) thru Ajax will need your GMail(or Google Apps account) id and password -

 

If successfully login, then it will show the list of available spreadsheets for you to choose -

Once you decide the spreadsheet to use, click at "get spreadsheet" button to retrieve, however, since the program does not offer you the choice of worksheets (Google Spreadsheets supports multi-worksheet), it will always grab the first available worksheet from the spreadsheet.

A spreadsheet of exact layout of original Google Spreadsheet will be displayed on top of the page!

The bottom part of the program is to display the Google map with the locations(addresses) information on the spreadsheet.As long as there is a header called "ADDRESS" the Google Map will be triggered and display all addresses on the "ADDRESS" column. Click at any row, it will diplay the information windows for the selected row on the Google Map.

You can try the demo program here, or you can download the whole files here.

Add A Google Map To XPages

A snapshot of the Lotus XPage embedded Google Map

The goal of this XPage practice is to add a web search function for a traditional Lotus CRM database with a embedded Google Map view. A Google map with address markers will be displayed under based upon the customers search result .

This is the original view of the Notes database which I used for the data source, the database need to be fully indexed for search function to perform correctly -

 

This XPage named "search4" was created under the same database and the layout of this XPage includes three Panels - first is for the search bar, second is for the View control and the third is for the Google Map canvas.

First Panel has two components, one is the "Edie Box" for the search string, we need to bind it to the "View Scope" and name it "searchString".

The second component is the search Button which need to add a "Simple Action" under its onClick event, then choose "Open Page" as the function, the name of the Page should be "computed" -
"search4.xsp?search=" + viewScope.searchString

The View (data grid) for the search result is named "viewPanel1" (default), I selected the data source from Domino view "location"

add following content at "search" field which should be a server side JavaScript(compute dynamically) -
context.getUrlParameter("search");

The third Panel is the easiest one which you just need to give it a name for Google Map API can access it, we named it "map" (actually, it become "view:_id1:map" on web page).

Make "dojoParseOnLoad" on XPage property as "true" since we need to use dojoAddOnLoad to load our JavaScript codes after page loaded.

There is a JavaScript library (geo4.js) needed inside this database script library. The purpose of this library is to dynamically load the Google Javascript header after page load , and to execute the Google API for populating the address markers on the map. I use the the Google Maps API geocoding service via the GClientGeocoder object to decode the address and create marker. To make my life easier, I did some hacks to avoid the javascript variable conversion which I still have problem dealing with, I had to manually find out the name of the table and div on the web page I need and hard-code them on the geo4.js (red section).

Following is the JavaScript codes - geo4.js stored at script library.

view plaincopy to clipboardprint?

1. var map;  
2.   
3. function loadScript() {  
4.   var script = document.createElement("script");  
5.   script.type = "text/javascript";  
6.   script.src = "http://maps.google.com/maps?file=api&v=2.x&key=your_google_api_key&async=2&callback=loadMap";  
7.   document.body.appendChild(script);  
8. }  
9.    
10. function loadMap() {  
11.   map = new GMap2(document.getElementById("view:_id1:map"));  
12.   map.setCenter(new GLatLng(38.4419, -102.1419), 4);  
13.   map.setUIToDefault();  
14.   mapMe();  
15.   }  
16.    
17. function mapMe() {  
18. var tb=document.getElementById("view:_id1:viewPanel1");  
19. var tr=tb.getElementsByTagName("tr");  
20.   
21. for (i=1; i<tr.length; i++)  
22. var td=tr[i].getElementsByTagName("td");  
23.   
24. var sp=td[4].getElementsByTagName("span");  
25. if (sp[0] != undefined)  
26. showAddress(sp[0].firstChild.data, map);  
27.   
28.   }  
29. }  
30.    
31.   function showAddress(address, m) {  
32.       var geocoder = new GClientGeocoder();  
33.       geocoder.getLatLng(  
34.         address,  
35.         function(point) {  
36.           if (!point) {  
37.            // alert(address + " not found");  
38.           } else {  
39.              
40.             var marker = new GMarker(point);  
41.             m.addOverlay(marker);  
42.                 var html = address;  
43.               GEvent.addListener(marker, "click", function() {  
44.              marker.openInfoWindowHtml(html);  
45.               })     
46.             //marker.openInfoWindowHtml(address);  
47.           }  
48.         }  
49.       );  
50.     }  
51.      
52.   function showAddressInfo(address, m) {  
53.       var geocoder = new GClientGeocoder();  
54.       geocoder.getLatLng(  
55.         address,  
56.         function(point) {  
57.           if (!point) {  
58.            // alert(address + " not found");  
59.           } else {  
60.              
61.             var marker = new GMarker(point);  
62.             m.addOverlay(marker);  
63.                 var html = address;  
64.               GEvent.addListener(marker, "click", function() {  
65.              marker.openInfoWindowHtml(html);  
66.               })     
67.             marker.openInfoWindowHtml(address);  
68.           }  
69.         }  
70.       )  
71.     }  
72.      
73.     dojo.addOnLoad(loadScript);  

var map;

function loadScript() {
  var script = document.createElement("script");
  script.type = "text/javascript";
  script.src = "http://maps.google.com/maps?file=api&v=2.x&key=your_google_api_key&async=2&callback=loadMap";
  document.body.appendChild(script);
}
 
function loadMap() {
  map = new GMap2(document.getElementById("view:_id1:map"));
  map.setCenter(new GLatLng(38.4419, -102.1419), 4);
  map.setUIToDefault();
  mapMe();
  }
 
function mapMe() {
var tb=document.getElementById("view:_id1:viewPanel1");
var tr=tb.getElementsByTagName("tr");

for (i=1; i<tr.length; i++)
var td=tr[i].getElementsByTagName("td");

var sp=td[4].getElementsByTagName("span");
if (sp[0] != undefined)
showAddress(sp[0].firstChild.data, map);

  }
}
 
  function showAddress(address, m) {
      var geocoder = new GClientGeocoder();
      geocoder.getLatLng(
        address,
        function(point) {
          if (!point) {
           // alert(address + " not found");
          } else {
           
            var marker = new GMarker(point);
            m.addOverlay(marker);
                var html = address;
              GEvent.addListener(marker, "click", function() {
             marker.openInfoWindowHtml(html);
              })   
            //marker.openInfoWindowHtml(address);
          }
        }
      );
    }
   
  function showAddressInfo(address, m) {
      var geocoder = new GClientGeocoder();
      geocoder.getLatLng(
        address,
        function(point) {
          if (!point) {
           // alert(address + " not found");
          } else {
           
            var marker = new GMarker(point);
            m.addOverlay(marker);
                var html = address;
              GEvent.addListener(marker, "click", function() {
             marker.openInfoWindowHtml(html);
              })   
            marker.openInfoWindowHtml(address);
          }
        }
      )
    }
   
    dojo.addOnLoad(loadScript);

You can download the design template of this Notes database by clicking here

PayPal 2.0 Certified Developer

 

Just passed the PayPal 2.0 Certified Developer exam couple days ago and received a congratulations email from PayPal who also put me on the list of PayPal Certified Directory! I have been using eBay API (Java) to integrate with Lotus Notes applications since 2001, my company was one of the certified eBay developers back then. Early last year due to a emergent custom project, I started to use PayPal API  (PHP). As a matter of fact, PalPal is an eBay company, therefore, some parts of PayPal API are very similar to eBay API, it sure helped me pick up the developing speed a lot! The PayPal Developer Certification needs to pass three exams - Website Payments Standard, Website Payments Pro and Payflow Gateway. I am very luck to become one of the six certified developers in New York area.

Install Lotus Domino Server 8.5.1 on IBM Cloud

Started from January 25, 2010 IBM Smart Business Development and Test on the IBM Cloud is opening a new Pilot Service for selected customers. Whoever with or without cloud experience should deem this as a great opportunity to be the first few people to taste IBM Cloud and it is FREE! Follow the instructions on front page you should be able to register and get the right to create your own application server(instance) on IBM cloud right away!

It is easy to create an instance on IBM cloud, even faster than Amazon EC2, however, there are not too many choices for OS, I chose SUSE Linux Enterprise v10 SP2 and Redhat Enterprise Linux (32-bit) v5.4 to begin with. Following video is the best tutorial to teach you how to connect to your own instances by SSH client: putty -


My goal is to install the latest Lotus Domino server 8.5.1 on IBM Cloud since there is no image pre-installed for Lotus Domino/Notes yet. After successful log in, you can type
sudo -i
groupadd notes
useradd -G notes notes
to have root access right, create "notes" user and "notes" group, assign "notes" user under "notes" group. Upload the Domino 8.51 installation file(lotus_domino851_xlinux_CZ5RWEN.tar) which I used WinSCP. Find the tmp directory to unzipped the tar file, then type "./install" to start the installation -

tar -xf lotus_domino851_xlinux_CZ5RWEN.tar
cd linux/domino
./install

I used all default choices to complete the install. You can always change to your own setting.

After server software installed, you need to use remote server installation to finalize the Domino server setup, I have uploaded a server id file since I want to use it to replicate big chunk of data to do my testing. Unlike the Amazon EC2, IBM Cloud doesn't support any tool for developers to control the ports. Unfortunately, the port 8585 for Domino remote installation is not open, therefore you have to assign a different port to run, I used port 8080. Or you can always post your request on support forum to ask, the original Domino port 1352 was not opened until somebody asked for it.

/opt/ibm/lotus/bin/server -listen 8080

Find a local Windows Domino server type following command under DOS console
nserver -remote
to start the remote server setup, close the listen job when finish.

After complete the setup, log into the instance by notes id, go to /local/notesdata (if this is your assigned data directory), the type

/opt/ibm/lotus/bin/server

Congratulations! You have your first Domino server up and running on IBM Cloud!

Type "top -u notes" to show all notes tasks.

A snapshot of the Cloud Domino server under Administrator view.

---

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part I