Google Spreadsheet

You can create datasources to expose data in Google spreadsheets as services using the WSO2 Data Services Server. To begin, follow the steps from 1 to 3 in creating a data service using various data sources. When you get to the Add New Data Source screen, select Google Spreadsheet as the data source type. The Google-specific options will now be available for editing as shown below.

The example above uses a private Google spreadsheet. When you select Visibility as private, you are asked to provide credentials. You can also tick the Use as a Secret Alias option to specify an alias name for the password instead of giving the actual password. For more information, see Securing Datasource Password.

Note that public visibility is supported when a Google spread sheet is published on the Web. To publish a spreadsheet, select File > Publish to the web from the Spreadsheet's user interface. Use the URL generated there as the Google spreadsheet URL here. 

The Use Query Mode option (in the UI above) allows you to create Google spreadsheet datasources in two different ways as follows:

• Google datasources in Non-Query Mode : Allows to directly expose the contents of a Google spreadsheet as a service
• Google datasources in Query Mode : Allows to query a Google spreadsheet in a familiar, SQL-like manner, and expose the results set as a service

Google datasources in non-query mode 

To create a datasource in non-query mode, simply uncheck the Use Query Mode check box and save. Then, click Next to add a query. Note that the Query Details UI does not provide support to write a query. You can simply add Output Mappings to define how the output looks like. For example,

Click Add New Output Mapping to define how the output looks like. In this example, we create Customer Number as element and Customer Name and City as attributes.

Google datasources in query mode

In the query mode, users can query a Google Spreadsheet in a much familiar SQL-like manner. To create a datasource in query mode, check the Use Query Mode check box and save. Then, click Next to add a query. Note that the Query Details UI now provides support to write a query. For example,

To implement the query mode, internally the org.wso2.carbon.dataservices.sql.driver.TDriver class is used as the SQL Driver. It is a JDBC driver implementation to be used with tabular data models such as Google SpreadSheets, Excel sheets etc.

At the moment, the query mode supports only the basic SELECT, INSERT, UPDATE and DELETE queries. Note that the Google spreadsheet sql driver does not accept the SELECT * command. Therefore, all the required select options (e.g., column names) should be specified in the query. See the following example: <sql>SELECT employeeId,name,salary FROM Sheet1 WHERE employeeId = ?</sql>.

Nested queries will be supported in an upcoming release.

Given below are few examples of the supported queries:

Queries supported in the query mode

Sample 1:

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

Sample 2:

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

Sample 3:

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

Sample 4:

DELETE FROM customers
WHERE customerNumber=?

You can also create new sheets in the Excel or drop existing sheets.

Sample 5:

CREATE SHEET ProductCategories (ProductCode, Category)

Sample 6:

DROP SHEET ProductCategories

To write a query to this datasource, see Writing Data Service Queries.

Also, see a demonstration of service enabling a Google spreadsheet in Google Spreadsheet Sample.