Class DataSourceSheet
Page Summary
Use the DataSourceSheet class to access and modify existing data source sheets linked to a database.
New data source sheets are created using the Spreadsheet.insertDataSourceSheet(spec) method.
Key methods include adding and removing filters and sort specs, auto-resizing and setting column widths, retrieving sheet values, and managing data refreshes.
You can also get the associated data source, the current execution status, or view the data source sheet as a regular sheet object.
Specific authorization scopes are required for scripts utilizing these methods, primarily related to accessing spreadsheet data.
Access and modify existing data source sheet. To create a new data source sheet, useSpreadsheet.insertDataSourceSheet(spec).
Only use this class with data that's connected to a database.
Methods
| Method | Return type | Brief description |
|---|---|---|
add | Data | Adds a filter applied to the data source sheet. |
as | Sheet | Returns the data source sheet as a regular sheet object. |
auto | Data | Auto resizes the width of the specified column. |
auto | Data | Auto resizes the width of the specified columns. |
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 | Integer|null | Returns the width of the specified column. |
get | Data | Gets the data source the object is linked to. |
get | Data | Returns all filters applied to the data source sheet. |
get | Object[] | Returns all the values for the data source sheet for the provided column name. |
get | Object[] | Returns all the values for the data source sheet for the provided column name from the providedstart row (based-1) and up to the providednum. |
get | Sort | Gets all the sort specs in the data source sheet. |
get | Data | Gets the data execution status of the object. |
refresh | Data | Refreshes the data of the object. |
remove | Data | Removes all filters applied to the data source sheet column. |
remove | Data | Removes the sort spec on a column in the data source sheet. |
set | Data | Sets the width of the specified column. |
set | Data | Sets the width of the specified columns. |
set | Data | Sets the sort spec on a column in the data source sheet. |
set | Data | Sets the sort spec on a column in the data source sheet. |
wait | Data | Waits until the current execution completes, timing out after the provided number of seconds. |
Detailed documentation
addFilter(columnName, filterCriteria)
Adds a filter applied to the data source sheet.
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 sheet, for method 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
asSheet()
autoResizeColumn(columnName)
Auto resizes the width of the specified column.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The column name. |
Return
Data — This 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
autoResizeColumns(columnNames)
Auto resizes the width of the specified columns.
Parameters
| Name | Type | Description |
|---|---|---|
column | String[] | The list of column names to update. |
Return
Data — This 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
getColumnWidth(columnName)
Returns the width of the specified column.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The column name. |
Return
Integer|null — The column's width, ornull if the column uses the default width.
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 sheet.
Return
Data — An array of all filters applied to the data source sheet.
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
getSheetValues(columnName)
Returns all the values for the data source sheet for the provided column name.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The data source column name to fetch values for. |
Return
Object[] — A one-dimensional array of values.
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
getSheetValues(columnName, startRow, numRows)
Returns all the values for the data source sheet for the provided column name from the providedstart row (based-1) and up to the providednum.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The data source column name to fetch values for. |
start | Integer | The row position to start fetching values from. |
num | Integer | The number of rows to fetch. |
Return
Object[] — A one-dimensional array of values.
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 sheet.
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
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
removeFilters(columnName)
Removes all filters applied to the data source sheet column.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The name of the column to remove filters from. |
Return
Data — The data source sheet, for method 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
removeSortSpec(columnName)
Removes the sort spec on a column in the data source sheet.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The name of the column. |
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
setColumnWidth(columnName, width)
Sets the width of the specified column.
Parameters
| Name | Type | Description |
|---|---|---|
column | String | The column name. |
width | Integer | The new width for the column. |
Return
Data — This 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
setColumnWidths(columnNames, width)
Sets the width of the specified columns.
Parameters
| Name | Type | Description |
|---|---|---|
column | String[] | The list of column names to update. |
width | Integer | The new width for the columns. |
Return
Data — This 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
setSortSpec(columnName, ascending)
Sets the sort spec on a column in the data source sheet.
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
setSortSpec(columnName, sortOrder)
Sets the sort spec on a column in the data source sheet.
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
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.