Writing a Gadget which uses a Google Spreadsheet as a data source

Introduction

Google spreadsheets are being used extensively by many organizations to store their business data. Google spreadsheets provide facilities to track budgets, run financial calculations and do many more things with data. Therefore Google spreadsheets have the ability to be used as data sources for business applications such as Gadgets which can be deployed into an enterprise gadget server like WSO2 Gadget Server. 

Things to have...

• Basic knowledge on how to write a Google Gadget. (Google Gadgets API Developer's Guide) 
• Optional: Read Authoring, deploying and using XML Gadgets in WSO2 Gadget Server by Nuwan Bandara
• WSO2 Gadget Server

What is a Gadget

                                   

Gadgets are simple XML,HTML and JavaScript applications that can be embedded in web pages and other applications which presents information that is useful to the user in a small space. Any developer with a basic knowledge about XML, HTML and JavaScript can author a gadget and deploy it into an Enterprise Gadget server such as WSO2 Gadget Server or a Gadget portal such as iGoogle.

Google Spreadsheets

Google Spreadsheets are online spreadsheets which can be access by any place and also can be shared between various people. Therefore these are used heavily in organizations for many purposes such as to track budgets, run financial calculations and do many more things with data. You can find more details about Google Spreadsheets and how to use them form here.

  

How to use WSO2 Gadget Server

You can download WSO2 Gadget Server from WSO2 Oxygen Tank. For a complete installation guide please refer WSO2 Gadget Server - Installation Guide. For details about adding gadgets to the portal of WSO2 Gadget Server please refer Adding Gadgets to the Portal.

 

Using the Spreadsheet as the Data Source for Google Visualization API

The URL of the Spreadsheet is required if a developer is going to use a Google Spreadsheet as a Data Source. Depending on the view privileges, the access to the spreadsheet will be determined. Therefore when developing gadgets which interacts with Google Spreadsheets developers has to consider about the access level of the spreadsheet that is being used. That is if the spreadsheet has been published to the web anyone can access it through a gadget and if only a limited number of people have view privileges only those will be able to access the spreadsheet through a gadget.

In this article a published spreadsheet  (Gross Agricultural Production in Country X) which contains some sample Agricultural Production data about a country will be used.

First Step: Load the libraries

A gadget which uses Google Visualization API with a Spreadsheet data source require two libraries. They should be loaded before making requests for data. The libraries are loaded using two separate <script> tags as follows.

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
          google.load('visualization', '1', {packages: ['corechart']});
</script>

The first <script> tag loads Google JSAPI library which is used to load API’s associated with Google (in this example the Visualization API).

The second script loads the Visualization API(Google Visualization and chart libraries) which is used to query the Spreadsheet data source and to draw the charts. The line google.load() loads the Visualization library. As the parameters the version of the visualization library (1) and the required packages of Google Chart libraries are given. Only those libraries can be used within the gadget. The ‘corechart’ loads the basic chart libraries which is used to draw pie,bar and column charts. Please refer Loading the libraries for more details.

Second Step: Query the Spreadsheet

As the next step a query is made to retrieve the data from the spreadsheet as follows. This should be included into a separate <script> tag (third).

function querySpreadsheet() {
    var query = new google.visualization.Query(‘URL of the Spreadsheet’);          
     // Send the query with a callback function.
    query.send(handleQueryResponse);
}

google.visualization.Query() creates the query with the Data Source URL. The query can contain optional parameters such as the required sheet. More details can be found here.

After initiating the query it is send with the callback function which is responsible for handling the response.

This method should be called when the gadget is loaded. It is done as follows.

gadgets.util.registerOnLoadHandler(querySpreadsheet);

Third Step : Handling the response

When a response is returned it will be handled by the callback method (handleQueryResponse) defined in the query.send() method in the querySpreadsheet() function. 

function handleQueryResponse(response) {
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' +    
            response.getDetailedMessage());
        return;
    }    
    var data = response.getDataTable();
    var visualization = new    google.visualization.ColumnChart(document.getElementById('visualization'));
    visualization.draw(data, {
        title:"Gross Aggricultural Production of Country X", 
        width:600, 
        height:400,
        hAxis: {
            title: "Year"
        }
    }
    );
}

First it will check for errors in the response and if there is an error it  will be displayed in an alert dialog.

If the response does not contain an error, the DataTable returned by the data source will be assigned to the variable ‘data’.

var data = response.getDataTable();

This DataTable object contains a two-dimensional table which can be formatted according to the users requirement using the methods of that object. Further details about DataTable class can be found here.

After creating the data table object, Google visualization library uses it to draw a Column Chart. First a ColumnChart object  is created with an ‘div’ element (visualization)  from the gadgets HTML body which will be used for drawing the chart.

var visualization = new google.visualization.ColumnChart(document.getElementById('visualization'));

After creating the object, ‘draw’ method is invoked with the data and parameters about the chart. This method will draw the chart inside the ‘div’ section defined earlier. Title of the chart, title for the horizontal axis and dimensions of the chart are the parameters given to the method.

visualization.draw(data,
    {
        title:"Gross Agricultural Production of Country X", 
        width:600, 
        height:400,
        hAxis: {
            title: "Year"
        }
    }
);

Note: If the above method is used DataTable object (data) should be compatible with chart type being used. Therefore Spreadsheet data has been formatted to be compatible with the Column Chart in Google Charts. So the first column contains years as plain text. Also the column headers are of string type. Also this can be achieved by creating a new DataTable object using the older object according to the required format, inside the Gadget Source.

  

Final Step: Creating the div element for the chart

Finally a div element should be defined to draw the chart as follows.

<body style="font-family: Arial;border: 0 none;">
    <div id="visualization" style="height: 400px; width: 400px;"></div>
</body>

The complete gadget source is as follows.

<?xml version="1.0" encoding="UTF-8"?>
<Module>
    <ModulePrefs title="Google Spredsheet Sample Gadget"
                 description="A Gadget which uses a Google Spreadsheet as a Data Source"
                 author="Sumedha" author_email="sumedhas@wso2.com"
                 author_link="http://www.wso2.org/"
                 title_url="http://www.wso2.org/"
                 height="400">
        <Require feature="dynamic-height"/>
    </ModulePrefs>
    <Content type="html"><![CDATA[
        <script type="text/javascript" src="http://www.google.com/jsapi"></script>
        <script type="text/javascript">
            google.load('visualization', '1', {packages: ['corechart']});
        </script>
        <script type="text/javascript">
            gadgets.util.registerOnLoadHandler(querySpreadsheet);

            function querySpreadsheet() {
                var query = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=0Aio3Y5QAMdBBdHU2R0hfcXkwSlNBbFhQeTMzX25VLVE&hl=en_US');
                // Send the query with a callback function.
                query.send(handleQueryResponse);
            }
    
            function handleQueryResponse(response) {
                if (response.isError()) {
                    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
                    return;
                }
                var data = response.getDataTable();
                var visualization = new google.visualization.ColumnChart(document.getElementById('visualization'));
                visualization.draw(data,
                                    {title:"Gross Aggricultural Production of Country X",
                                    width:450, height:400,
                                    hAxis: {title: "Year"}}
                                );
            }      
        </script>
        <body style="font-family: Arial;border: 0 none;">
            <div id="visualization" style="height: 400px; width: 400px;"></div>
        </body>
  ]]>
    </Content>
</Module>

Using this link the gadget can be added directly to the portal.

The completed gadget looks as follows.