[Download] | [Documentation Home] | [Release Note]

Introduction


WSO2 Data Services Server provides the capability of exposing data from Google spread sheets. You can use either private or public google spread sheets as the data source. We can create google spread sheet datasources in two different modes.


Sample Google Spread Sheet


Following is a sample spread sheet which is hosted in https://spreadsheets.google.com/ccc?key=0Av5bU8aVtFjPdElrUVN3VmZlRkoyM1ZzVlE1MzdtbXc&hl=en


DS

Figure 1: Sample google spread sheet


Google SpreadSheet Data Sources in non-query Mode

Step 1: Start by giving a name


Lets begin to create the data service by clicking on "Create" link in left menu under Services/Add/Data Service. We will name the data service as "GoogleSpreadSheet" and click on "Next" to enter the details for the data source.


DS

Figure 2: Create data google spread sheet data service


Step 2: Enter details about your Google Spread Sheet


After giving a data source id select the data source type as Google Spread Sheet and enter the url for the sheet. Note that public visibility is supported when Google Spread Sheet is published on the web. To start publishing a spreadsheet to the web, select File > Publish to the web from the Spreadsheets user interface, and then click the Start Publishing button. Use the URL generated there as the URL of the sheet. If the spread sheet is Private you have to enter the user name and the password for the sheet. Click on "Save" once you done with entering the details.


DS

Figure 3: Configure data service


Step 3: Enter google spread sheet and query details using which values will be extracted.


Click on "Add New Query" to enter a new query. Lets name the query as "getCustomers" and select the data source which we created in the previous step. Enter the worksheet details and configure how the response look like.


DS

Figure 4: Add query


Step 4: Configure how the response should look like.


Lets create customer Number as element and customer name and city as attributes. Click on "Mian Configuration" once you enter the output mappings and "Save" the query details.


DS

Figure 5: Response configuration


Once you add a query, it will be listed as follows.


DS

Figure 6: View query


Step 5: Create an operation


Add a new operation and select the created query. Save the created operation.


DS

Figure 6: Add operation


Existing operations will be listed as follows. Click on "Finish".


DS

Figure 7: View operations


Step 6: Service deployed


You can find the deployed service under Deployed services list.


DS

Figure 8: Deployed services


Step 7: Try your service


Simplest way to invoke and check your created service is using "Try It" option provided in the service list. Click on "Try It" link and invoke the service by clicking on the "getCustomers" button.


DS

Figure 9: Invoke service using Try-it


Google SpreadSheet Data Sources in Query Mode

This functionality allows users to query an Google Spreadsheet in a much familiar SQL-like manner, and expose it as a service. Internally org.wso2.carbon.dataservices.sql.driver.TDriver class will be used as the SQL Driver. It is a JDBC driver implemenation to be used with tabular data models such as Google SpreadSheets, Excel sheets.

Step 1: Start by giving a name


As we did earlier lets start by creating a new data service. Let's name it as GoogleSpreadSheetQueryMode.


DS

Figure 10: Create data google spread sheet data service


Step 2: Enter details about your Google Spread Sheet


After giving a data source id select the data source type as 'Google Spreadsheet' and select "Use Query Mode" check box. Then enter the Google Spreadsheet name for 'SpreadSheet Name' and url for the sheet. Note that public visibility is supported when Google Spread Sheet is published on the web. To start publishing a spreadsheet to the web, select File > Publish to the web from the Spreadsheets user interface, and then click the Start Publishing button. Use the URL generated there as the URL of the sheet. If the spread sheet is Private you have to enter the user name and the password for the sheet. Click on "Save" once you done with entering the details.


DS

Figure 11: Configure data service


As we did earlier click on "Next" to create the query.

Step 3: Define the Query, input parameters and output parameters.


Lets name the Query Id as googleQuery and select the data source which created in previous step. You can see that UI will allow you to define the query, which was not supported earlier. Enter the query as given in the below UI and click on 'Generate Input Mappings' to auto generate the input mapping parameters.


DS

Figure 12: Add query


Once you add a query, click on "Next" to create the operation.


Step 4: Create a web service operation


Click on "Add on Operation", and enter a name to the operation. Lets name it as insertCustomers. Select the created query from the drop down list and click on 'Add Query Params as Operation Params' to add the parmeters to operation. Now save the operation.


DS

Figure 13: Add operation


Now Try-it the service and check the Google Spreadsheet for the newly added records.

Supported query types

Currently, this feature provides support for basic SELECT, INSERT, UPDATE and DELETE functionalities.

Note : Nested quries are currently not supported


Eg:

SELECT customerNumber,customerName,phone,state,country FROM customers

Sample SELECT query

INSERT INTO customers(customerNumber,customerName,contactLastName) VALUES(?,?,?)

Sample INSERT query

UPDATE customers SET contactFirstName=?, contactLastName=? WHERE customerNumber=?

Sample UPDATE query

DELETE FROM customers WHERE customerNumber=?

Sample DELETE query

Also we can create new Sheets and drop an existing sheet from a Google Spreadsheet.


Eg:

CREATE SHEET ProductCategories (ProductCode, Category)

Sample SELECT query

DROP SHEET ProductCategories

Sample INSERT query