Google Sheets Data Connector

Built-in
Built by CHILI publish
Third Party

Installation

You can deploy multiple instances of the connector, each with different settings.

Configuration

Base Configuration

Once installed, navigate to the Connector overview, and select your deployed Google Sheets connector. Start with Configuration.

screenshot-full

Name: Choose a name to distinguish your connector setup.
Description: Give more context what this connector does, what is specific about it.
Version: If available, choose the version you want to use.
Proxy settings

sheets.googleapis.com

Authentication

To authenticate with Google Sheets, you need to provide credentials.

You can configure Server Authentication and Browser Authentication separately or use a single setup for both.

screenshot

Server Authentication Always required: defines the method on how the CHILI GraFx Server will talk to the Google Sheets server
Browser Authentication Optionally, you can define how the browser needs to talk to Google Sheets, to pull data in

1. Server Authentication

Server authentication is Always required.

For the server authentication, you will need to setup a service account on Google Cloud.

After confirmation, a JSON file will be downloaded to your computer. (see example below)

Below is an example (where actual credentials have been removed for security). Below, we'll refer to parts of that JSON to use in the setup.

{
  "type": "service_account",
  "project_id": "your-project-name",
  "private_key_id": "2d7f5c97ccae8465e708bc...",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADqsdfkjqmsdlkfjmsqdkfjtyTXDMR\n42AQ7VJsIxnPM5FUZx8xzRNMVDQakle5Ksi6zFeZr3/Nrh20yXp0iYXtkLqNTvAD\n...\nXwbYE9GufVHVtvXz573fQcQzrPJ5ifjoZ+hDpfpT9ZOfMO1zA/HzOlxfUN9XF2Kc\njfFdOCixWLT6HuKeOb0GH1eo\n-----END PRIVATE KEY-----\n",
  "client_email": "google-generated-address@your-project-name.iam.gserviceaccount.com",
  "client_id": "123456789123456798",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/yourname%40yourproject-grafx.iam.gserviceaccount.com",
  "universe_domain": "googleapis.com"
}

screenshot-full

Authorization method: Select the required authentication method.
Separate or same method: Enable to configure different methods for Server and Browser authentication.

screenshot

Token endpoint:
see JSON: token_uri
```
https://oauth2.googleapis.com/token
```
Issuer: Provide the email address of the service account.
see JSON: client_email
Signing algorithm: JWT Bearer token requires RS256 algorithm.
Private Key: Provide the PEM-formatted private key.
see JSON: private_key You can copy-past the full contents, leaving in the "\n". They will be replaced when you save your settings.
If you come back to change settings, you will not see the private key, for obvious reasons.
New changes: re-enter the private key again before you save.

Try it out

To try out your setup, open a test Google Sheets document with public access.

Public data

Beware to not share data you would like to keep private, as the sheet will be public to anyone with the link.

screenshot

screenshot-full

Now, add 1 variable to a Smart Template, and preview in Run Mode.

If that works, the connector works fine.

Later you can tweak the security and permissions on the google side, together with your system admin.

Stop here

No need to setup Browser authentication, if the system can use the service account for both Server and Browser authentication. Only if you need a different way to authenticate in the Browser, then continue on 2.

2. Browser Authentication or Impersonation

Create Client ID credentials on Google Cloud Console

At the end, you'll get a JSON file. We'll refer to elements in the setup below.

A modified example to use as reference: ¹

{
"web":
    {
    "client_id":"123456789.apps.googleusercontent.com",
    "project_id":"your-project-id",
    "auth_uri":"https://accounts.google.com/o/oauth2/auth",
    "token_uri":"https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
    "client_secret":"ABCDEF-_DEF123456HIJKLM"
    }
}

screenshot-full

OAuth 2.0 Authorization Code

Client ID:
see JSON: client_id
Client Secret:
see JSON: client_secret
Authorization endpoint:
see JSON: auth_uri
Set the endpoint to:
The below endpoint includes 2 extra parameters
```
https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&include_granted_scopes=true
```

Extra URL parameters

access_type=offline This ensures that the application can continue to access the Google API even when the user is not actively using it. It allows the system to receive a refresh token, which can be used to get a new access token without requiring the user to log in again.

include_granted_scopes=true If the user has already granted permission for certain scopes in a previous authentication, this setting ensures that those permissions are retained. This way, the user doesn’t have to approve the same access every time they log in.

Token endpoint:
see JSON: token_uri
```
https://oauth2.googleapis.com/token
```
Scope: Provide the scope:

https://www.googleapis.com/auth/spreadsheets.readonly

For more details, refer to Google Developers.

Goes without saying, these settings will NOT work, you need to make your own. ↩