Connected Sheets is a Google Sheets feature that lets you analyze BigQuery data directly within Sheets. You can access Connected Sheets programmatically with the Spreadsheet service.
Common Connected Sheets actions
Use the DataSource
classes and objects to connect to BigQuery and analyze data.
The table below lists the most common DataSource
actions and how to create them in Apps Script:
Action | Google Apps Script class | Method to use |
---|---|---|
Connect a sheet to BigQuery | DataSourceSpec |
SpreadsheetApp.newDataSourceSpec() |
Choose a BigQuery data source | DataSource |
Spreadsheet.insertDataSourceSheet().getDataSource() |
Add a BigQuery data source sheet | DataSourceSheet |
Spreadsheet.insertDataSourceSheet() |
Add a pivot table | DataSourcePivotTable |
Range.insertDataSourcePivotTable() |
Pull data into an extract | DataSourceTable |
Range.insertDataSourceTable() |
Use a formula | DataSourceFormula |
Range.setFormula() |
Add a chart | DataSourceChart |
Sheet.insertDataSourceChart() |
Add required authorization scopes
To access BigQuery data, you must include the enableBigQueryExecution()
method
in your Google Apps Script code. This method adds the required bigquery.readonly
OAuth scope to your Google Apps Script project.
The following sample shows the SpreadsheetApp.enableBigQueryExecution()
method
called within a function:
function addDataSource() { SpreadsheetApp.enableBigQueryExecution(); var spreadsheet = SpreadsheetApp.getActive(); }
Add additional OAuth scopes to the manifest file
Most OAuth scopes are automatically added to the manifest file based on the functions used in your code. If you need additional scopes to access certain BigQuery data, you can set explicit scopes.
For example, to query BigQuery data hosted within Google Drive, you must add a Drive OAuth scope to your manifest file.
The following sample shows the oauthScopes
portion of a manifest file. It adds
a drive OAuth scope in addition to the minimum required spreadsheet
and
bigquery.readonly
OAuth scopes:
{ ... "oauthScopes": [ "https://www.googleapis.com/auth/bigquery.readonly", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive" ], ... }
Example: Create and refresh a data source object
The following example shows how to add a BigQuery data source, create a data source object from the data source, refresh the data source object, and get the execution status. In this example, the code snippets are executed in sequence.
Add a BigQuery data source
To add a BigQuery data source to a spreadsheet, insert a data source sheet with a data source spec. The data source sheet is automatically refreshed to fetch preview data.
Replace <YOUR_PROJECT_ID>
below with a valid Google Cloud project ID.
// For operations that fetch data from BigQuery, enableBigQueryExecution() must be called.
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());
// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
.asBigQuery()
.setProjectId('<YOUR_PROJECT_ID>')
.setTableProjectId('bigquery-public-data')
.setDatasetId('ncaa_basketball')
.setTableId('mbb_historical_tournament_games')
.build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();
Add a data source object
Once the data source is added to the spreadsheet, data source objects can be
created from the data source. In this example, a pivot table is created using
DataSourcePivotTable
.
Unlike regular data in grid sheets that are referenced by cell index or A1 notations, data from data sources are usually referenced by column names. Therefore, most property setters on data source objects use column name as input.
var rootCell = spreadsheet.insertSheet('pivotTableSheet').getRange('A1');
// Add data source pivot table and set data source specific configurations.
var dataSourcePivotTable = rootCell.createDataSourcePivotTable(dataSource);
var rowGroup = dataSourcePivotTable.addRowGroup('season');
rowGroup.sortDescending().setGroupLimit(5);
dataSourcePivotTable.addColumnGroup('win_school_ncaa');
dataSourcePivotTable.addPivotValue('win_pts', SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
dataSourcePivotTable.addPivotValue('game_date', SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
var filterCriteria = SpreadsheetApp.newFilterCriteria()
.whenTextEqualToAny(['Duke', 'North Carolina'])
.build();
dataSourcePivotTable.addFilter('win_school_ncaa', filterCriteria);
// Get a regular pivot table instance and set shared configurations.
var pivotTable = dataSourcePivotTable.asPivotTable();
pivotTable.setValuesDisplayOrientation(SpreadsheetApp.Dimension.ROWS);
Refresh a data source object
You can refresh data source objects to fetch the latest data from BigQuery based on the data source specs and object configurations.
The process to refresh data is asynchronous. To refresh a data source object, use the following methods:
refreshData()
starts the data refresh execution.waitForCompletion()
returns the end state once the data execution is completed. This eliminates the need to keep polling the execution status.DataExecutionStatus.getErrorCode()
gets the error code in case the data execution fails.
The sample below illustrates a refresh of the pivot table data:
var status = dataSourcePivotTable.getStatus();
Logger.log('Initial state: %s', status.getExecutionState());
dataSourcePivotTable.refreshData();
status = dataSourcePivotTable.waitForCompletion(/* timeoutInSeconds= */ 60);
Logger.log('Ending state: %s', status.getExecutionState());
if (status.getExecutionState() == SpreadsheetApp.DataExecutionState.ERROR) {
Logger.log('Error: %s (%s)', status.getErrorCode(), status.getErrorMessage());
}
Use triggers with Connected Sheets
Automate your Connected Sheets data source functions with triggers and events. For example, use time-driven triggers to refresh data source objects repeatedly at a specific time, and use spreadsheet event triggers to trigger data execution on a predefined event.
The sample below adds a data source with a query parameter and refreshes the data source sheet when the query parameter is edited.
Replace <YOUR_PROJECT_ID>
below with a valid Google Cloud project ID.
// Add data source with query parameter.
function addDataSource() {
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.getActive();
// Add a new sheet and use A1 cell as the parameter cell.
var parameterCell = spreadsheet.insertSheet('parameterSheet').getRange('A1');
parameterCell.setValue('Duke');
// Add data source with query parameter.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
.asBigQuery()
.setProjectId('<YOUR_PROJECT_ID>')
.setRawQuery('select * from `bigquery-public-data`.`ncaa_basketball`.`mbb_historical_tournament_games` WHERE win_school_ncaa = @SCHOOL')
.setParameterFromCell('SCHOOL', 'parameterSheet!A1')
.build();
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
dataSourceSheet.asSheet().setName('ncaa_data');
}
// Function used to configure event trigger to refresh data source sheet.
function refreshOnParameterEdit(e) {
var editedRange = e.range;
if (editedRange.getSheet().getName() != 'parameterSheet') {
return;
}
// Check that the edited range includes A1.
if (editedRange.getRow() > 1 || editedRange.getColumn() > 1) {
return;
}
var spreadsheet = e.source;
SpreadsheetApp.enableBigQueryExecution();
spreadsheet.getSheetByName('ncaa_data').asDataSourceSheet().refreshData();
}
In the above sample, the addDataSource()
function adds a data source to the
spreadsheet. After you execute addDataSource()
, create an event trigger in
the Apps Script editor. To learn how to create an event trigger, see Installable triggers.
Select the following options for your trigger:
- Event source: From spreadsheet
- Event type: On edit
- Function to run:
refreshOnParameterEdit
Once the trigger is created, the data source sheet refreshes automatically every time the parameter cell is edited.
Troubleshoot
Error message | Resolution |
---|---|
Use enableBigQuery() to enable data executions for BIGQUERY data sources. |
This error indicates that SpreadsheetApp.enableBigQueryExecution() is not called before fetching BigQuery data.Call SpreadsheetApp.enableBigQueryExecution() in functions that use methods for BigQuery execution. Such as, refreshData() on data source objects, Spreadsheet.insertDataSourceTable() , and DataSource.updateSpec() . These methods require an additional bigquery.readonly OAuth scope to work. |
Not permitted to act on data sources. Please contact your administrator to enable the feature. |
This error indicates that the account doesn’t have Connected Sheets enabled. Connected Sheets is only available to Google Workspace users with certain subscriptions. Contact your administrator to enable the feature. |