# Connected Sheets

## Use Connected Sheets

[Connected Sheets](https://cloud.google.com/blog/products/g-suite/connected-sheets-is-generally-available) 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 <a href="#common_connected_sheets_actions" id="common_connected_sheets_actions"></a>

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 <a href="#add_required_authorization_scopes" id="add_required_authorization_scopes"></a>

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:

```javascript
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](https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes).

For example, to [query BigQuery data hosted within Google Drive](https://cloud.google.com/bigquery/external-data-drive#api), 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:

```javascript
{ ...
  "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 <a href="#example_create_and_refresh_a_data_source_object" id="example_create_and_refresh_a_data_source_object"></a>

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 <a href="#add_a_bigquery_data_source" id="add_a_bigquery_data_source"></a>

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.

```javascript
// 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.

```javascript
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

```javascript
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 <a href="#use_triggers_with_connected_sheets" id="use_triggers_with_connected_sheets"></a>

Automate your Connected Sheets data source functions with [triggers and events](https://developers.google.com/apps-script/guides/triggers/installable). For example, use [time-driven triggers](https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers) to refresh data source objects repeatedly at a specific time, and use spreadsheet [event triggers](https://developers.google.com/apps-script/guides/triggers/installable#g_suite_application_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.

```javascript
// 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](https://developers.google.com/apps-script/guides/triggers/installable).

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.
