Connectors – Google Sheets

Sigma enables the users to connect with the Google sheet data using Connectors in Sigma Studio.

Prerequisites
To create Google sheet connections, make sure that the provided GSheet_jars folder is present within the Sigma and the Studio war.

Sample File Location for Sigma Studio in Tomcat Server:
<Tomcat-Installed-Folder> \webapps\expertctstudio\WEB-INF\lib
Sample File Location for Sigma in Tomcat Server:
<Tomcat-Installed-Folder> \webapps\modelhouse\WEB-INF\lib

It is important to possess the following credentials for creating a Google sheet connection:

1. Client ID
   
2. Client Secret
3. Access Token
4. Refresh Token

Perform the following steps in order to obtain the Client ID and the Client Secret information:

1. Go to the following URL link:
   https://console.developers.google.com
   
    
2. Click Enable APIS and Services.
   
   
3. Search for Google Sheets API in the search box and select it.
   
   
4. Click Manage.
   
   
5. Click Credentials option from the menu on the left-hand side of the page.
   
   
   
6. Click Create Credentials and select OAuth Client ID from the drop-down list.
   
   
7. Click the Application Type drop-down and select Web Application.
   
   
8. Enter the web application name in Name field. Here, Web client is provided as the name.
   
   
9. Click ADD URI in Authorized JavaScript origins field and provide https://console.developers.google.com as the URI.
   
   
10. Click ADD URI in Authorized redirect URIs field and provide https://developers.google.com/oauthplayground as the URI.
    
    
11. Click Create.
    
    
12. Make note of the Client ID and the Client Secret information from the pop up that appears once the OAuth client is created.

Now, perform the following steps in order to obtain the Access and the Refresh Tokens:

1. Go to the following URL link:
   https://developers.google.com/oauthplayground/
   
   
2. Click the settings ( ) icon.
   
   
3. Toggle on the Use your own OAuth credentials checkbox.
   
   
   
4. Provide Client ID and Client Secret information on the OAuth Client ID and the OAuth Client Secret fields.
   
   
5. Now, on the left-hand side of the page, in Step 1, click Google Sheets API v4 and select the first option.
   
   
6. Copy the first option URL https://www.googleapis.com/auth/drive and paste it on the Input your own scopes filed and click Authorize APIs.
   
   
7. Now, choose your Google account and click Allow in order to provide permission to read Google sheets from the account's Google drive.
   
   
8. Now, in step 2, click Exchange the authorization code for access and refresh tokens.
   
   
9. Make note of the access and the refresh tokens.
   
   

Perform the following steps in order to create a connection and a datasource for the Google sheet:

1. In Sigma Studio, click Manage > Connection.
   
   
   The Create New Connection page appears as shown in the following screenshot:
   
   
2. Click OTHERS and select the Google Sheets.
   
   
3. Enter a unique name for the connection in the Connection Name field.
4. Provide the obtained client ID in Client ID field.
5. Enter the obtained client secret in Client Secret field.
6. Similarly, provide the access and the refresh tokens in the Access Token and Refresh Token fields respectively.
   
   

7. Click Submit to save the connection.
   

   When you try to exit the Connectors screen, before saving the configured Google Sheets connection, a warning pop-up message appears, as shown in the following screen shot:
   

   • Click Cancel, if you do not want to exit the Connectors screen.
   • Click Ok, if you want to exit the Connectors screen.

   
   
   
   A success message for creating a connection appears as shown in the following screenshot:
   
   

8. Click Ok.
9. Click the Data Source  icon on the existing Google Sheets connection.
   
   
   The Create Request page appears.
   
   
10. In the Create Request page, enter a unique data source name in the Data Source Name field.
    
    

11. Provide the Google spread sheet ID in Spreadsheet ID field.
    
    

    You can identify the spread sheet ID from your Google sheet URL.
    
    

12. Provide the spread sheet range in Range field.
    
    

13. Enter the column name and its ID on the Column Name and Column ID field. Here, Name and Task are provided as the Column Name and Column ID.
    
    

    Click plus (  ) icon to add column(s) and minus (  ) icon to delete the column(s).

14.  Click Save.
    

    When you try to exit the Data Source screen, before saving the configured Google Sheets data source, a warning pop-up message appears, as shown in the following screen shot:
    

    • Click Cancel, if you do not want to exit the Data Source screen.
    • Click Ok, if you want to exit the Data Source screen.

    
    
    
    A success message for creating a datasource appears as shown in the following screenshot:
    
    

15. Click Ok.