Configuring a Google Sheet as a datasource using API key authentication

Unlike the OAuth2 authentication (see here), this approach requires the spreadsheet to be publicly exposed.

Prerequisites

Have a Jira Cloud instance
Have Elements Connect installed on your instance
Have a Google Sheet

Configuration steps

Google API configuration
Elements Connect configuration

1. Google API configuration

Set up a Google Cloud Platform (GCP) project

Go to the Google Cloud Console: https://console.cloud.google.com/
Create a new project or select an existing one:

Enable the Google Sheets API

Go to your new project
Go to "APIs and services"
Go to "Library"
Search for "Google Sheets API" and enable it:

Create API Key

Still in your project, go back to "APIs and services"
Go to "Credentials"
Click on "Create credentials" and select "API key".
Copy the generated API key:

Configure API restrictions (optional)

For security and privacy reasons, you can edit your new API KEY and perform the following actions:

IP addresses restrictions:
- Set application restriction to "IP addresses"
- Add the IP addresses mentioned here:
Key restrictions:
- Set "API restrictions" to "Restrict key"
- Select the "Google Sheets API":

Make the Sheet public

With this approach, the Google Sheet needs to be publicly accessible, as shown below:

2. Elements Connect configuration

Datasource configuration

Go to the Elements Connect administration menu
Go to the Datasources tab
Create a new REST API datasource and configure it as follows:
- API Base URL:
  CODE
```
https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}
```
  Please refer to the bottom of this page for instructions on how to retrieve {SPREADSHEET_ID}
- Authentication:
  - Mode: API Key
  - Send in: Query parameter
  - Parameter name:
    CODE
```
key
```
  - API Key: the API key you generated earlier in Google.
- Then, click "Save" and "Test Connection":

Endpoint for the Connected field configuration

When setting up your Connected field, you need to complete the Base URL by adding the following in the Path section:

CODE

/values/{SHEET_NAME}

Replace {SHEET_NAME} with the name of the sheet you want to access. Instructions on how to retrieve it are available at the bottom of this page.

For example:

How to retrieve `{SPREADSHEET_ID}` and `{SHEET_NAME}`

{SPREADSHEET_ID} corresponds to the long string of characters in your Spreadsheet’s URL:
{SHEET_NAME} is the name of the sheet you want to reach in your Spreadsheet:

For example, in this case, the URL to use in Elements Connect would be: https://sheets.googleapis.com/v4/spreadsheets/1WSFLmwiEDH0LjPtpdnjSncyo4n1uj3I9or5qcvpJ7dQ/values/Sheet1