Class DataSourceTable

DataSourceTable

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

Only use this class with BigQuery data sources.

This example shows how to create a new data source table.

SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.getActive();
var spec = 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.
var dataSheet = spreadsheet.insertSheetWithDataSourceTable(spec);
var dataSourceTable = 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();
var dataSheet = SpreadsheetApp.getActive().getSheetByName("Data Sheet 1");
var dataSourceTable = dataSheet.getDataSourceTables()[0];
var dataSource = dataSourceTable.getDataSource();
var newSpec = 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

MethodReturn typeBrief description
addColumns(columnNames)DataSourceTableAdds columns to the data source table.
addFilter(columnName, filterCriteria)DataSourceTableAdds a filter applied to the data source table.
addSortSpec(columnName, ascending)DataSourceTableAdds a sort spec on a column in the data source table.
addSortSpec(columnName, sortOrder)DataSourceTableAdds a sort spec on a column in the data source table.
cancelDataRefresh()DataSourceTableCancels the data refresh associated with this object if it's currently running.
forceRefreshData()DataSourceTableRefreshes the data of this object regardless of the current state.
getColumns()DataSourceTableColumn[]Gets all the data source columns added to the data source table.
getDataSource()DataSourceGets the data source the object is linked to.
getFilters()DataSourceTableFilter[]Returns all filters applied to the data source table.
getRange()RangeGets the Range this data source table spans.
getRowLimit()IntegerReturns the row limit for the data source table.
getSortSpecs()SortSpec[]Gets all the sort specs in the data source table.
getStatus()DataExecutionStatusGets the data execution status of the object.
isSyncingAllColumns()BooleanReturns whether the data source table is syncing all columns in the associated data source.
refreshData()DataSourceTableRefreshes the data of the object.
removeAllColumns()DataSourceTableRemoves all the columns in the data source table.
removeAllSortSpecs()DataSourceTableRemoves all the sort specs in the data source table.
setRowLimit(rowLimit)DataSourceTableUpdates the row limit for the data source table.
syncAllColumns()DataSourceTableSync all current and future columns in the associated data source to the data source table.
waitForCompletion(timeoutInSeconds)DataExecutionStatusWaits 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

NameTypeDescription
columnNamesString[]The list of the names of the columns to add.

Return

DataSourceTable — The data source table, 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

addFilter(columnName, filterCriteria)

Adds a filter applied to the data source table.

Parameters

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

Return

DataSourceTable — The data source table, 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

addSortSpec(columnName, ascending)

Adds a sort spec on a column in the data source table.

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

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

addSortSpec(columnName, sortOrder)

Adds a sort spec on a column in the data source table.

Parameters

NameTypeDescription
columnNameStringThe name of the column to sort.
sortOrderSortOrderThe sort order.

Return

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

cancelDataRefresh()

Cancels the data refresh associated with this object if it's currently running.

This example shows how to cancel a formula refresh.

const spreadsheet = SpreadsheetApp.getActive();
const formula = 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

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

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

getColumns()

Gets all the data source columns added to the data source table.

Return

DataSourceTableColumn[] — A list of data source table columns.

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

Return

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

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

getRange()

Gets the Range this data source table spans.

Return

Range — The range.

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

getRowLimit()

Returns the row limit for the data source table.

Return

Integer — The row limit for the data source table, or null 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 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 table.

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

isSyncingAllColumns()

Returns whether the data source table is syncing all columns in the associated data source.

Return

BooleanTrue if the data source table is syncing all columns in the associated data source, or false otherwise.

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

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

removeAllColumns()

Removes all the columns in the data source table.

Return

DataSourceTable — The data source table, 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

removeAllSortSpecs()

Removes all the sort specs in the data source table.

Return

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

setRowLimit(rowLimit)

Updates the row limit for the data source table. If the provided row limit is null, then updates the data source table to use the default max row limit as in Google Sheets UI.

Parameters

NameTypeDescription
rowLimitIntegerThe new row limit for the data table. If null, updates the table to use the default row limit.

Return

DataSourceTable — The data source table, 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

syncAllColumns()

Sync all current and future columns in the associated data source to the data source table.

Return

DataSourceTable — The data source table, 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