[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
Figure 1: Sample google spread sheet
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.
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.
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.
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.
Figure 5: Response configuration
Once you add a query, it will be listed as follows.
Figure 6: View query
Step 5: Create an operation
Add a new operation and select the created query. Save the created operation.
Figure 6: Add operation
Existing operations will be listed as follows. Click on "Finish".
Figure 7: View operations
Step 6: Service deployed
You can find the deployed service under Deployed services list.
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.
Figure 9: Invoke service using Try-it
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.
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.
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.
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.
Figure 13: Add operation
Now Try-it the service and check the Google Spreadsheet for the newly added records.
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
INSERT INTO customers(customerNumber,customerName,contactLastName) VALUES(?,?,?)
UPDATE customers SET contactFirstName=?, contactLastName=? WHERE customerNumber=?
DELETE FROM customers WHERE customerNumber=?
Also we can create new Sheets and drop an existing sheet from a Google Spreadsheet.
Eg:
CREATE SHEET ProductCategories (ProductCode, Category)
DROP SHEET ProductCategories