How to Configure Scheduled Queries in Google Sheets with BigQuery

Modern enterprise business intelligence workflows require querying massive datasets residing in cloud data warehouses without crashing desktop spreadsheet applications. Through Connected Sheets, Google Sheets connects natively to Google Cloud BigQuery, allowing users to analyze billions of rows of live production data using familiar formulas, pivot tables, and charts. Configuring scheduled query refreshes ensures team dashboards always reflect up-to-the-minute data automatically.

Prerequisites and IAM Service Account Permissions

Connecting Google Sheets to BigQuery requires specific administrative access in Google Cloud Platform (GCP):

• GCP Project Access: An active Google Cloud Project with the BigQuery API enabled and billing attached.
• BigQuery IAM Roles: The Google Workspace user account must hold at least BigQuery Data Viewer (roles/bigquery.dataViewer) on the target datasets and BigQuery Job User (roles/bigquery.jobUser) on the project to execute query slots.

Connecting Google Sheets Directly to BigQuery

Establish the native data pipeline directly from within the spreadsheet interface:

1. Open a new or existing spreadsheet in Google Sheets.
2. Click the Data menu in the top toolbar.
3. Hover over Data connectors and select Connect to BigQuery.
4. Select your Google Cloud Project ID.
5. Choose your connection mode:
   • Dataset & Table: Browse public or private datasets to load a table directly.
   • Custom Query: Write custom SQL to aggregate, join, or filter terabytes of data down to relevant reporting metrics before transferring bytes to the sheet.
6. Click Connect. Google Sheets initializes a Connected Sheet tab backed by the BigQuery calculation engine.

Building Custom SQL Queries Inside Connected Sheets

Writing targeted SQL optimizes query slot consumption and minimizes BigQuery billing scan costs:

SELECT
  DATE_TRUNC(order_timestamp, DAY) AS transaction_date,
  region,
  COUNT(order_id) AS total_orders,
  ROUND(SUM(revenue_usd), 2) AS gross_revenue
FROM
  `company-analytics-prod.sales.orders_partitioned`
WHERE
  order_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY
  transaction_date, region
ORDER BY
  transaction_date DESC;

Click Apply. BigQuery executes the computation in the cloud and renders the aggregated result table inside Google Sheets.

Configuring Automated Scheduled Data Refreshes

Automate background execution so analysts never have to trigger manual updates:

1. In the Connected Sheet bottom status banner, click Refresh options (or go to Data > Data connectors > Refresh options).
2. In the right-hand sidebar, locate Scheduled refresh.
3. Click Edit schedule.
4. Select your preferred update frequency:
   • Every hour: For live operational monitoring.
   • Daily: Set a specific time window (e.g., 05:00 AM - 06:00 AM) to process overnight ETL loads prior to the morning business standup.
   • Weekly: For high-level executive financial recaps.
5. Click Save.

Google Cloud manages query scheduling in the background, updating all dependent charts, formulas, and pivot tables in the workbook automatically without requiring the sheet to be open in an active browser tab.