Class DataSourceTable
Page Summary
The DataSourceTable class is used to access and modify existing data source tables, specifically with BigQuery data sources.
You can create a new data source table using
Spreadsheet.insertSheetWithDataSourceTable(spec)and access it viadataSheet.getDataSourceTables()[0].Data source tables allow adding columns, filters, and sort specifications to the data.
You can refresh the data in a DataSourceTable using
refreshData()orforceRefreshData(), and cancel a refresh withcancelDataRefresh().The status of data execution for a DataSourceTable can be retrieved using
getStatus()and you can wait for completion usingwaitForCompletion().
Access and modify existing data source table. To create a new data source table on a new sheet,useSpreadsheet.insertSheetWithDataSourceTable(spec).
Only use this class with BigQuery data sources.
This example shows how to create a new data source table.
SpreadsheetApp .enableBigQueryExecution();constspreadsheet=SpreadsheetApp.getActive();constspec=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.constdataSheet=spreadsheet.insertSheetWithDataSourceTable(spec);constdataSourceTable=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();constdataSheet=SpreadsheetApp.getActive().getSheetByName('Data Sheet 1');constdataSourceTable=dataSheet.getDataSourceTables()[0];constdataSource=dataSourceTable.getDataSource();constnewSpec=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 |
|---|---|---|
add | Data | Adds columns to the data source table. |
add | Data | Adds a filter applied to the data source table. |
add | Data | Adds a sort spec on a column in the data source table. |
add | Data | Adds a sort spec on a column in the data source table. |
cancel | Data | Cancels the data refresh associated with this object if it's currently running. |
force | Data | Refreshes the data of this object regardless of the current state. |
get | Data | Gets all the data source columns added to the data source table. |
get | Data | Gets the data source the object is linked to. |
get | Data | Returns all filters applied to the data source table. |
get | Range | Gets theRange this data source table spans. |
get | Integer|null | Returns the row limit for the data source table. |
get | Sort | Gets all the sort specs in the data source table. |
get | Data | Gets the data execution status of the object. |
is | Boolean | Returns whether the data source table is syncing all columns in the associated data source. |
refresh | Data | Refreshes the data of the object. |
remove | Data | Removes all the columns in the data source table. |
remove | Data | Removes all the sort specs in the data source table. |
set | Data | Updates the row limit for the data source table. |
sync | Data | Sync all current and future columns in the associated data source to the data source table. |
wait | Data | Waits until the current execution completes, timing out after the provided number of seconds. |
Detailed documentation
addColumns(columnNames)
Adds columns to the data source table.
Parameters
| Name | Type | Description |
|---|---|---|
column | String[] | The list of the names of the columns to add. |
Return
Data — The data source table, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addFilter(columnName, filterCriteria)
Adds a filter applied to the data source table.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The name of the column to apply this filter to. |
filter | Filter | The filter criteria to apply. |
Return
Data — The data source table, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addSortSpec(columnName, ascending)
Adds a sort spec on a column in the data source table.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The name of the column to sort. |
ascending | Boolean | Iftrue, sort the column in ascending order; iffalse, sort the column in descending order. |
Return
Data — The data source sheet, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addSortSpec(columnName, sortOrder)
Adds a sort spec on a column in the data source table.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The name of the column to sort. |
sort | Sort | The sort order. |
Return
Data — The data source sheet, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
cancelDataRefresh()
Cancels the data refresh associated with this object if it's currently running.
This example shows how to cancel a formula refresh.
constspreadsheet=SpreadsheetApp.getActive();constformula=spreadsheet.getDataSourceFormulas()[0];// Cancel the ongoing refresh on the formula.formula.cancelDataRefresh();
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for specific data sourcetype.
Return
Data — The data object.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
forceRefreshData()
Refreshes the data of this object regardless of the current state. Seerefresh formore details. If you want to cancel a currently running refresh of this object, seecancel.
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for specific data sourcetype.
Return
Data — The data object.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getColumns()
Gets all the data source columns added to the data source table.
Return
Data — A list of data source table columns.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getDataSource()
Gets the data source the object is linked to.
Return
Data — The data source.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getFilters()
Returns all filters applied to the data source table.
Return
Data — An array of all filters applied to the data source table.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getRange()
getRowLimit()
Returns the row limit for the data source table.
Return
Integer|null — The row limit for the data source table, ornull if no limit is set and the table uses the default max limit as in Google Sheets UI.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getSortSpecs()
Gets all the sort specs in the data source table.
Return
Sort — A list of sort specs.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getStatus()
Gets the data execution status of the object.
Return
Data — The data execution status.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
isSyncingAllColumns()
Returns whether the data source table is syncing all columns in the associated data source.
Return
Boolean —True if the data source table is syncing all columns in the associated data source, orfalse otherwise.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
refreshData()
Refreshes the data of the object.
Throws an exception if currently inerror state. UseData to update the specification. The method ispreferred overforce to prevent unexpected edits on data source.
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for specific data sourcetype.
Return
Data — The data object.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
removeAllColumns()
Removes all the columns in the data source table.
Return
Data — The data source table, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
removeAllSortSpecs()
Removes all the sort specs in the data source table.
Return
Data — The data source sheet, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
setRowLimit(rowLimit)
Updates the row limit for the data source table. If the provided row limit isnull,then updates the data source table to use the default max row limit as in Google Sheets UI.
Parameters
| Name | Type | Description |
|---|---|---|
row | Integer | The new row limit for the data table. Ifnull, updates the table to use the default row limit. |
Return
Data — The data source table, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
syncAllColumns()
Sync all current and future columns in the associated data source to the data source table.
Return
Data — The data source table, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://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 thedata execution.
Parameters
| Name | Type | Description |
|---|---|---|
timeout | Integer | The time to wait for data execution, in seconds. The maximum is 300 seconds. |
Return
Data — The data execution status.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-11 UTC.