What trigger should I use for Run BigQuery SQL and Export Results Directly to Google Sheets?

This script is designed for: Daily Trigger. Configure it under Apps Script > Triggers (clock icon).

What OAuth permissions does Run BigQuery SQL and Export Results Directly to Google Sheets require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/bigquery, https://www.googleapis.com/auth/spreadsheets.

API & Webhooks AutomationDifficulty: Advanced⚡ Daily Trigger

Run BigQuery SQL and Export Results Directly to Google Sheets

Executes an asynchronous SQL query on a Google Cloud BigQuery dataset, waits for query completion, and streams analytical results directly into a Google Sheet tab.

Required OAuth Scopes: https://www.googleapis.com/auth/bigquery • https://www.googleapis.com/auth/spreadsheets

Code.gs (Apps Script V8 Runtime)

function runBigQueryAndExportToSheet() {
  const projectId = 'PASTE_GCP_PROJECT_ID_HERE';
  const sql = 'SELECT geo_country, count(1) as sessions FROM `project.analytics.events` GROUP BY 1 ORDER BY 2 DESC LIMIT 100;';
  
  const request = {
    query: sql,
    useLegacySql: false
  };
  
  const queryResults = BigQuery.Jobs.query(request, projectId);
  const rows = queryResults.rows;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BQ_Export');
  
  const tableData = [['Country', 'Sessions']];
  if (rows) {
    rows.forEach(r => {
      tableData.push([r.f[0].v, Number(r.f[1].v)]);
    });
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, tableData.length, 2).setValues(tableData);
}

Line-by-Line Code Explanation

Line 2-3: Configures Google Cloud project ID and standard SQL query.

Line 10: Invokes BigQuery Advanced Service API.

Line 15-18: Parses nested BigQuery result fields `f[index].v` into standard 2D arrays.

Line 21-22: Writes data into target spreadsheet tab.

Step-by-Step Setup & Deployment

In Apps Script, click Services (+) > Select BigQuery API.
Replace GCP Project ID and SQL query with your database coordinates.
Set a scheduled trigger to refresh reporting data automatically.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More API & Webhooks Automation Snippets

API & Webhooks