Google Spreadsheet

Google Spreadsheet datasources allow you to create a data service using a Google spreadsheet as the data storage type.

You can get the Add New Datasource screen shown below on the management console by executing steps 1 to 3 in creating a data service. Then, select Google Spreadsheet as the Datasource Type. It opens the spreadsheet-specific options 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 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 query to this data source, see Writing Data Service Queries.

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