Configure a Google Sheet as a datasource

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

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

    

    
    

    
    

Authorize API Key in Google Sheets:

Share your Google Sheet with the email address associated with your previously created project or make it publicly accessible.

Elements Connect 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}/values/{SHEET_NAME}

    The Spreadsheet ID is the last string of characters in the URL for your spreadsheet. For example, in the URL https://docs.google.com/spreadsheets/d/1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps/edit#gid=0, the spreadsheet ID is "1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps".
  • 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":