Class DataSource
Page Summary
The
DataSourceclass is used to access and modify existing data sources connected to a database.You can create calculated columns and new pivot or data source tables on new sheets using this class.
The class provides methods to retrieve columns, calculated columns, and associated data source sheets.
You can refresh or cancel refreshes for linked data source objects and update the data source specification.
You can wait for data executions linked to the data source to complete within a specified timeout.
Access and modify existing data source. To create a data source table with new data source, seeData.
Only use this class with data that's connected to a database.
Methods
| Method | Return type | Brief description |
|---|---|---|
cancel | void | Cancels all currently running refreshes of data source objects linked to this data source. |
create | Data | Creates a calculated column. |
create | Data | Creates a data source pivot table from this data source in the first cell of a new sheet. |
create | Data | Creates a data source table from this data source in the first cell of a new sheet. |
get | Data | Returns the calculated column in the data source that matches the column name. |
get | Data | Returns all the calculated columns in the data source. |
get | Data | Returns all the columns in the data source. |
get | Data | Returns the data source sheets associated with this data source. |
get | Data | Gets the data source specification. |
refresh | void | Refreshes all data source objects linked to the data source. |
update | Data | Updates the data source specification and refreshes the data source objects linked with thisdata source with the new specification. |
update | Data | Updates the data source specification and refreshes the linkeddatasource sheets with the new specification. |
wait | void | Waits until all the current executions of the linked data source objects complete, timing outafter the provided number of seconds. |
Detailed documentation
cancelAllLinkedDataSourceObjectRefreshes()
Cancels all currently running refreshes of data source objects linked to this data source.
This example shows how to cancel all the refreshes of a data source.
SpreadsheetApp .enableBigQueryExecution();constspreadsheet=SpreadsheetApp.getActiveSpreadsheet();constdataSource=spreadsheet.getDataSources()[0];dataSource.cancelAllLinkedDataSourceObjectRefreshes();
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for the specific datasource type.
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
createCalculatedColumn(name, formula)
Creates a calculated column. This method is only available for BigQuery data sources.
Parameters
| Name | Type | Description |
|---|---|---|
name | String | The name of the calculated column. |
formula | String | The calculated column formula. |
Return
Data — The newly created calculated column.
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
createDataSourcePivotTableOnNewSheet()
Creates a data source pivot table from this data source in the first cell of a new sheet. As aside effect, makes the new sheet the active sheet.
Return
Data — The newly created data source pivot 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
createDataSourceTableOnNewSheet()
Creates a data source table from this data source in the first cell of a new sheet. As a sideeffect, makes the new sheet the active sheet.
This method is only available for BigQuery data sources.
Return
Data — The newly created 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
getCalculatedColumnByName(columnName)
Returns the calculated column in the data source that matches the column name.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The name of the calculated column to get. |
Return
Data — The calculated column that matches the column name, ornull if there is no such calculated column.
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
getCalculatedColumns()
Returns all the calculated columns in the data source.
Data source specs ofData type returns an empty array.
Return
Data — An array of all the calculated columns in 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
getColumns()
Returns all the columns in the data source.
Return
Data — An array of all theData in 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
getDataSourceSheets()
Returns the data source sheets associated with this data source.
Return
Data — An array of data source sheets.
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
getSpec()
Gets the data source specification.
Return
Data — The data source specification.
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
refreshAllLinkedDataSourceObjects()
Refreshes all data source objects linked to the data source.
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for the specific datasource type.
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
updateSpec(spec)
Updates the data source specification and refreshes the data source objects linked with thisdata source with the new specification.
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for the specific datasource type.
Throws an exception if the data source specification type is of a different type than thecurrent data source type.
Parameters
| Name | Type | Description |
|---|---|---|
spec | Data | The data source specification to update with. |
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
updateSpec(spec, refreshAllLinkedObjects)
Updates the data source specification and refreshes the linkeddatasource sheets with the new specification.
Throws an exception if the data source type is not enabled. UseSpreadsheet methods to enable data execution for the specific datasource type.
Throws an exception if the data source specification type is of a different type than thecurrent data source type.
Parameters
| Name | Type | Description |
|---|---|---|
spec | Data | The data source specification to update with. |
refresh | Boolean | Iftrue, also refreshes all the data source objects linked with this data source. |
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
waitForAllDataExecutionsCompletion(timeoutInSeconds)
Waits until all the current executions of the linked data source objects complete, timing outafter the provided number of seconds. Throws an exception if the executions are not completedwhen timing out, but does not cancel the data executions.
Parameters
| Name | Type | Description |
|---|---|---|
timeout | Integer | The time to wait for data executions, in seconds. The maximum is 300 seconds. |
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.