How to Sync BigQuery to Google Sheets Automatically for Real-Time Reports

Why Sync BigQuery with Google Sheets?

Modern enterprises often face a common reporting dilemma: executive stakeholders prefer the familiar grid of Google Sheets for ad-hoc analysis, while data engineering teams maintain single sources of truth inside Google BigQuery. Manually exporting CSVs introduces human error and creates stale datasets within hours.

By establishing an automated synchronization pipeline, you achieve the best of both worlds: SQL-grade processing speed across billions of records combined with interactive spreadsheet pivot tables.

Method 1: Utilizing Connected Sheets (Enterprise Native Solution)

Connected Sheets is Google Cloud’s native feature allowing Google Sheets to interface directly with BigQuery without extracting data to local memory.

1. Open an existing or new spreadsheet in Google Sheets.
2. In the top navigation menu, click Data > Data connectors > Connect to BigQuery.
3. Select your Google Cloud Billing Project, dataset name, and target table or view.
4. Click Connect. Google Sheets will generate a linked tab with direct SQL execution handles.

Configuring Scheduled Data Refresh

To ensure non-technical executives view fresh figures every morning without opening BigQuery:

• In the bottom Connected Sheets toolbar, click Scheduled refresh.
• Define your cadence: Daily, Weekly, or Hourly. Specify the exact time zone and update window (e.g., 04:00 AM UTC).
• Select which specific pivot tables and formulas should execute upon refresh.
• Click Apply. Cloud IAM credentials will maintain the scheduled query execution automatically.

Method 2: Automated Google Apps Script with BigQuery Service Account

If you require custom data transformation or need to append snapshot rows to a historical archive daily, Google Apps Script provides granular programmatic control:

function syncBigQueryToSheet() {
  const projectId = 'your-gcp-project-id';
  const query = 'SELECT region, sum(revenue) as total_rev FROM `your-dataset.orders` GROUP BY 1 ORDER BY 2 DESC LIMIT 50';
  
  const request = {
    query: query,
    useLegacySql: false
  };
  
  const queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;
  
  // Sleep loop until query completes
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Live_Dashboard');
  const rows = queryResults.rows;
  const data = rows.map(row => row.f.map(cell => cell.v));
  
  sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}

Security & Production Best Practices

• Least Privilege IAM: Assign the service account or user role roles/bigquery.dataViewer and roles/bigquery.jobUser rather than project owner permissions.
• Query Cost Controls: Always query partitioned tables (e.g. _PARTITIONDATE) to prevent accidental multi-terabyte full-table scans when refreshing sheets.
• Audit Logging: Monitor Cloud Logging for BigQuery job execution frequencies to verify query budgets.