Thursday, July 12, 2012

How to create a Data service from Google Spread sheet


Google Spread sheets are popular tool and used in businesses heavily. Imagine a usecase where you need to collect data from various people and then use that data in your applications. Most of the time 5 minute work would be to create a Google Spread sheet and design a form to share among the people to collect the data. Once you have the collected data you may need to use them in your applications. WSO2 Data Services provide you a very convenient way to expose those data as a data service.


1) WSO2 Data Service Server 2.6.3
2) Online Google Spread sheet

Starting the server.

Extract Data Services Server and lets consider root folder as <DSS_HOME>.
navigate to <DSS_HOME>/bin folder and in linux run (If you are in windows use wso2server.bat)
Login to server using browser (default URL : https://localhost:9443)

Creating the Data Service.

Click on add → Data Service → create. ( you will get the following panel to add a name)

  • Create the data source from Google Spread sheet.

 (If you do not have your own one lets use the following online spreadsheet i have created.)

DataSource id gspreadTest
DataSoure TypeGoogle Spread Sheet
Google Spreadsheet URL

  • After completing click on Test Connection to verify the data source you have created. If it is successful you should get the following success dialog.

  • Create Query

For the query you need to define following parameters.

Query IDgetAllExpensesAn unique id to identify the query
Data SourcegspredTestData source we created in previous step
Worksheet no1identification no of the excel working sheet. (from sheet1, sheet2... etc.)
Start Reading from2From which row you are starting reading
Rows to read-1if it is (-1) it will read all the rows. Otherwise define the no of rows need to be read.
Headers availabletrueif yes. it will identify column headers for output mapping. Otherwise you need to define column number.

  • Create output mappings to columns of Google Spread sheet.

click on “Add new output mapping” and fill the for all the columns in the spreadsheet as we are going to extract all of them through this data service.

  • Add Grouped By element : Expenses and Row name : Expense(In the result it will appear by those given names.)

<Expenses xmlns="">
       <TimeStamp>3/13/2012 11:45:09</TimeStamp>

  • After adding all of them in the “main Configuration” you can view the added output mappings.

  • Create a operation to Call the query.

  • Then save and click → finish to deploy the data service.
  • Go to Service Management ( Click → List on left panel) and refresh after few seconds to view your deployed service.
  • Click on try it to test you service.


  1. This comment has been removed by the author.

  2. Lookin for space? The SanDisk 2TB Pro SSD is an external USB 3.0 flash drive that is powered by SanDisk's latest SandForce SF-2281 controller. It is one of the fastest drives available in the market.

    The SanDisk 2TB Pro Black Friday comes with a USB 3.0 interface and has a capacity of 2TB. The device is available in two colors, black and white. It comes with a three year warranty.

    The SanDisk 2TB Pro is an external drive that has been specially designed to provide excellent performance for heavy data users. You can use it to store large amounts of data and transfer files quickly. It is the perfect external storage solution for any user who needs to store lots of photos, videos, music, and documents.