Documentation Plugins Google Sheets data source Query editor

Google Sheets query editor

The Google Sheets data source query editor configures the Google Sheets API query. Use it when building panels in a dashboard or in Explore.

This document walks you through key concepts and a summary of the fields, then how to create a query, then each field in detail, and finally example use cases for common scenarios.

Before you begin

• Ensure you have configured the Google Sheets data source and that Save & test shows Success.
• Your credentials must have access to the spreadsheets you want to query.

Key concepts

Query editor fields

The sections below describe each field in detail.

Create a query

To create a query:

1. Select the Google Sheets data source for the panel (or open Explore and select it there).
2. In Spreadsheet ID, enter the spreadsheet ID or URL, or choose a spreadsheet from the list if you use JWT authentication.
3. Optionally, enter a Range in A1 notation (e.g. Sheet1!A2:E). Leave it empty to use the entire first sheet.
4. Set Cache Time if you want something other than the default (5 minutes). Use 0s to disable caching.
5. Turn Use Time Filter on if your data has a time column and you want to filter by the dashboard time range.
6. Run the query. The panel refreshes with the data from your sheet.

Spreadsheet ID

The Spreadsheet ID field controls which spreadsheet to query.

You can:

• Enter a spreadsheet ID.
• Enter a spreadsheet URL.

The query editor automatically extracts the spreadsheet ID from the URL.

• Enter a spreadsheet URL including a range.

  The query editor automatically extracts both spreadsheet ID and range from the URL. To copy a range:

  1. Open the spreadsheet.

  2. Select the cells that you want to include.

  3. Right-click one of the cells and choose Get link to this range. The link is copied to your clipboard.

• Select a spreadsheet from the drop-down menu.

  The drop-down menu is only populated if you are using Google JWT authentication. You can only view spreadsheets shared with the service account associated with the token.

  To configure a service account with JWT authentication, refer to Authenticate with a service account JWT.

Next to the Spreadsheet ID field there’s an external link icon. Click that icon to open the spreadsheet in Google Sheets in a new tab.

Range

The Range field controls which cells to query. You can use A1 notation (for example, Sheet1!A2:E or Class Data!A2:E) or a named range. If you leave the range empty, the API returns the whole first sheet in the spreadsheet.

Cache Time

The Cache Time field controls how long to cache the Google Sheets API response. The cache key is a combination of spreadsheet ID and range, so changing either results in a different cache key.

Options include 0s, 5s, 10s, 30s, 1m, 2m, 5m, 10m, 30m, 1h, 2h, and 5h. The default is five minutes (5m). Set Cache Time to 0s to bypass the cache completely.

Use Time Filter

The Use Time Filter toggle controls whether to apply the dashboard time range to the data. When enabled, the plugin filters rows using the first time field in the data so that only rows within the dashboard time picker range are returned.

The plugin does not use query-language macros (such as $__timeFilter()). Use this toggle instead to apply the dashboard time range.

How data is interpreted

The plugin uses the first row of the range as column headers and infers a type for each column from the cell values:

• Mixed types: If a column has mixed value types (e.g. numbers and text), the plugin treats the column as string. A warning is added to the response (e.g. “Multiple data types found in column … Using string data type”).
• Mixed units: If a column has mixed units (e.g. different currencies or formats), the plugin uses the formatted value and may add a warning (e.g. “Multiple units found in column … Formatted value will be used”).
• Parse errors: Cells that cannot be parsed (e.g. invalid date text) produce per-cell warnings. The row may still be returned with a fallback or empty value depending on the column type.

These warnings are included in the query response metadata. If a panel shows unexpected types or empty values, check that the sheet has consistent types in each column and that date columns are formatted as date/time in Google Sheets.

Example use cases

Time series or time-based data

Use a range where the first column (or first column the plugin detects as time) contains dates or timestamps. Enable Use Time Filter so the panel only shows rows within the dashboard time picker range. Choose a Cache Time that balances freshness with API quota (for example, 5m for frequently updated data).

Table or tabular data (no time filter)

Set Spreadsheet ID and Range to your data (e.g. Sheet1!A1:D50). Leave Use Time Filter off if there is no time column. Use a longer Cache Time if the sheet changes rarely to reduce API usage.

KPIs or single values

Use a small range (e.g. one row or a few cells, such as Summary!B2:B5) and pair it with a Stat or Gauge panel. Set a short Cache Time (e.g. 30s or 1m) if the values update often, or longer if they are static.

Annotations from a sheet

Use a Google Sheet as an annotation source to overlay events on panels. See Annotations for sheet layout (time, text, tags), steps to add an annotation query, and Use Time Filter.

Same spreadsheet, different panels

Use one Spreadsheet ID across multiple panels and set a different Range in each (e.g. Sales!A1:E100, Inventory!A1:C50). To save time, set a default spreadsheet in the data source configuration so new queries are pre-filled.

Dynamic spreadsheet or range with template variables

Use template variables in Spreadsheet ID or Range (e.g. $spreadsheet or Sheet1!A1:$region) so users can switch sheets or ranges from the dashboard. The query runs with the selected variable values.

Frequently updated vs. static data

For data that changes often (e.g. live status), use a short Cache Time (5s, 30s, or 1m) and be aware of Google Sheets API quotas. For reference data that rarely changes, use a longer cache (30m, 1h, or 5h) to reduce API calls.

Copying a range from Google Sheets

In Google Sheets, select the cells you need, right-click and choose Get link to this range, then paste the link into Spreadsheet ID. The editor extracts both the spreadsheet ID and the range from the URL.

Next steps

• Use template variables to make dashboards dynamic.
• Quick start: create a sample dashboard to try the data source with sample data.
• Configure the data source to change authentication or default spreadsheet.