Introduction
This guide takes you through setting up Google Sheets for use with Cyclr, as well as installing a Google Sheets Connector.
You will need a Google Cloud Platform account.
Setup & authentication
Overview
The Google Sheets Connector supports 2 Authentication Types, which are explained in this guide:
- OAuth2 – requires user interaction to install the Connector.
- Service Account – impersonates a domain account and requires less user interaction.
Remote setup in Google Cloud Console – performed by Cyclr Partner
Depending on the Authentication Type you choose, the setup is slightly different after following the initial “Common Setup” below:
Common Setup – Enabling the Google Sheets API
Both Authentication Types require the following setup to be performed.
To access the Google Sheets API endpoints, you need to enable the “Google Sheets API” within the project in your Google workspace. Google’s documentation on how to do this can be found here.
Then skip to the appropriate section below:
OAuth2 – remote setup
To authenticate the Connector using the “OAuth 2.0 Authorization Code” Authentication Type, you need to create a web application inside your Google Cloud Platform account to get your OAuth 2.0 credentials:
- From the Google Cloud Platform Credentials page, select CREATE CREDENTIALS > OAuth client ID. Create a project with the following settings:
| Setting | Value |
|---|---|
| Application type | Web application |
| Authorised redirect URIs | A URL containing your Cyclr Service Domain. This can be found under Settings > General Settings > Service Domain in your Cyclr console. For example, https://{ServiceDomain}/connector/callback |
- Make a note of the Client ID and Client Secret.
For more information, see Google’s guide on how to create client credentials.
Service Account – remote setup
To authenticate the Connector using the “Service Account” Authentication Type, you need to do the following:
- Enable the “Google Sheets API” for a project within your Google workspace.
- Create a service account.
- Enable domain-wide delegation for the service account.
Create a Service Account
A service account needs to be created to allow admin-level access to Google Sheets within your workspace. Google’s documentation on how to do this can be found here.
When creating the service account, set up the fields below as follows:
- Grant this service account access to project: Set the service account role to
Owner. - Grant users access to this service account: Set the Service account admins role field to any users that will be administering the service account.
Make a note of the OAuth 2.0 client ID and Email for your service account. You will need these to enable domain-wide delegation and to authorize your account with Cyclr. You can find this on the service accounts page.
When adding the service account key, set the Key type to JSON, then open the downloaded .json key file and make a note of the private_key field. You need this to authorize your account with Cyclr.
Enabling domain-wide delegation for your Service Account
Domain-wide delegation needs to be enabled for your service account to allow it to access user data on behalf of users in your workspace. You need to be a “super administrator” of the workspace to configure this. Google’s documentation on how to do this can be found here.
When enabling domain-wide delegation, set up the fields below as follows:
- Client ID: Set to the OAuth 2.0 client ID from the previous section.
- OAuth scopes (comma-delimited):
Add the following scopes:https://www.googleapis.com/auth/spreadsheets– for Sheets access.https://www.googleapis.com/auth/drive.metadata.readonly– for Drive metadata read access.
Partner Setup in Cyclr Console
Having performed the appropriate setup described above, go to your Cyclr Partner Console:
- Go to Connectors > Application Connector Library.
- Use the search box to locate the Google Sheets Connector entry.
- Select the Pencil button.
- In the Authentication dropdown, select either OAuth2 or Service Account, then follow the appropriate section below:
“OAuth2” Setup
- Select the Settings tab.
- Enter the below values:
| Value | Description |
|---|---|
| Client ID | The default client ID to use. |
| Client Secret | The default client secret to use. |
| Scopes | The scopes to use. Cyclr defaults to the following if you don’t provide a value: https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive.metadata.readonly |
- Select Save Changes.
If you leave these values blank, they must be provided each time the Connector is installed.
“Service Account” Setup
- Select the Settings tab.
- Enter the below values:
| Value | Description |
|---|---|
| Client ID | Leave blank. |
| Client Secret | Leave blank. |
| Scopes | The scopes to use. Cyclr defaults to the following if you don’t provide a value: https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive.metadata.readonly |
- Select Save Changes.
If you leave these values blank, they must be provided each time the Connector is installed.
Cyclr Connector installation
Depending on the Authentication Type you’ve chosen, the installation of a Google Sheets Connector is slightly different, as explained below:
“OAuth2” Installation
When authenticating the Connector, the user must be signed in using the relevant Google user and then provide consent to your Google project to access their data.
Note: With the “OAuth2” Authentication Type, the Service Account Email, Service Account Administrator Email, and Service Account Private Key fields should be ignored.
“Service Account” Installation
The following values must be provided for the Google workspace to connect with:
| Value | Description |
|---|---|
| Service Account Email | The email address of the service account. |
| Service Account Administrator Email | The email address of the administrator of the service account. |
| Service Account Private Key | The private key of the service account. |