Documentation Plugins Google Sheets data source Configure

Configure the Google Sheets data source

This document explains how to configure and provision the Google Sheets data source. The plugin uses the Google Sheets API in read-only mode; it does not write or modify spreadsheets.

Before you begin

Before configuring the data source, ensure you have:

• Grafana permissions: Organization administrator role (to add and configure data sources).
• Plugin installed: The Google Sheets data source plugin. For instructions, refer to Install a plugin.
• Google account: A Google account; for JWT or API key authentication, a Google Cloud project with the required APIs enabled (see each authentication section below for details).

Add the data source

To add the data source:

1. Click Connections in the left-side menu.
2. Enter Google Sheets in the search dialog. You can filter by Data source to only see data sources.
3. Click Google Sheets to add it.
4. Configure the data source using the sections below.

Name and default

At the top of the configuration page, set Name (the name used to refer to the data source in panels and queries) and Default (toggle to make this the default data source for new panels).

Authentication

The Google Sheets data source supports three authentication methods. The default is Google JWT File (service account).

• Google JWT File: uses a service account and can access private spreadsheets. Works in all environments where Grafana is running.
• API key: offers simpler configuration, but requires spreadsheets to be public.
• GCE Default Service Account: automatically retrieves default credentials. Requires Grafana to be running on a Google Compute Engine virtual machine.

On the configuration page, choose an authentication type. You can expand Configure Google Sheets Authentication for step-by-step guidance in the UI. Depending on your authentication type, you may need to share spreadsheets or set permissions; see Sharing.

Authenticate with a service account JWT

If you want to access private spreadsheets, you must use a service account authentication. A Google service account belongs to a project within an account or organization instead of to an individual end user. The application, in this case Grafana, calls Google APIs on behalf of the service account, so users aren’t directly involved.

Enable the Google Sheets API and the Google Drive API for the project that contains the service account.

The Google Sheets data source uses the scope https://www.googleapis.com/auth/spreadsheets.readonly to get read-only access to spreadsheets. It also uses the scope https://www.googleapis.com/auth/drive.metadata.readonly to list all spreadsheets that the service account has access to in Google Drive.

To create a service account and get a JWT file:

1. Enable the APIs in your Google Cloud project.
   1. Open the Google Sheets API page and click enable.
   2. Open the Google Drive API page and click enable.
2. Open the Credentials page in the Google Cloud Console.
3. Click Create credentials then Service account.
4. Fill out the service account details form and then click Create and continue.
5. Ignore the Service account permissions and Principals with access sections, just click Done.
6. Click into the details for the service account, navigate to the Keys tab, and click Add Key. Choose key type JSON and click Create. A JSON key file will be created and downloaded to your computer.
7. Upload or drag this file into the JWT Key Details section of the data source configuration.
8. Grant the service account access to resources as appropriate.

Authenticate with an API key

For publicly shared spreadsheets, you don’t need to authorize the request, but you must provide an API key.

To generate an API key:

1. Before you can use the Google APIs, you need to enable them in your Google Cloud project.
   1. Open the Google Sheets API page and click enable.
2. Open the Credentials page in the Google Cloud Console.
3. Click Create credentials and then API key.
4. Paste the value in the API Key field of the data source configuration.

Authenticate with the default GCE service account

When Grafana is running on a Google Compute Engine (GCE) virtual machine, Grafana can automatically retrieve default credentials from the metadata server. As a result, there is no need to generate a private key file for the service account. You also don’t need to upload the file to Grafana.

To authenticate with the default GCE service account:

1. You must create a service account for use by the GCE virtual machine. For more information, refer to Create new service account.
2. Verify that the GCE virtual machine instance is running as the service account that you created. For more information, refer to setting up an instance to run as a service account.
3. Allow access to the specified API scope.
4. Enter the project name in the Default project field of the data source configuration.

Configure settings

Below the authentication type selector, Default project and Default Spreadsheet ID appear:

Default Spreadsheet ID

You can optionally configure a Default Spreadsheet ID in the data source settings. When set, this spreadsheet ID will be automatically populated in new queries, making it faster to create queries that use the same spreadsheet.

To configure a default spreadsheet ID:

1. On the configuration page, scroll to the Default Spreadsheet ID field.
2. Choose one of these options:
   • Select Spreadsheet ID (JWT authentication only): If you’re using Google JWT File authentication, click the control to select a spreadsheet from the list. The list shows all spreadsheets that the service account has access to.
   • Enter a spreadsheet ID: Manually enter the spreadsheet ID from the spreadsheet URL.
   • Paste a spreadsheet URL: Paste the full spreadsheet URL, and the ID will be automatically extracted.

When you create a new query, the default spreadsheet ID will be pre-filled in the Spreadsheet ID field of the query editor.

Sharing

Refer to the official guidance from Google on how to share resources:

• Google Sheets
• Google Drive

Granting access to the service account used with JWT authentication

