Google Spreadsheet

You can create data sources to expose data in Google spreadsheets as services using the WSO2 Data Services Server. To begin, follow the steps in Creating Data Sources. When you select Google Spreadsheet as the data source type at the time a data source is created, the Google-specific options appear. For example: 

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 Passwords.

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 data sources in two different ways as follows:

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

Google data sources in non-query mode 

To create a data source 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 data sources in query mode

In the query mode, users can query a Google Spreadsheet in a much familiar SQL-like manner. To create a data source 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 basic SELECT, INSERT, UPDATE and DELETE queries. 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 sample query to this data source, see Writing Data Service Queries.

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