Connected Sheets

Use Connected Sheets

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	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 Apps Script code. This method adds the required bigquery.readonly OAuth scope to your 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:

1. refreshData() starts the data refresh execution.

2. waitForCompletion() returns the end state once the data execution is completed. This eliminates the need to keep polling the execution status.

3. 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.