Class DataSourceFormula

  • Use DataSourceFormula to access and modify existing data source formulas, but use Range.setFormula(formula) to create a new one.

  • This class is specifically for data connected to a BigQuery database.

  • Methods are available to cancel, force, or simply refresh data, get information about the formula and its status, and set a new formula.

  • Scripts using these methods require specific authorization scopes related to spreadsheets.

DataSourceFormula

Access and modify existing data source formulas. To create a new data source formula, useRange.setFormula(formula).

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

Methods

MethodReturn typeBrief description
cancelDataRefresh()DataSourceFormulaCancels the data refresh associated with this object if it's currently running.
forceRefreshData()DataSourceFormulaRefreshes the data of this object regardless of the current state.
getAnchorCell()RangeReturns theRange representing the cell where this data source formula is anchored.
getDataSource()DataSourceGets the data source the object is linked to.
getDisplayValue()StringReturns the display value of the data source formula.
getFormula()StringReturns the formula for this data source formula.
getStatus()DataExecutionStatusGets the data execution status of the object.
refreshData()DataSourceFormulaRefreshes the data of the object.
setFormula(formula)DataSourceFormulaUpdates the formula.
waitForCompletion(timeoutInSeconds)DataExecutionStatusWaits until the current execution completes, timing out after the provided number of seconds.

Detailed documentation

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. UseSpreadsheetApp#enable...Execution() methods to enable data execution for specific data sourcetype.

Return

DataSourceFormula — The data object.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

  • 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. SeerefreshData() formore details. If you want to cancel a currently running refresh of this object, seecancelDataRefresh().

Throws an exception if the data source type is not enabled. UseSpreadsheetApp#enable...Execution() methods to enable data execution for specific data sourcetype.

Return

DataSourceFormula — The data object.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

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

getAnchorCell()

Returns theRange representing the cell where this data source formula is anchored.

Return

Range — The anchor cell.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

  • 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 followingscopes:

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

getDisplayValue()

Returns the display value of the data source formula.

Return

String — The display value.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

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

getFormula()

Returns the formula for this data source formula.

Return

String — The formula.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

  • 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 followingscopes:

  • 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 inerror state. UseDataSource#updateSpec() to update the specification. The method ispreferred overforceRefreshData() to prevent unexpected edits on data source.

Throws an exception if the data source type is not enabled. UseSpreadsheetApp#enable...Execution() methods to enable data execution for specific data sourcetype.

Return

DataSourceFormula — The data object.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

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

setFormula(formula)

Updates the formula.

Parameters

NameTypeDescription
formulaStringThe new formula.

Return

DataSourceFormula — The data source formula, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the followingscopes:

  • 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 thedata 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 followingscopes:

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