Google Spreadsheet

Follow the instructions below to create a data service using a sample Google spread sheet as the data source. 

Google data sources can be created in two different modes as follows:

• Google Data Sources in Non-Query Mode : The 'Non-Query' mode allows to simply expose the content of a Google spreadsheet as a service.
• Google Data Sources in Query Mode : The 'Query Mode' allows users to query a Google spreadsheet in a familiar, SQL-like manner, and expose it as a service.

Google Data Sources in Non-Query Mode 

1. Log on to the product's management console and select   "Data Service -> Create" under the "Main" menu.

2. The "Create Data Service" page appears. Fill in the fields and click "Next" . The Data Service name is mandatory.

3. The "Data Sources" page appears. Click on the "Add New Data Source" link and add your Data Source details. Select the data source type as "Google Spread Sheet ".

You can use either private or public Google spread sheets as the data source. Note that public visibility is supported when a Google spread sheet is published on the web. To publish a spreadsheet to the web, select "File > Publish to the web" from the Spreadsheet's 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 will be prompted to enter the user name and the password. For example,

Click "Save" . You have added a Data Source to your Web Service. Click "Next" to proceed.

4. The "Queries" page appears.

Initially your service does not include any queries. Click on "Add New Query" to add a new query, input/output mappings, events etc. to your Data Service. Enter query details according to the structure of the response you want.

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

Click on "Main Configuration" once you enter the output mappings and "Save" the query details. For information on adding validations to input mappings in the query, if any, refer to section Input Validators . Detailed information on writing queries can be found in section Data Service Queries.

5. After saving, the added data source page appears. You can edit or delete the Data Source. To proceed click "Next ".

6. The "Operations" page appear s. Click the "Add new operation" link to c reate an operation. Select a query to be executed when this operation is called. For example,

Click "Save" once done.

7. The operation will be listed in the "Operations" page.

The Data Service configuration is done. Click "Finish" . ( You can continue to add resources by clicking the "Next" button if you like).

8. You will be navigated to the "Deployed Services" page .

From here, you can manage your data service. For instructions, refer to section Managing Data Services . For a demonstration on the usage of Google Spreadsheets as a data source, refer to Google Spreadsheet Sample . 

Google Data Sources in Query Mode

In the query mode, users can query a Google Spreadsheet in a much familiar SQL-like manner. 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.

Follow the instructions below to add a data source in the query mode.

1. Log on to the product's management console and select   "Data Service -> Create" under the "Main" menu.

2. The "Create Data Service" page appears. Fill in the fields and click "Next" . The Data Service name is mandatory.

3. The "Data Sources" page appears. Click on the "Add New Data Source" link and add your Data Source details. Select the data source type as "Google Spread Sheet ". Make sure you select the "Use Query Mode" check box.

You can use either private or public Google spread sheets as the data source. Note that public visibility is supported when a Google spread sheet is published on the web. To publish a spreadsheet to the web, select "File > Publish to the web" from the Spreadsheet's 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 will be prompted to enter the user name and the password. For example,

4. The "Queries" page appears.

Initially your service does not include any queries. Click on "Add New Query" to add a new query, input/output mappings, events etc. to your Data Service. Enter query details according to the structure of the response you want.

Currently, the Query Mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. Nested queries are not supported at the moment but will be facilitated in an upcoming release. For more information, refer to supported queries .

5. Once done, click "Next" to add operations.

6. Click on "Add Operation", and enter a name to the operation. Select the created query from the drop down list and click on "Add Query Params as Operation Params" to add the parameters to operation. Now save the operation. For example,

Queries Supported in the 'Query' Mode

Currently, only basic SELECT, INSERT, UPDATE and DELETE queries are supported by the 'Query' mode. Shown below are few example queries.

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