Configuration

Select “Configuration” in the navigation panel to configure the Google Sheets connector. Before you begin, configure your Google Account as described in the steps below.

Authentication

Configure Google Service Account

The Google Sheets Connector uses a service account for authentication and communications with the Google Sheets API. Before the Google Sheets Connector can send data to Google Sheets, the Google Sheets API needs to be enabled in your account and a service account needs to be configured for use by Dataristix.

Login to your Google account to create a service account for Dataristix by following these steps (see also: https://cloud.google.com/iam/docs/service-accounts-create).

1. Within the Google Cloud Console (https://console.cloud.google.com/), select “IAM & Admin / Service Accounts”.

2. Create a project if required by clicking on the “Create Project” button and follow the prompt to enter a project name, for example “My Sheets Project”.

3. Click on “Create Service Account”.

4. Enter a suitable service account name of your choice, then click on “Create and Continue”. Skip through the optional “Grant this service account access to project” and “Grant users access to this service account” (you may re-visit these options later). Take note of the e-mail address created for the service account; you’ll need this e-mail address later to grant access to spreadsheets.

5. Click on the vertical dots in the “Actions” column and select menu option “Manage Keys”.

6. Click “Add Key” / “Create New Key” (Note: if you plan to use Workload Identity Federation then please contact support).

7. In the dialog, select “JSON” as the key type, then click “Create”. This will download a key file onto your computer for use with the Google Sheets Connector. Keep this file in a safe place.

Enable Google Sheets API

Click on the “APIs & Services” menu (you can see the menu in the first step of the previous section) and click on “Enable APIs and Services”

Search for “Google Sheets” to locate the Google Sheets API, then click on the “Google Sheets API” panel.

Click “Enable” to enable the Google Sheets API.

Grant the service account access to a spreadsheet

The service account created for the Google Sheets Connector needs to have access to at least one spreadsheet to send data to. Go to your Google Sheets and add a blank spreadsheet or locate an existing one, then click on the “Share” button.

Rename the sheet if prompted, then paste the e-mail address of the service account created earlier into the “Add people and groups” entry field titled “Share Live Data”. The service account should be an “Editor”. You do not need to notify the service account.

Return to Dataristix for the remainder of the configuration.

Import Service Account key

The service account key JSON file saved previously can now be imported into the Google Sheets Connector. Click on the “Import Key” button and locate the JSON file. After successful import, you should see the name of the key displayed in the “Service Account Key ID” field.

The service key is stored in encrypted form by Dataristix and you can delete the original file or keep it in a safe place.

Remove Service Account key

If you no longer wish to use the service account key then you can remove the key from the configuration by clicking on the “Remove Key” button. This will remove the key from storage.

Warning

Removing the key will prevent existing tasks from starting data transfers to Google Sheets. Already running tasks continue to function until they are stopped.

Placeholders

Google Sheets may contain placeholders that are recognized by the Google Sheets Connector when spreadsheets are added. Placeholders may be generated and placed on the clipboard by copying connector topics. Here you can configure how placeholders should be generated or how tags should be derived from RTD-style placeholders found in your Google sheet. Read more about placeholders in the placeholder documentation.

( 1 ) When datasource connector topics are copied by right-clicking on the topic, then the Google Sheets Connector copies placeholder text in the configured form onto the clipboard for pasting into a Google sheet. The sheet can then subsequently be added to turn placeholders into tag connection points.

( 2 ) Excel RTD-style links within a Google sheet are interpreted as placeholders when adding such a sheet to the Google Sheet connector. For RTD links originating from the Dataristix Excel Connector, RTD links contain the connector name and topic name. Tag names generated from such links may optionally include the RTD link’s connector and topic name by checking the respective option. See also: Excel RTD links.

( 3 ) Excel links originating from OPC Office Link within a Google sheet are also interpreted as placeholders when adding such a sheet to the Google Sheet connector. Tag names generated from such links may optionally contain the additional link parameters (for example, the OPC Office Link data group name) by checking the “Include additional RTD topics” option. See also: Excel OPC Office Link links.

API Calls

Google places a limit on the frequency of API calls, and the Google Sheet Connector reduces the number of API calls by batching calls when possible. The period of time over which live values are collected without resulting in an API call is determined by the “Batch Write Requests for …”” value.

Enter a value here that is as high as acceptable to reduce the number of API calls. Sheets will not receive live values faster than this configured rate, irrespective of the update rate of data source topics.