Access and modify existing data source table. To create a new data source table on a new sheet,
use Spreadsheet.insertSheetWithDataSourceTable(spec).
This example shows how to create a new data source table.
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.getActive();
var spec = SpreadsheetApp.newDataSourceSpec()
.asBigQuery()
.setProjectId('big_query_project')
.setRawQuery('select @FIELD from table limit @LIMIT')
.setParameterFromCell('FIELD', 'Sheet1!A1')
.setParameterFromCell('LIMIT', 'namedRangeCell')
.build();
// Starts data execution asynchronously.
var dataSheet = spreadsheet.insertSheetWithDataSourceTable(spec);
var dataSourceTable = dataSheet.getDataSourceTables()[0];
// waitForCompletion() blocks script execution until data execution completes.
dataSourceTable.waitForCompletion(60);
// Check status after execution.
Logger.log("Data execution state: %s.", dataSourceTable.getStatus().getExecutionState());
This example shows how to edit a data source.
SpreadsheetApp.enableBigQueryExecution();
var dataSheet = SpreadsheetApp.getActive().getSheetByName("Data Sheet 1");
var dataSourceTable = dataSheet.getDataSourceTables()[0];
var dataSource = dataSourceTable.getDataSource();
var newSpec = dataSource.getSpec()
.copy()
.asBigQuery()
.setRawQuery('select name from table limit 2')
.removeAllParameters()
.build();
// Updates data source specification and starts data execution asynchronously.
dataSource.updateSpec(newSpec);
// Check status during execution.
Logger.log("Data execution state: %s.", dataSourceTable.getStatus().getExecutionState());
// waitForCompletion() blocks script execution until data execution completes.
dataSourceTable.waitForCompletion(60);
// Check status after execution.
Logger.log("Data execution state: %s.", dataSourceTable.getStatus().getExecutionState());
Methods
| Method | Return type | Brief description |
|---|---|---|
forceRefreshData() | DataSourceTable | Refreshes the data of this object regardless of the current state. |
getDataSource() | DataSource | Gets the data source the object is linked to. |
getRange() | Range | Gets the Range this data source table spans. |
getStatus() | DataExecutionStatus | Gets the data execution status of the object. |
refreshData() | DataSourceTable | Refreshes the data of the object. |
waitForCompletion(timeoutInSeconds) | DataExecutionStatus | Waits until the current execution completes, timing out after the provided number of seconds. |
Detailed documentation
forceRefreshData()
Refreshes the data of this object regardless of the current state. See refreshData() for
more details.
Throws an exception if the data source type is not enabled. Use SpreadsheetApp#enable...Execution() methods to enable data execution for specific data source
type.
Return
DataSourceTable — The data object.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly -
https://www.googleapis.com/auth/spreadsheets
getDataSource()
Gets the data source the object is linked to.
Return
DataSource — The data source.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly -
https://www.googleapis.com/auth/spreadsheets
getRange()
getStatus()
Gets the data execution status of the object.
Return
DataExecutionStatus — The data execution status.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly -
https://www.googleapis.com/auth/spreadsheets
refreshData()
Refreshes the data of the object.
Throws an exception if currently in error state. Use DataSource#updateSpec() to update specificiation. The method is
preferred over forceRefreshData() to prevent unexpected edits on data source.
Throws an exception if the data source type is not enabled. Use SpreadsheetApp#enable...Execution() methods to enable data execution for specific data source
type.
Return
DataSourceTable — The data object.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly -
https://www.googleapis.com/auth/spreadsheets
waitForCompletion(timeoutInSeconds)
Waits until the current execution completes, timing out after the provided number of seconds. Throws an exception if the execution is not completed when timing out, but does not cancel the data execution.
Parameters
| Name | Type | Description |
|---|---|---|
timeoutInSeconds | Integer | The time to wait for data execution, in seconds. The maximum is 300 seconds. |
Return
DataExecutionStatus — The data execution status.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly -
https://www.googleapis.com/auth/spreadsheets
Need help? Visit our