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, proceed with the steps given below.

1. Enter a datasource ID in the first field of the New Datasource screen. 
2. Then select Google Spreadsheet for the Datasource Type field. The Google-specific options will now be available for editing as shown below.
   
3. 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.
4. In the Google Spreadsheet URL field, enter the URL of the google spreadsheet that you want to expose as a service.
   
5. The Visibility field specifies whether the spreadsheet should be private or public:
   

   • Public visibility is supported when a Google spreadsheet is published on the Web. To publish a spreadsheet, select File > Publish to the web from the spreadsheet's user interface. Use the spreadsheet's URL for the Google Spreadsheet URL field.

     Note that Public visibility can only be used if the Use Query Mode check box is not selected. This also means that you cannot add or modify the data in the spreadsheet. That is, you can only get data from a public spreadsheet.

   • When you set the visibility to Private, you are asked to provide credentials as well as a browser redirect URL that is known to your browser. 
     

     Client ID, Client Secret and Refresh Token

     Google no longer supports authentication through username and password. Therefore, it is now necessary to provide credentials in the form of a Client ID, Client Secret and Refresh Token as shown above. Given below are the steps you need to follow in order to obtain client credentials from google and to use them in your data service.

     1. See the google documentation for instructions on how to get an OAuth2 client ID and client secret. In order to get these credentials, you will be asked to provide an authorization redirect URL. Be sure to use the same URL as the browser redirect URL shown above. 

        Setting the hostname

        Note that the Redirect URI should contain the same host name as the Authorized Redirect URl that you provided in the previous step, as well as the host on which the management console runs.

        • If the server is running on your machine, you can simply use "localhost" as the hostname (or the direct IP address, which is 127.0.0.1). 

        • If the server is running on a local network, you must always use a host name instead of the direct IP address. This is because publicly shared IPs cannot be used. You also need to ensure that the hostname you use is known to the browser by registering it in your "/etc/hosts" file.

     2. Now you need to update the New Datasource screen with the Client ID and the Client Secret. When you click Generate Token, you will be redirected to the google consent page. After you approve that, the refresh token will be inserted into the New Datasource screen automatically as shown below. Note that we just store the refresh token because the access token is going to expire anyway.
        
6. Save the datasource.

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 Step 3: Writing Data Service Queries.

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