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.
   

2. Start the ESB profile:

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

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.

   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 .

      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.

   Open

   

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.

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.