Page Summary

outlined_flag

• Connected Sheets allows you to analyze BigQuery and Looker data directly within Google Sheets and is accessible programmatically using the Spreadsheet service.

• Common actions with Connected Sheets, such as connecting to a data source or adding charts, are performed using specificDataSource classes and methods in Apps Script.

• To access BigQuery or Looker data, you must includeenableBigQueryExecution() orenableLookerExecution() respectively in your Apps Script code, which adds the necessary OAuth scopes.

• Data source objects can be added and refreshed asynchronously, with methods available to check the execution status and handle errors.

• Triggers, including time-driven and event triggers, can be used to automate data source functions, such as refreshing data on a schedule or when a parameter is edited.

Connected Sheets is a Google Sheets feature that lets you analyze BigQuery and Looker datadirectly within Sheets.You can access Connected Sheetsprogrammatically with the Spreadsheet service.

Common Connected Sheets actions

Use theDataSource classes and objects to connect to BigQuery or Looker andanalyze data.The following table lists the most commonDataSource actions andhow to create them in Apps Script:

Add required authorization scopes

To access BigQuery data, you must include theenableBigQueryExecution() methodin your Google Apps Script code. This method adds the requiredbigquery.readonlyOAuth scope to your Google Apps Script project.

The following sample shows theSpreadsheetApp.enableBigQueryExecution() methodcalled within a function:

functionaddDataSource(){SpreadsheetApp.enableBigQueryExecution();varspreadsheet=SpreadsheetApp.getActive();}

To access Looker data, you must include theenableLookerExecution() method inyour Google Apps Script code. Accessing Looker in Apps Scriptwill reuse your existing Google Account Link with Looker.

The following sample shows theSpreadsheetApp.enableLookerExecution() methodcalled within a function:

functionaddDataSource(){SpreadsheetApp.enableLookerExecution();varspreadsheet=SpreadsheetApp.getActive();}

Add additional OAuth scopes to the manifest file

When connecting with BigQuery, most OAuth scopes are automatically added to themanifest file based on the functions used in your code. If you need additionalscopes to access certain BigQuery data, you canset explicit scopes.

For example, toquery BigQuery data hosted within Google Drive, you must add a Drive OAuth scope to your manifestfile.

The following sample shows theoauthScopes portion of a manifest file. It addsa drive OAuth scope in addition to the minimum requiredspreadsheet andbigquery.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 examples shows how to add a data source, create a datasource object from the data source, refresh the data source object, and getthe execution status.

Add a data source

The following examples show how to add a BigQuery and a Looker data sourcerespectively.

BigQuery

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.

//ForoperationsthatfetchdatafromBigQuery,enableBigQueryExecution()mustbecalled.SpreadsheetApp.enableBigQueryExecution();varspreadsheet=SpreadsheetApp.create('Test connected sheets');Logger.log('New test spreadsheet:%s',spreadsheet.getUrl());//Builddatasourcespecbyselectingatable.vardataSourceSpec=SpreadsheetApp.newDataSourceSpec().asBigQuery().setProjectId('<YOUR_PROJECT_ID>').setTableProjectId('bigquery-public-data').setDatasetId('ncaa_basketball').setTableId('mbb_historical_tournament_games').build();//Adddatasourceanditsassociateddatasourcesheet.vardataSourceSheet=spreadsheet.insertDataSourceSheet(dataSourceSpec);vardataSource=dataSourceSheet.getDataSource();

Looker

To add a Looker 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<INSTANCE_URL>,<MODEL_NAME>,<EXPLORE_NAME> in the followingsample with a valid Looker instance URL, model name and explore namerespectively.

//ForoperationsthatfetchdatafromLooker,enableLookerExecution()mustbecalled.SpreadsheetApp.enableLookerExecution();varspreadsheet=SpreadsheetApp.create('Test connected sheets');Logger.log('New test spreadsheet:%s',spreadsheet.getUrl());//Builddatasourcespecbyselectingatable.vardataSourceSpec=SpreadsheetApp.newDataSourceSpec().asLooker().setInstanceUrl('<INSTANCE_URL>').setModelName('<MODEL_NAME>').setExploreName('<EXPLORE_NAME>').build();//Adddatasourceanditsassociateddatasourcesheet.vardataSourceSheet=spreadsheet.insertDataSourceSheet(dataSourceSpec);vardataSource=dataSourceSheet.getDataSource();

Add a data source object

Once the data source is added to the spreadsheet, data source objects can becreated from the data source. In this example, a pivot table is created usingDataSourcePivotTable on the BigQuerydataSource created inthe code sample which adds a BigQuery datasource.

Unlike regular data in grid sheets that are referenced by cell index or A1notations, data from data sources are usually referenced by column names.Therefore, most property setters on data source objects use column name asinput.

varrootCell=spreadsheet.insertSheet('pivotTableSheet').getRange('A1');//Adddatasourcepivottableandsetdatasourcespecificconfigurations.vardataSourcePivotTable=rootCell.createDataSourcePivotTable(dataSource);varrowGroup=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);varfilterCriteria=SpreadsheetApp.newFilterCriteria().whenTextEqualToAny(['Duke','North Carolina']).build();dataSourcePivotTable.addFilter('win_school_ncaa',filterCriteria);//Getaregularpivottableinstanceandsetsharedconfigurations.varpivotTable=dataSourcePivotTable.asPivotTable();pivotTable.setValuesDisplayOrientation(SpreadsheetApp.Dimension.ROWS);

Refresh a data source object

You can refresh data source objects to fetch the latest data from BigQuerybased 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 iscompleted. This eliminates the need to keep polling the execution status.
3. DataExecutionStatus.getErrorCode() gets the error code in case the dataexecution fails.

The sample below illustrates a refresh of the pivot table data:

varstatus=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 withtriggers and events.For example, usetime-driven triggersto refresh data source objects repeatedly at a specific time, and usespreadsheetevent triggersto trigger data execution on a predefined event.

The following sample adds a BigQuery data source with a query parameter andrefreshes the data source sheet when the query parameter is edited.

Replace<YOUR_PROJECT_ID> below with a valid Google Cloud project ID.

//Adddatasourcewithqueryparameter.functionaddDataSource(){SpreadsheetApp.enableBigQueryExecution();varspreadsheet=SpreadsheetApp.getActive();//AddanewsheetanduseA1cellastheparametercell.varparameterCell=spreadsheet.insertSheet('parameterSheet').getRange('A1');parameterCell.setValue('Duke');//Adddatasourcewithqueryparameter.vardataSourceSpec=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();vardataSourceSheet=spreadsheet.insertDataSourceSheet(dataSourceSpec);dataSourceSheet.asSheet().setName('ncaa_data');}//Functionusedtoconfigureeventtriggertorefreshdatasourcesheet.functionrefreshOnParameterEdit(e){vareditedRange=e.range;if(editedRange.getSheet().getName()!='parameterSheet'){return;}//CheckthattheeditedrangeincludesA1.if(editedRange.getRow() >1||editedRange.getColumn() >1){return;}varspreadsheet=e.source;SpreadsheetApp.enableBigQueryExecution();spreadsheet.getSheetByName('ncaa_data').asDataSourceSheet().refreshData();}

In the above sample, theaddDataSource() function adds a data source to thespreadsheet. After you executeaddDataSource(), create an event trigger inthe Apps Script editor. To learn how to create an event trigger, seeInstallable 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 automaticallyevery time the parameter cell is edited.

Troubleshoot