By default, the service account doesn’t have access to any spreadsheets within the account or organization that it’s associated with. To grant the service account access to files or folders in Google Drive, you need to share the file or folder with the service account’s email address. The service account’s email address is the client_email field in the JWT file.

Verify the connection

Click Save & test to verify the connection. A successful connection shows the message Success. If the test fails, see Troubleshooting.

Provision the data source

You can define the Google Sheets data source with Grafana provisioning (YAML) or with the Grafana Terraform provider.

You can provision the data source using any of these authentication mechanisms:

• With an API key
• With a service account JWT
• With the default GCE service account

With an API key

To create an API key, refer to Authenticate with an API key.

Example:

YAML Copy

apiVersion: 1
datasources:
  - name: <DATA_SOURCE_NAME>
    type: grafana-googlesheets-datasource
    jsonData:
      authenticationType: 'key'
      defaultSheetID: <SPREADSHEET_ID> # Optional: default spreadsheet ID for new queries
    secureJsonData:
      apiKey: <API_KEY>

Replace <API_KEY>, <DATA_SOURCE_NAME>, and optionally <SPREADSHEET_ID> with your values.

With a service account JWT

To create a service account and its JWT file, refer to Authenticate with a service account JWT.

Example:

YAML Copy

apiVersion: 1
datasources:
  - name: <DATA_SOURCE_NAME>
    type: grafana-googlesheets-datasource
    jsonData:
      authenticationType: 'jwt'
      defaultProject: <PROJECT_ID>
      clientEmail: <CLIENT_EMAIL>
      tokenUri: 'https://oauth2.googleapis.com/token'
      defaultSheetID: <SPREADSHEET_ID> # Optional: default spreadsheet ID for new queries
    secureJsonData:
      privateKey: <PRIVATE_KEY_DATA>

Replace <PROJECT_ID>, <CLIENT_EMAIL>, <PRIVATE_KEY_DATA>, <DATA_SOURCE_NAME>, and optionally <SPREADSHEET_ID> with your values.

Private key from local file

The following example shows provisioning the Google Sheets data source using a private key file stored locally.

YAML Copy

apiVersion: 1
datasources:
  - name: <DATA_SOURCE_NAME>
    type: grafana-googlesheets-datasource
    jsonData:
      authenticationType: 'jwt'
      defaultProject: <PROJECT_ID>
      clientEmail: <CLIENT_EMAIL>
      privateKeyPath: '/path/to/privateKey'
      tokenUri: 'https://oauth2.googleapis.com/token'
      defaultSheetID: <SPREADSHEET_ID> # Optional: default spreadsheet ID for new queries

With the default GCE service account

You can use the Google Compute Engine (GCE) default service account to authenticate data source requests if you’re running Grafana on GCE.

Example:

YAML Copy

apiVersion: 1
datasources:
  - name: <DATA_SOURCE_NAME>
    type: grafana-googlesheets-datasource
    jsonData:
      authenticationType: 'gce'
      defaultProject: <PROJECT_ID>
      defaultSheetID: <SPREADSHEET_ID> # Optional: default spreadsheet ID for new queries

Replace <PROJECT_ID>, <DATA_SOURCE_NAME>, and optionally <SPREADSHEET_ID> with your values.

Provision with Terraform

Use the grafana_data_source resource with type = "grafana-googlesheets-datasource". Pass plugin-specific options in json_data_encoded and secure_json_data_encoded (JSON strings, camelCase keys).

API key:

hcl Copy

resource "grafana_data_source" "google_sheets" {
  type = "grafana-googlesheets-datasource"
  name = "<DATA_SOURCE_NAME>"

  json_data_encoded = jsonencode({
    authenticationType = "key"
    defaultSheetID    = "<SPREADSHEET_ID>" # Optional
  })

  secure_json_data_encoded = jsonencode({
    apiKey = "<API_KEY>"
  })
}

Service account JWT:

hcl Copy

resource "grafana_data_source" "google_sheets" {
  type = "grafana-googlesheets-datasource"
  name = "<DATA_SOURCE_NAME>"

  json_data_encoded = jsonencode({
    authenticationType = "jwt"
    defaultProject      = "<PROJECT_ID>"
    clientEmail         = "<CLIENT_EMAIL>"
    tokenUri            = "https://oauth2.googleapis.com/token"
    defaultSheetID      = "<SPREADSHEET_ID>" # Optional
  })

  secure_json_data_encoded = jsonencode({
    privateKey = "<PRIVATE_KEY_DATA>"
  })
}

GCE default service account:

hcl Copy

resource "grafana_data_source" "google_sheets" {
  type = "grafana-googlesheets-datasource"
  name = "<DATA_SOURCE_NAME>"

  json_data_encoded = jsonencode({
    authenticationType = "gce"
    defaultProject      = "<PROJECT_ID>"
    defaultSheetID      = "<SPREADSHEET_ID>" # Optional
  })
}

Replace the placeholders with your values. For more examples and options, see the Grafana Terraform provider documentation.