Class DataSourceSheet

  • 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.
DataSourceSheet

Access and modify existing data source sheet. To create a new data source sheet, use Spreadsheet.insertDataSourceSheet(spec) .

Only use this class with data that's connected to a database.

Methods

MethodReturn typeBrief description
addFilter(columnName, filterCriteria) DataSourceSheet Adds a filter applied to the data source sheet.
asSheet() Sheet Returns the data source sheet as a regular sheet object.
autoResizeColumn(columnName) DataSourceSheet Auto resizes the width of the specified column.
autoResizeColumns(columnNames) DataSourceSheet Auto resizes the width of the specified columns.
cancelDataRefresh() DataSourceSheet Cancels the data refresh associated with this object if it's currently running.
forceRefreshData() DataSourceSheet Refreshes the data of this object regardless of the current state.
getColumnWidth(columnName) IntegerReturns the width of the specified column.
getDataSource() DataSource Gets the data source the object is linked to.
getFilters() DataSourceSheetFilter[] Returns all filters applied to the data source sheet.
getSheetValues(columnName) Object[]Returns all the values for the data source sheet for the provided column name.
getSheetValues(columnName, startRow, numRows) Object[]Returns all the values for the data source sheet for the provided column name from the provided start row (based-1) and up to the provided numRows.
getSortSpecs() SortSpec[] Gets all the sort specs in the data source sheet.
getStatus() DataExecutionStatus Gets the data execution status of the object.
refreshData() DataSourceSheet Refreshes the data of the object.
removeFilters(columnName) DataSourceSheet Removes all filters applied to the data source sheet column.
removeSortSpec(columnName) DataSourceSheet Removes the sort spec on a column in the data source sheet.
setColumnWidth(columnName, width) DataSourceSheet Sets the width of the specified column.
setColumnWidths(columnNames, width) DataSourceSheet Sets the width of the specified columns.
setSortSpec(columnName, ascending) DataSourceSheet Sets the sort spec on a column in the data source sheet.
setSortSpec(columnName, sortOrder) DataSourceSheet Sets the sort spec on a column in the data source sheet.
waitForCompletion(timeoutInSeconds) DataExecutionStatus 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

NameTypeDescription
columnNameStringThe name of the column to apply this filter to.
filterCriteriaFilterCriteria The filter criteria to apply.

Return

DataSourceSheet — The data source sheet, for method chaining.

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

asSheet()

Returns the data source sheet as a regular sheet object.

Return

Sheet — The regular sheet.

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

autoResizeColumn(columnName)

Auto resizes the width of the specified column.

Parameters

NameTypeDescription
columnNameStringThe column name.

Return

DataSourceSheet — This data source sheet, for chaining.

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

autoResizeColumns(columnNames)

Auto resizes the width of the specified columns.

Parameters

NameTypeDescription
columnNamesString[]The list of column names to update.

Return

DataSourceSheet — This data source sheet, for chaining.

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

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. Use SpreadsheetApp#enable...Execution() methods to enable data execution for specific data source type.

Return

DataSourceSheet — 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

forceRefreshData()

Refreshes the data of this object regardless of the current state. See refreshData() for more details. If you want to cancel a currently running refresh of this object, see cancelDataRefresh() .

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

DataSourceSheet — 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

getColumnWidth(columnName)

Returns the width of the specified column.

Parameters

NameTypeDescription
columnNameStringThe column name.

Return

Integer — The column's width, or null if the column uses the default width.

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

getFilters()

Returns all filters applied to the data source sheet.

Return

DataSourceSheetFilter[] — 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 following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getSheetValues(columnName)

Returns all the values for the data source sheet for the provided column name.

Parameters

NameTypeDescription
columnNameStringThe 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 following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://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 provided start row (based-1) and up to the provided numRows.

Parameters

NameTypeDescription
columnNameStringThe data source column name to fetch values for.
startRowIntegerThe row position to start fetching values from.
numRowsIntegerThe 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 following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getSortSpecs()

Gets all the sort specs in the data source sheet.

Return

SortSpec[] — A list of sort specs.

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

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 the specification. 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

DataSourceSheet — 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

removeFilters(columnName)

Removes all filters applied to the data source sheet column.

Parameters

NameTypeDescription
columnNameStringThe name of the column to remove filters from.

Return

DataSourceSheet — The data source sheet, for method chaining.

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

removeSortSpec(columnName)

Removes the sort spec on a column in the data source sheet.

Parameters

NameTypeDescription
columnNameStringThe name of the column.

Return

DataSourceSheet — The data source sheet, for chaining.

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

setColumnWidth(columnName, width)

Sets the width of the specified column.

Parameters

NameTypeDescription
columnNameStringThe column name.
widthIntegerThe new width for the column.

Return

DataSourceSheet — This data source sheet, for chaining.

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

setColumnWidths(columnNames, width)

Sets the width of the specified columns.

Parameters

NameTypeDescription
columnNamesString[]The list of column names to update.
widthIntegerThe new width for the columns.

Return

DataSourceSheet — This data source sheet, for chaining.

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

setSortSpec(columnName, ascending)

Sets the sort spec on a column in the data source sheet.

Parameters

NameTypeDescription
columnNameStringThe name of the column to sort.
ascendingBooleanIf true, sort the column in ascending order; if false, sort the column in descending order.

Return

DataSourceSheet — The data source sheet, for chaining.

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

setSortSpec(columnName, sortOrder)

Sets the sort spec on a column in the data source sheet.

Parameters

NameTypeDescription
columnNameStringThe name of the column to sort.
sortOrderSortOrder The sort order.

Return

DataSourceSheet — The data source sheet, for chaining.

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

NameTypeDescription
timeoutInSecondsIntegerThe 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

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2024年12月03日 UTC.