This documentation is for WSO2 Enterprise Integrator version 6.0.0 . View documentation for the latest release.
Skip to end of metadata
Go to start of metadata

This tutorial will guide you on how to expose data in a google spreadsheet as a data service.

See the following topics for instructions:


Start the Create New Data Service wizard

Now, let's start creating the data service from scratch:

Add a google spreadsheet datasource

You can add a google spreadsheet as the datasource by following the steps given below.

  1. Click Add New Datasource to open the following screen.
  2. Follow the instructions below to fill the datasource details.

    1. In the Datasource Id field, enter GoogleSpreadsheet as the value.

    2. In the Datasource Type field, specify the type of datasource for which the data service is created. Select Google Spreadsheet from the list. You will now get the following screen:

    3. In the Google Spreadsheet URL field, specify the path to your spreadsheet. The file we are using for this tutorial is https://docs.google.com/spreadsheets/d/1o1pCmMFcbWZ_eJ54ymcb486GctTdsR6b4kFERmKrR1w/edit?hl=en&hl=en#gid=0.

      Note that this is a private spreadsheet, which is not published on the web. If you want to use a public spreadsheet, see the topic on exposing a public spreadsheet as a data service.

    4. Select a value for the Visibility field based on whether the spreadsheet is private or public. Since we have used a private spreadsheet in this example, set the visibility to PrivateYou are asked to provide credentials as well as a browser redirect URL that is known to your browser. Google no longer supports authentication through username and password. Therefore, it is now necessary to provide credentials in the form of a Client IDClient Secret and Refresh Token.

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

    6. Update the Client ID and the Client Secret fields in the Edit Datasource screen with the values you got in the previous step.

    7. Click Generate Token. You will now be redirected to the google consent page. After you approve that, the refresh token will be inserted into the New Datasource screen automatically. Note that we just store the refresh token because the access token is going to expire anyway.

    8. The Use Query Mode option allows you to write queries for the spreadsheet in two different modes:

      • Non-Query mode: Allows you to directly expose the contents of a Google spreadsheet as a service.

      • Query mode: Allows you to query a Google spreadsheet in a familiar, SQL-like manner, and expose the result as a service. You need to provide the name of the spreadsheet, in addition to the spreadsheet URL.

      See the next section on defining queries for more information on how the query mode affects how you query data in the spreadsheet.

  3. Save the datasource.
  4. Click Next to go to the Queries screen

Define a query for the datasource

You can define queries in two ways for a google spreadsheet, depending on whether or not Query mode is enabled for the datasource.

If query mode is disabled

If query mode is disabled for the spreadsheet, you cannot use SQL statements to query data in the spreadsheet. Note that in non-query mode, you can only get data from the spreadsheet and you cannot insert, update or modify any data. 

  1. Click Add New Query to open the Add New Query screen.
  2. Enter Q1 as the query id in the Query ID field.
  3. In the Datasource field, select the datasource for which you are going to write a query. Select the datasource for the Google spreadsheet that you created previously.

  4. You can directly specify the details of the spreadsheet as shown below.
  5. Define Output Mapping: Now, let's specify how the data fetched from the datasource should be displayed in the output. The google spreadsheet we are using contains several columns with customer data. We will create output mappings for the following columns: IDCustomerNumberCustomerName and City.
    1. In the Output type field, specify the format in which the query results should be presented. You can select XML, JSON or RDF. We will use XML for this tutorial.
    2. In the Grouped by element field, specify a grouping for all the output mappings. This will be the XML element that will group the query result. Enter Customers in this field. 
    3. In the Row Name field, specify the XML element that should group each individual result.  Enter Customer in this field.
    4. Click Add New Output Mapping to start creating the output mapping for the customerNumber column. Enter values as shown below:
      add the output mapping details
    5. Click Add to save the output mapping. You will now have one output mapping listed for the query.
    6. Now, add output mappings for the following:

      Mapping TypeElement NameDatasource TypeDatasource Column NameParameter TypeSchema Type
      elementcustomerNumbercolumncustomerNumberSCALARstring
      attributecustomerNamecolumncustomerNameSCALARstring
      attributecitycolumncitySCALARstring

      You will now have the following output mappings listed for the query:

      list of existing output mappings

    Find out more about defining Output Mappings.

  6. Click Next to go to the Operations screen.

If query mode is enabled

You can query data in the spreadsheet using SQL statements.  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. 

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

  1. Click Add New Query to open the Add New Query screen.
  2. Enter Q1 as the query id in the Query ID field.
  3. In the Datasource field, select the datasource for which you are going to write a query. Select the datasource for the Google spreadsheet that you created previously.

  4. Specify an SQL query to insert data into the spreadsheet.

    INSERT INTO customers (customerNumber, customerName, city)
    VALUES(?,?,?)
  5. Define Input Mapping: You need to create input mappings for the customerNumber, customerName and city columns specified in the above SQL statement. These input mapping parameters will be used for inserting data into the relevant columns. Click Generate Input Mapping, and default input mappings will be generated for all the fields that you have specified in your SQL statement as shown below.

    Find out more about defining Input Mappings.

  6. Click Next to go to the Operations screen.

Define an operation to invoke the query

Follow the steps given below.

  1. Click Add New Operation to open the Add New Operation screen.
  2. In the Operation Name field, enter a name for the operation.
  3. In the Query ID field, select the query that you created.
  4. Save the operation.

Finish creating the data service

Once you have defined the operation, click Finish to complete the data service creation process. You will now be taken to the Deployed Services screen, which shows all the data services deployed on the server.


Invoking your data service

You can try the data service you created by using the TryIt tool that is in your product by default.

  1. Go to the Deployed Services screen.
  2. Click the Try this service link for the GSpread data service that you just created. The TryIt tool will open with the GSpread service.
  3. Select the operation your created earlier and click Send.
    • If you are invoking a query that can insert data into the spreadsheet, you will need to specify the values that should be inserted before clicking Send. 
    • If you are invoking a query that can fetch data from the spreadsheet, the relevant data will be published when you click Send.

 

Exposing a public spreadsheet as a data service

A google spreadsheet can be a private spreadsheet or a publicly exposed (published to the web) spreadsheet. The tutorial given above uses a private spreadsheet.

Note the following if you are using a public spreadsheet:

  • Before you begin, the spreadsheet is required to be published to 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.
  • When you come to the Edit Datasource step in the Create New Data Service wizard, the Use Query Mode check box should be cleared as it is not possible to define SQL-like queries for a public spreadsheet. 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. You can follow the same instructions given in the section for defining a query in 'non-query' mode (in the above tutorial) to define the query for a public spreadsheet.
  • No labels