How to configure Google Sheet as a datasource using API key authentication

Prerequisites

• Have a Jira Cloud instance

• Have Elements Connect installed on your instance

• Have a Google Sheet

Configuration steps

1. Google API configuration

2. 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:

    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:

      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:

/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: