Exposing a Google Spreadsheet as a Data Service

This tutorial guides you on how to expose data in a Google spreadsheet as a data service. 

See the following topics for instructions.  Also, see the samples in  Data Integration Samples.

Start the Create New Data Service wizard

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

1. Download the product installer from here, and run the installer.
   Let's call the installation location of your product the <EI_HOME> directory.

   If you installed the product using the installer, this is located in a place specific to your OS as shown below:

   OS	Home directory
   Mac OS	/Library/WSO2/EnterpriseIntegrator/6.5.0
   Windows	C:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\
   Ubuntu	/usr/lib/wso2/EnterpriseIntegrator/6.5.0
   CentOS	/usr/lib64/EnterpriseIntegrator/6.5.0

2. Start the ESB profile:

3. Access the management console of the ESB profile: https://localhost:9443/carbon/.

   For the purpose of this tutorial, be sure to use localhost as the IP in the above URL.

4. Sign in using admin as the username and password.

5. Click Create under the Data Service menu to open the Create Data Service wizard.

6. Enter GSpread as the data service name as shown below. Leave the default values for the other fields.
   

7. Click Next to go to the Datasources screen.
   

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, Select  Google Spreadsheet  from the list. 
      This specifies the type of datasource for which the data service is created. You will now get the following screen:
      

   3. In the Google Spreadsheet URL field, specify https://docs.google.com/spreadsheets/d/1o1pCmMFcbWZ_eJ54ymcb486GctTdsR6b4kFERmKrR1w/edit?hl=en&hl=en#gid=0 as the path to your spreadsheet.

      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.

      You can use your own private spreadsheet too.

   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 Private. 
      
      You are asked to provide credentials. Google no longer supports authentication through username and password. Therefore, you need to provide credentials in the form of a Client ID, Client Secret, and Refresh Token.

   5. Get the Client ID and Client Secret:

      1. Go to google documentation and click CONFIGURE A PROJECT.

      2. Expand the drop-down, select a project you have already created or create a new project.

      3. On the Configure your OAuth client window, select Web Browser.

      4. Enter the URL given for Redirect URI in the management console as the value for  Authorized Redirect URIs .

         Setting the hostname

         Note that the Redirect URI should contain the same hostname 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 hostname 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.

      5. Click CREATE.

      6. Copy the Client ID and Client Secret, and enter them in the management console.
         

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

   7. Click Test Connection to ensure that the configured works as expected.
      If you get a successful message, your configurations are correct. If you get an error, make sure you configure the fields as explained in the document.

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

      In this tutorial, we use the Non-Query mode. Therefore, don't select Use Query Mode.

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

Define a query for the datasource

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.

   Worksheet Number	1 You want the query to get the data from Sheet1 in the Google spreadsheet.
   Start reading from	2   You enter 2 because the 1st row of the spreadsheet is the header. The data is available from row 2 onwards.
   Rows to read	5 The data in this row will be fetched by the query.
   Headers available	true
   Header row	1   You enter 1 because the 1st row of the spreadsheet includes the header.

   

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: ID, CustomerNumber, CustomerName, and City.

   Output type	Select XML. You can select XML, JSON, or RDF. We will use XML for this tutorial.
   Grouped by element	Enter Customers. This is the XML element that groups the query result.
   Row Name	Enter Customer. This is the XML element that groups each individual result.
   Add New Output Mapping	Follow the steps given below to add a new output mapping: Click Add New Output Mapping  to start creating the output mapping for the  customerNumber  column. Enter values as shown below: Mapping Type Element Name Datasource Type Datasource Column Name Parameter Type Schema Type element customerNumber column customerNumber SCALAR string Click Add to save the output mapping. You will now have one output mapping listed for the query. Now, add output mappings for the following: Mapping Type Output Filed Name Datasource Type Datasource Column Name Parameter Type Schema Type attribute customerName column customerName SCALAR string attribute city column city SCALAR string You will now have the following output mappings listed for the query:

6. Click Main Configuration to return to the Query screen.
7. Click Save.
8. 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 CustomerDetails as the name for the operation.
3. In the Query ID  field, select Q1. This is the query that you created under Define a query for the datasource.
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 CustomerDetails operation your created earlier and click Send.
   You will see the data displayed.