The Sisense Gmail connector is a standalone connector that allows you to import data from Gmail’s API into the ElastiCube Manager. After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense in the ElastiCube Manager. The connection string is used to authenticate users who connect to the Gmail APIs. To obtain a connection string, you will need to register a Gmail app.

Once you have connected to Gmail, you can import a variety of tables from the Gmail API.

This page describes how to install the Gmail connector, how to connect to Gmail with a connection string, and what tables you can import into the ElastiCube Manager:

Installing the Gmail Connector

Sisense provides the Gmail connector as a standalone connector that you can download and add to your list of default Sisense connectors.

To install the Gmail connector:

  1. Download the Gmail installation file.
  2. Open the installation file and click Install.
  3. After the installation process is complete, click Close.

The Gmail connector is displayed in the ElastiCube Manager under Add Data > Web Services.

Connecting to the Gmail REST API

To access Gmail’s REST API from the ElastiCube Manager, you must provide valid Oauth Gmail credentials through a connection string. These credentials are provided by Gmail when you register an application.

After you receive your credentials from Gmail, you can create the connection string and provide Sisense with it to connect to your data.

Registering an App

Follow the steps below to obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret:

  1. Log into the Google API Console.
  2. Click Create Project or select an existing project.
  3. In the API Manager, click Credentials > Create Credentials > OAuth Client Id.
  4. Select the application type.If you are making a Web application, select Web application. In the Authorized Redirect URIs box, enter the URL you want to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access.If you are making a desktop application, select Other.
  5. Click Create. The OAuthClientId and OAuthClientSecret are displayed.
  6. Click Library > Gmail API > Enable API.

Authenticating through Gmail

After setting the following connection properties, you are ready to connect:

  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client secret in your app settings.
  • User: Set this to the user of the Gmail account.

When you connect the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver then completes the OAuth process:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Refreshes the access token when it expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Adding Gmail Tables to your ElastiCube

Sisense uses connection strings to connect to Gmail and import data into the ElastiCube Manager.

The connection string to connect to Gmail has the following structure:

jdbc:Gmail:Property1=Value1;Property2=Value2;

The following is an example of a Gmail connection string:

jdbc:gmail:user[email protected];InitiateOAuth=GETANDREFRESH;OAuthClientId=

xxxxxxxxxxxxxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxxxxxxxxxxxxx;

Note: To switch between accounts, you need to delete the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Gmail Data Provider.

To add Gmail data:

  1. In ElastiCube Manager, click Add Data and then, Gmail. The Connect to Gmail window is displayed.
  2. In Datasource Connection String, enter your connection string.
  3. Click Connect to Server. Gmail is displayed in the Select Database list.
  4. Click OK. Sisense connects to Gmail and displays a list of tables available for you to import.
  5. Select the relevant tables and click Add. The tables are displayed in the ElastiCube Manager.

Switching between Accounts

When you connect to the Gmail data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Gmail Data Provider on your Sisense server. To connect to the Gmail data source with another user on the same machine, you must delete the OAuthsettings.txt file. Sisense will then generate a new file for that user.

Another option to support multiple users is to define the location and file name of an OAuthsettings file for each unique user in your connection string through the OAuthSettingsLocation parameter. When each user connects to the data source, Sisense generates the OAuth file with the file name you specify in the location you define. In the examples below, two users are allowed to access the Gmail data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.

jdbc:Gmail:OAuthSettingsLocation=C:\Gmail\auth\john.txt;OAuthClientId=11276856774486;OAuthClientSecret

=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

jdbc:Gmail:OAuthSettingsLocation=C:\Gmail\auth\sally.txt;OAuthClientId=11276856774486;OAuthClientSecret

=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

In the example above, to OAuth files are created, one for John and one for Sally in the location C:\Gmail\auth\.

This is useful if you support many users who each need to access the Gmail data source.

Gmail Tables

Gmail’s RESTful APIs expose the following Gmail tables that you can import into the ElastiCube Manager through the Sisense Gmail connector:

  • Gmail/All Mail
  • Gmail/Drafts
  • Gmail/Important
  • Gmail/Sent Mail
  • Gmail/Spam
  • Gmail/Starred
  • Gmail/Trash
  • Inbox
  • Test Label

Limitations

  1. The default Gmail table names contain a backslash; some also contain spaces. You can use square brackets to escape the table names for the default Gmail folders.
    For example:
    SELECT * FROM [Gmail/All Mail]
  2. The default number of messages returned is 100. This can be changed by setting either LIMIT or MaxItems. If you wish to return all mail within a mailbox, specify a value of 0 or -1.
  3. Aggregate functions not supported.