Class SpreadsheetApp

SpreadsheetApp

Access and create Google Sheets files. This class is the parent class for the Spreadsheet service .

Properties

Property	Type	Description
`AutoFillSeries`	`AutoFillSeries`	An enumeration of the types of series used to calculate auto-filled values.
`BandingTheme`	`BandingTheme`	An enumeration of the possible banding themes.
`BooleanCriteria`	`BooleanCriteria`	An enumeration of conditional formatting boolean criteria.
`BorderStyle`	`BorderStyle`	An enumeration of the valid styles for setting borders on a `Range` .
`ColorType`	`ColorType`	An enumeration of possible color types.
`CopyPasteType`	`CopyPasteType`	An enumeration of the possible paste types.
`DataExecutionErrorCode`	`DataExecutionErrorCode`	An enumeration of the possible data execution error codes.
`DataExecutionState`	`DataExecutionState`	An enumeration of the possible data execution states.
`DataSourceParameterType`	`DataSourceParameterType`	An enumeration of the possible data source parameter types.
`DataSourceRefreshScope`	`DataSourceRefreshScope`	An enumeration of possible data source refresh scopes.
`DataSourceType`	`DataSourceType`	An enumeration of the possible data source types.
`DataValidationCriteria`	`DataValidationCriteria`	An enumeration representing the data validation criteria that can be set on a range.
`DateTimeGroupingRuleType`	`DateTimeGroupingRuleType`	An enumeration of date time grouping rule.
`DeveloperMetadataLocationType`	`DeveloperMetadataLocationType`	An enumeration of possible developer metadata location types.
`DeveloperMetadataVisibility`	`DeveloperMetadataVisibility`	An enumeration of the possible developer metadata visibilities.
`Dimension`	`Dimension`	An enumeration of the possible dimensions of a spreadsheet.
`Direction`	`Direction`	A enumeration of the possible directions that one can move within a spreadsheet using the arrow keys.
`FrequencyType`	`FrequencyType`	An enumeration of possible frequency types.
`GroupControlTogglePosition`	`GroupControlTogglePosition`	An enumeration of the positions that the group control toggle can be in.
`InterpolationType`	`InterpolationType`	An enumeration of conditional format gradient interpolation types.
`PivotTableSummarizeFunction`	`PivotTableSummarizeFunction`	An enumeration of the functions that may be used to summarize values in a pivot table.
`PivotValueDisplayType`	`PivotValueDisplayType`	An enumeration of the ways that a pivot value may be displayed.
`ProtectionType`	`ProtectionType`	An enumeration representing the parts of a spreadsheet that can be protected from edits.
`RecalculationInterval`	`RecalculationInterval`	An enumeration of the possible intervals that can be used in spreadsheet recalculation.
`RelativeDate`	`RelativeDate`	An enumeration of relative date options for calculating a value to be used in date-based `BooleanCriteria` .
`SheetType`	`SheetType`	An enumeration of the different types of sheets that can exist in a spreadsheet.
`SortOrder`	`SortOrder`	An enumeration of sort order.
`TextDirection`	`TextDirection`	An enumeration of valid text directions.
`TextToColumnsDelimiter`	`TextToColumnsDelimiter`	An enumeration of the preset delimiters for split text to columns.
`ThemeColorType`	`ThemeColorType`	An enumeration of possible theme color types.
`ValueType`	`ValueType`	An enumeration of value types returned by `Range.getValue()` and `Range.getValues()` from the Range class of the Spreadsheet service. The enumeration values listed below are in addition to `Number` , `Boolean` , `Date` , or `String` .
`WrapStrategy`	`WrapStrategy`	An enumeration of the strategies used for wrapping cells.

Methods

Method	Return type	Brief description
`create(name)`	`Spreadsheet`	Creates a new spreadsheet with the given name.
`create(name, rows, columns)`	`Spreadsheet`	Creates a new spreadsheet with the given name and the specified number of rows and columns.
`enableAllDataSourcesExecution()`	`void`	Turns data execution on for all types of data sources.
`enableBigQueryExecution()`	`void`	Turns data execution on for BigQuery data sources.
`enableLookerExecution()`	`void`	Turns data execution on for Looker data sources.
`flush()`	`void`	Applies all pending Spreadsheet changes.
`getActive()`	`Spreadsheet`	Returns the currently active spreadsheet, or `null` if there is none.
`getActiveRange()`	`Range`	Returns the selected range in the active sheet, or `null` if there is no active range.
`getActiveRangeList()`	`RangeList`	Returns the list of active ranges in the active sheet or `null` if there are no ranges selected.
`getActiveSheet()`	`Sheet`	Gets the active sheet in a spreadsheet.
`getActiveSpreadsheet()`	`Spreadsheet`	Returns the currently active spreadsheet, or `null` if there is none.
`getCurrentCell()`	`Range`	Returns the current (highlighted) cell that is selected in one of the active ranges in the active sheet or `null` if there is no current cell.
`getSelection()`	`Selection`	Returns the current `Selection` in the spreadsheet.
`getUi()`	`Ui`	Returns an instance of the spreadsheet's user-interface environment that allows the script to add features like menus, dialogs, and sidebars.
`newCellImage()`	`CellImageBuilder`	Creates a builder for a `CellImage` .
`newColor()`	`ColorBuilder`	Creates a builder for a `Color` .
`newConditionalFormatRule()`	`ConditionalFormatRuleBuilder`	Creates a builder for a conditional formatting rule.
`newDataSourceSpec()`	`DataSourceSpecBuilder`	Creates a builder for a `DataSourceSpec` .
`newDataValidation()`	`DataValidationBuilder`	Creates a builder for a data validation rule.
`newFilterCriteria()`	`FilterCriteriaBuilder`	Creates a builder for a `FilterCriteria` .
`newRichTextValue()`	`RichTextValueBuilder`	Creates a builder for a Rich Text value.
`newTextStyle()`	`TextStyleBuilder`	Creates a builder for a text style.
`open(file)`	`Spreadsheet`	Opens the spreadsheet that corresponds to the given File object.
`openById(id)`	`Spreadsheet`	Opens the spreadsheet with the given ID.
`openByUrl(url)`	`Spreadsheet`	Opens the spreadsheet with the given URL.
`setActiveRange(range)`	`Range`	Sets the specified range as the `active range` , with the top left cell in the range as the `current cell` .
`setActiveRangeList(rangeList)`	`RangeList`	Sets the specified list of ranges as the `active ranges` .
`setActiveSheet(sheet)`	`Sheet`	Sets the active sheet in a spreadsheet.
`setActiveSheet(sheet, restoreSelection)`	`Sheet`	Sets the active sheet in a spreadsheet, with the option to restore the most recent selection within that sheet.
`setActiveSpreadsheet(newActiveSpreadsheet)`	`void`	Sets the active spreadsheet.
`setCurrentCell(cell)`	`Range`	Sets the specified cell as the `current cell` .

Detailed documentation

`create(name)`

Creates a new spreadsheet with the given name.

 // 
  
 The 
  
 code 
  
 below 
  
 creates 
  
 a 
  
 new 
  
 spreadsheet 
  
 "Finances" 
  
 and 
  
 logs 
  
 the 
  
 URL 
  
 for 
  
 it 
 var 
  
 ssNew 
  
 = 
  
 SpreadsheetApp 
 . 
 create 
 ( 
 "Finances" 
 ); 
 Logger 
 . 
 log 
 ( 
 ssNew 
 . 
 getUrl 
 ());

Parameters

Name	Type	Description
`name`	`String`	The name for the spreadsheet.

Return

Spreadsheet — a new spreadsheet

Authorization

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

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

`create(name, rows, columns)`

Creates a new spreadsheet with the given name and the specified number of rows and columns.

 // 
  
 The 
  
 code 
  
 below 
  
 creates 
  
 a 
  
 new 
  
 spreadsheet 
  
 "Finances" 
  
 with 
  
 50 
  
 rows 
  
 and 
  
 5 
  
 columns 
  
 and 
  
 logs 
  
 the 
 // 
  
 URL 
  
 for 
  
 it 
 var 
  
 ssNew 
  
 = 
  
 SpreadsheetApp 
 . 
 create 
 ( 
 "Finances" 
 , 
  
 50 
 , 
  
 5 
 ); 
 Logger 
 . 
 log 
 ( 
 ssNew 
 . 
 getUrl 
 ());

Parameters

Name	Type	Description
`name`	`String`	The name for the spreadsheet.
`rows`	`Integer`	The number of rows for the spreadsheet.
`columns`	`Integer`	The number of columns for the spreadsheet.

Return

Spreadsheet — a new spreadsheet

Authorization

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

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

`enableAllDataSourcesExecution()`

Turns data execution on for all types of data sources.

Data execution throws an exception if the data source type isn't turned on. Use this method to turn data execution on for all data source types.

 // 
  
 Turns 
  
 data 
  
 execution 
  
 on 
  
 for 
  
 all 
  
 types 
  
 of 
  
 data 
  
 sources 
 . 
 SpreadsheetApp 
 . 
 enableAllDataSourcesExecution 
 (); 
 // 
  
 Opens 
  
 the 
  
 spreadsheet 
  
 file 
  
 by 
  
 its 
  
 ID 
 . 
  
 If 
  
 you 
  
 created 
  
 your 
  
 script 
  
 from 
  
 a 
  
 Google 
  
 Sheets 
  
 file 
 , 
 // 
  
 use 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
 . 
 // 
  
 TODO 
 ( 
 developer 
 ): 
  
 Replace 
  
 the 
  
 ID 
  
 with 
  
 your 
  
 own 
 . 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 'abc123456' 
 ); 
 // 
  
 Gets 
  
 the 
  
 first 
  
 data 
  
 source 
  
 sheet 
  
 in 
  
 the 
  
 spreadsheet 
  
 and 
  
 refreshes 
  
 the 
  
 data 
 . 
 ss 
 . 
 getDataSourceSheets 
 ()[ 
 0 
 ] 
 . 
 refreshData 
 ();

Authorization

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

https://www.googleapis.com/auth/bigquery.readonly

`enableBigQueryExecution()`

Turns data execution on for BigQuery data sources.

Data execution for BigQuery data source throws an exception if not turned on.

 // 
  
 Turns 
  
 data 
  
 execution 
  
 on 
  
 for 
  
 BigQuery 
  
 data 
  
 sources 
 . 
 SpreadsheetApp 
 . 
 enableBigQueryExecution 
 (); 
 // 
  
 Opens 
  
 the 
  
 spreadsheet 
  
 file 
  
 by 
  
 its 
  
 ID 
 . 
  
 If 
  
 you 
  
 created 
  
 your 
  
 script 
  
 from 
  
 a 
  
 Google 
  
 Sheets 
  
 file 
 , 
 // 
  
 use 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
 . 
 // 
  
 TODO 
 ( 
 developer 
 ): 
  
 Replace 
  
 the 
  
 ID 
  
 with 
  
 your 
  
 own 
 . 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 'abc123456' 
 ); 
 // 
  
 Gets 
  
 the 
  
 first 
  
 data 
  
 source 
  
 sheet 
  
 in 
  
 the 
  
 spreadsheet 
  
 and 
  
 refreshes 
  
 the 
  
 BigQuery 
  
 data 
 . 
 ss 
 . 
 getDataSourceSheets 
 ()[ 
 0 
 ] 
 . 
 refreshData 
 ();

Authorization

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

https://www.googleapis.com/auth/bigquery.readonly

`enableLookerExecution()`

Turns data execution on for Looker data sources.

Data execution for Looker data source throws an exception if not turned on.

 // 
  
 Turns 
  
 data 
  
 execution 
  
 on 
  
 for 
  
 Looker 
  
 data 
  
 sources 
 . 
 SpreadsheetApp 
 . 
 enableLookerExecution 
 (); 
 // 
  
 Opens 
  
 the 
  
 spreadsheet 
  
 file 
  
 by 
  
 its 
  
 ID 
 . 
  
 If 
  
 you 
  
 created 
  
 your 
  
 script 
  
 from 
  
 a 
  
 Google 
  
 Sheets 
  
 file 
 , 
 // 
  
 use 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
 . 
 // 
  
 TODO 
 ( 
 developer 
 ): 
  
 Replace 
  
 the 
  
 ID 
  
 with 
  
 your 
  
 own 
 . 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 'abc123456' 
 ); 
 // 
  
 Gets 
  
 the 
  
 first 
  
 data 
  
 source 
  
 sheet 
  
 in 
  
 the 
  
 spreadsheet 
  
 and 
  
 refreshes 
  
 the 
  
 associated 
  
 Looker 
 // 
  
 data 
 . 
 ss 
 . 
 getDataSourceSheets 
 ()[ 
 0 
 ] 
 . 
 refreshData 
 ();

`flush()`

Applies all pending Spreadsheet changes.

Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

 // 
  
 The 
  
 code 
  
 below 
  
 changes 
  
 the 
  
 background 
  
 color 
  
 of 
  
 cells 
  
 A1 
  
 and 
  
 B1 
  
 twenty 
  
 times 
 . 
  
 You 
  
 should 
  
 be 
 // 
  
 able 
  
 to 
  
 see 
  
 the 
  
 updates 
  
 live 
  
 in 
  
 the 
  
 spreadsheet 
 . 
  
 If 
  
 flush 
 () 
  
 is 
  
 not 
  
 called 
 , 
  
 the 
  
 updates 
  
 may 
 // 
  
 be 
  
 applied 
  
 live 
  
 or 
  
 may 
  
 all 
  
 be 
  
 applied 
  
 at 
  
 once 
  
 when 
  
 the 
  
 script 
  
 completes 
 . 
 function 
  
 colors 
 () 
  
 { 
  
 var 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
  
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
 < 
 20 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 if 
  
 (( 
 i 
  
 % 
  
 2 
 ) 
  
 == 
  
 0 
 ) 
  
 { 
  
 sheet 
 . 
 getRange 
 ( 
 'A1' 
 ) 
 . 
 setBackground 
 ( 
 'green' 
 ); 
  
 sheet 
 . 
 getRange 
 ( 
 'B1' 
 ) 
 . 
 setBackground 
 ( 
 'red' 
 ); 
  
 } 
  
 else 
  
 { 
  
 sheet 
 . 
 getRange 
 ( 
 'A1' 
 ) 
 . 
 setBackground 
 ( 
 'red' 
 ); 
  
 sheet 
 . 
 getRange 
 ( 
 'B1' 
 ) 
 . 
 setBackground 
 ( 
 'green' 
 ); 
  
 } 
  
 SpreadsheetApp 
 . 
 flush 
 (); 
  
 } 
 }

`getActive()`

Returns the currently active spreadsheet, or null if there is none.

Functions that are run in the context of a spreadsheet can get a reference to the corresponding Spreadsheet object by calling this function.

 // The code below logs the URL for the active spreadsheet. 
 Logger 
 . 
 log 
 ( 
 SpreadsheetApp 
 . 
 getActive 
 (). 
 getUrl 
 ());

Return

Spreadsheet — the active Spreadsheet 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

`getActiveRange()`

Returns the selected range in the active sheet, or null if there is no active range. If multiple ranges are selected this method returns only the last selected range.

This generally means the range that a user has selected in the active sheet, but in a custom function it refers to the cell being actively recalculated.

 // 
  
 The 
  
 code 
  
 below 
  
 logs 
  
 the 
  
 background 
  
 color 
  
 for 
  
 the 
  
 active 
  
 range 
 . 
 var 
  
 colorObject 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveRange 
 () 
 . 
 getBackgroundObject 
 (); 
 // 
  
 Assume 
  
 the 
  
 color 
  
 has 
  
 ColorType 
 . 
 RGB 
 . 
 Logger 
 . 
 log 
 ( 
 colorObject 
 . 
 asRgbColor 
 () 
 . 
 asHexString 
 ());

Return

Range — The active 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

`getActiveRangeList()`

Returns the list of active ranges in the active sheet or null if there are no ranges selected. The active range containing the current highlighted cell is placed last in the list.

If there is a single range selected, this behaves as a getActiveRange() call.

 // 
  
 Returns 
  
 the 
  
 list 
  
 of 
  
 active 
  
 ranges 
 . 
 var 
  
 rangeList 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveRangeList 
 ();

Return

RangeList — the list of active ranges

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

`getActiveSheet()`

Gets the active sheet in a spreadsheet.

The active sheet in a spreadsheet is the sheet that is being displayed in the spreadsheet UI.

 // The code below logs the name of the active sheet. 
 Logger 
 . 
 log 
 ( 
 SpreadsheetApp 
 . 
 getActiveSheet 
 (). 
 getName 
 ());

Return

Sheet — the active Sheet 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

`getActiveSpreadsheet()`

Returns the currently active spreadsheet, or null if there is none.

Functions that are run in the context of a spreadsheet can get a reference to the corresponding Spreadsheet object by calling this function.

 // The code below logs the URL for the active spreadsheet. 
 Logger 
 . 
 log 
 ( 
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getUrl 
 ());

Return

Spreadsheet — the active Spreadsheet 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

`getCurrentCell()`

Returns the current (highlighted) cell that is selected in one of the active ranges in the active sheet or null if there is no current cell.

 // 
  
 Returns 
  
 the 
  
 current 
  
 highlighted 
  
 cell 
  
 in 
  
 the 
  
 one 
  
 of 
  
 the 
  
 active 
  
 ranges 
 . 
 var 
  
 currentCell 
  
 = 
  
 SpreadsheetApp 
 . 
 getCurrentCell 
 ();

Return

Range — the current cell

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

`getSelection()`

Returns the current Selection in the spreadsheet.

 var 
  
 selection 
  
 = 
  
 SpreadsheetApp 
 . 
 getSelection 
 (); 
 var 
  
 currentCell 
  
 = 
  
 selection 
 . 
 getCurrentCell 
 ();

Return

Selection — the current selection

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

`getUi()`

Returns an instance of the spreadsheet's user-interface environment that allows the script to add features like menus, dialogs, and sidebars. A script can only interact with the UI for the current instance of an open spreadsheet, and only if the script is bound to the spreadsheet. For more information, see the guides to menus and dialogs and sidebars .

 // Add a custom menu to the active spreadsheet, including a separator and a sub-menu. 
 function 
  
 onOpen 
 ( 
e ) 
  
 { 
  
 SpreadsheetApp 
 . 
 getUi 
 () 
  
 . 
 createMenu 
 ( 
 'My Menu' 
 ) 
  
 . 
 addItem 
 ( 
 'My menu item' 
 , 
  
 'myFunction' 
 ) 
  
 . 
 addSeparator 
 () 
  
 . 
 addSubMenu 
 ( 
 SpreadsheetApp 
 . 
 getUi 
 (). 
 createMenu 
 ( 
 'My sub-menu' 
 ) 
  
 . 
 addItem 
 ( 
 'One sub-menu item' 
 , 
  
 'mySecondFunction' 
 ) 
  
 . 
 addItem 
 ( 
 'Another sub-menu item' 
 , 
  
 'myThirdFunction' 
 )) 
  
 . 
 addToUi 
 (); 
 }

Return

Ui — an instance of this spreadsheet's user-interface environment

`newCellImage()`

Creates a builder for a CellImage .

 // 
  
 Opens 
  
 the 
  
 spreadsheet 
  
 file 
  
 by 
  
 its 
  
 URL 
 . 
  
 If 
  
 you 
  
 created 
  
 your 
  
 script 
  
 from 
  
 within 
  
 a 
 // 
  
 Google 
  
 Sheets 
  
 file 
 , 
  
 you 
  
 can 
  
 use 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
  
 instead 
 . 
 // 
  
 TODO 
 ( 
 developer 
 ): 
  
 Replace 
  
 the 
  
 URL 
  
 with 
  
 your 
  
 own 
 . 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openByUrl 
 ( 
 'https://docs.google.com/spreadsheets/d/abc123456/edit' 
 ); 
 // 
  
 Gets 
  
 Sheet1 
  
 by 
  
 its 
  
 name 
 . 
 const 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheetByName 
 ( 
 'Sheet1' 
 ); 
 // 
  
 Gets 
  
 cell 
  
 A1 
  
 on 
  
 Sheet1 
 . 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1' 
 ); 
 // 
  
 Builds 
  
 an 
  
 image 
  
 using 
  
 a 
  
 source 
  
 URL 
 . 
 const 
  
 cellImage 
  
 = 
  
 SpreadsheetApp 
 . 
 newCellImage 
 () 
  
 . 
 setSourceUrl 
 ( 
 'https://www.gstatic.com/images/branding/productlogos/apps_script/v10/web-64dp/logo_apps_script_color_1x_web_64dp.png' 
 ) 
  
 . 
 build 
 (); 
 // 
  
 Sets 
  
 the 
  
 image 
  
 in 
  
 cell 
  
 A1 
 . 
 range 
 . 
 setValue 
 ( 
 cellImage 
 );

Return

CellImageBuilder — The new builder.

`newColor()`

Creates a builder for a Color .

 var 
  
 rgbColor 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
 . 
 setRgbColor 
 ( 
 "#FF0000" 
 ) 
 . 
 build 
 ();

Return

ColorBuilder — The new builder.

`newConditionalFormatRule()`

Creates a builder for a conditional formatting rule.

 // 
  
 Adds 
  
 a 
  
 conditional 
  
 format 
  
 rule 
  
 to 
  
 a 
  
 sheet 
  
 that 
  
 causes 
  
 all 
  
 cells 
  
 in 
  
 range 
  
 A1 
 : 
 B3 
  
 to 
  
 turn 
  
 red 
 // 
  
 if 
  
 they 
  
 contain 
  
 a 
  
 number 
  
 between 
  
 1 
  
 and 
  
 10. 
 var 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 (). 
 getActiveSheet 
 (); 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 var 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberBetween 
 ( 
 1 
 , 
  
 10 
 ) 
  
 . 
 setBackground 
 ( 
 "#FF0000" 
 ) 
  
 . 
 setRanges 
 ( 
 [ 
 range 
 ] 
 ) 
  
 . 
 build 
 () 
 var 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

Return

ConditionalFormatRuleBuilder — the new builder.

`newDataSourceSpec()`

Creates a builder for a DataSourceSpec .

 // 
  
 Opens 
  
 the 
  
 spreadsheet 
  
 file 
  
 by 
  
 its 
  
 URL 
 . 
  
 If 
  
 you 
  
 created 
  
 your 
  
 script 
  
 from 
  
 within 
  
 a 
 // 
  
 Google 
  
 Sheets 
  
 file 
 , 
  
 you 
  
 can 
  
 use 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
  
 instead 
 . 
 // 
  
 TODO 
 ( 
 developer 
 ): 
  
 Replace 
  
 the 
  
 URL 
  
 with 
  
 your 
  
 own 
 . 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openByUrl 
 ( 
 'https://docs.google.com/spreadsheets/d/abc123456/edit' 
 ); 
 // 
  
 Enables 
  
 BigQuery 
 . 
 SpreadsheetApp 
 . 
 enableBigQueryExecution 
 (); 
 // 
  
 Builds 
  
 a 
  
 data 
  
 source 
  
 specification 
 . 
 // 
  
 TODO 
  
 ( 
 developer 
 ): 
  
 Update 
  
 the 
  
 project 
  
 ID 
  
 to 
  
 your 
  
 own 
  
 Google 
  
 Cloud 
  
 project 
  
 ID 
 . 
 const 
  
 dataSourceSpec 
  
 = 
  
 SpreadsheetApp 
 . 
 newDataSourceSpec 
 () 
  
 . 
 asBigQuery 
 () 
  
 . 
 setProjectId 
 ( 
 'project-id-1' 
 ) 
  
 . 
 setTableProjectId 
 ( 
 'bigquery-public-data' 
 ) 
  
 . 
 setDatasetId 
 ( 
 'ncaa_basketball' 
 ) 
  
 . 
 setTableId 
 ( 
 'mbb_historical_teams_games' 
 ) 
  
 . 
 build 
 (); 
 // 
  
 Adds 
  
 the 
  
 data 
  
 source 
  
 and 
  
 its 
  
 data 
  
 to 
  
 the 
  
 spreadsheet 
 . 
 ss 
 . 
 insertDataSourceSheet 
 ( 
 dataSourceSpec 
 );

Return

DataSourceSpecBuilder — The new builder.

`newDataValidation()`

Creates a builder for a data validation rule.

 var 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 () 
 . 
 getRange 
 ( 
 'A1' 
 ); 
 var 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newDataValidation 
 () 
  
 . 
 requireNumberBetween 
 ( 
 1 
 , 
  
 100 
 ) 
  
 . 
 setAllowInvalid 
 ( 
 false 
 ) 
  
 . 
 setHelpText 
 ( 
 'Number must be between 1 and 100.' 
 ) 
  
 . 
 build 
 (); 
 cell 
 . 
 setDataValidation 
 ( 
 rule 
 );

Return

DataValidationBuilder — The new builder.

`newFilterCriteria()`

Creates a builder for a FilterCriteria .

 // 
  
 Opens 
  
 the 
  
 spreadsheet 
  
 file 
  
 by 
  
 its 
  
 URL 
 . 
  
 If 
  
 you 
  
 created 
  
 your 
  
 script 
  
 from 
  
 within 
  
 a 
 // 
  
 Google 
  
 Sheets 
  
 file 
 , 
  
 you 
  
 can 
  
 use 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
  
 instead 
 . 
 // 
  
 TODO 
 ( 
 developer 
 ): 
  
 Replace 
  
 the 
  
 URL 
  
 with 
  
 your 
  
 own 
 . 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openByUrl 
 ( 
 'https://docs.google.com/spreadsheets/d/abc123456/edit' 
 ); 
 // 
  
 Gets 
  
 Sheet1 
  
 by 
  
 its 
  
 name 
 . 
 const 
  
 sheet 
  
 = 
  
 ss 
 . 
 getSheetByName 
 ( 
 'Sheet1' 
 ); 
 // 
  
 Sets 
  
 the 
  
 range 
  
 to 
  
 A1 
 : 
 D20 
 . 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:D20' 
 ); 
 // 
  
 Creates 
  
 a 
  
 filter 
  
 and 
  
 applies 
  
 it 
  
 to 
  
 the 
  
 specified 
  
 range 
 . 
 range 
 . 
 createFilter 
 (); 
 // 
  
 Gets 
  
 the 
  
 current 
  
 filter 
  
 for 
  
 the 
  
 range 
  
 and 
  
 creates 
  
 filter 
  
 criteria 
  
 that 
  
 only 
  
 shows 
  
 cells 
 // 
  
 that 
  
 aren 
 't empty. 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
 . 
 whenCellNotEmpty 
 () 
 . 
 build 
 (); 
 // 
  
 Sets 
  
 the 
  
 criteria 
  
 to 
  
 column 
  
 C 
 . 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 3 
 , 
  
 criteria 
 );

Return

FilterCriteriaBuilder — the new builder

`newRichTextValue()`

Creates a builder for a Rich Text value.

 // 
  
 Sets 
  
 cell 
  
 A1 
  
 to 
  
 have 
  
 the 
  
 text 
  
 "Hello world" 
 , 
  
 with 
  
 "Hello" 
  
 bolded 
 . 
 var 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 () 
 . 
 getRange 
 ( 
 'A1' 
 ); 
 var 
  
 bold 
  
 = 
  
 SpreadsheetApp 
 . 
 newTextStyle 
 () 
 . 
 setBold 
 ( 
 true 
 ) 
 . 
 build 
 (); 
 var 
  
 value 
  
 = 
  
 SpreadsheetApp 
 . 
 newRichTextValue 
 () 
  
 . 
 setText 
 ( 
 "Hello world" 
 ) 
  
 . 
 setTextStyle 
 ( 
 0 
 , 
  
 5 
 , 
  
 bold 
 ) 
  
 . 
 build 
 (); 
 cell 
 . 
 setRichTextValue 
 ( 
 value 
 );

Return

RichTextValueBuilder — The new builder.

`newTextStyle()`

Creates a builder for a text style.

 // 
  
 Sets 
  
 range 
  
 A1 
 : 
 B3 
  
 to 
  
 have 
  
 red 
 , 
  
 size 
  
 22 
 , 
  
 bolded 
 , 
  
 underlined 
  
 text 
 . 
 var 
  
 range 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 () 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 var 
  
 style 
  
 = 
  
 SpreadsheetApp 
 . 
 newTextStyle 
 () 
  
 . 
 setForegroundColor 
 ( 
 "red" 
 ) 
  
 . 
 setFontSize 
 ( 
 22 
 ) 
  
 . 
 setBold 
 ( 
 true 
 ) 
  
 . 
 setUnderline 
 ( 
 true 
 ) 
  
 . 
 build 
 (); 
 range 
 . 
 setTextStyle 
 ( 
 style 
 );

Return

TextStyleBuilder — The new builder.

`open(file)`

Opens the spreadsheet that corresponds to the given File object.

 // 
  
 Get 
  
 any 
  
 starred 
  
 spreadsheets 
  
 from 
  
 Google 
  
 Drive 
 , 
  
 then 
  
 open 
  
 the 
  
 spreadsheets 
  
 and 
  
 log 
  
 the 
  
 name 
 // 
  
 of 
  
 the 
  
 first 
  
 sheet 
  
 within 
  
 each 
  
 spreadsheet 
 . 
 var 
  
 files 
  
 = 
  
 DriveApp 
 . 
 searchFiles 
 ( 
  
 'starred = true and mimeType = "' 
  
 + 
  
 MimeType 
 . 
 GOOGLE_SHEETS 
  
 + 
  
 '"' 
 ); 
 while 
  
 ( 
 files 
 . 
 hasNext 
 ()) 
  
 { 
  
 var 
  
 spreadsheet 
  
 = 
  
 SpreadsheetApp 
 . 
 open 
 ( 
 files 
 . 
 next 
 ()); 
  
 var 
  
 sheet 
  
 = 
  
 spreadsheet 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
  
 Logger 
 . 
 log 
 ( 
 sheet 
 . 
 getName 
 ()); 
 }

Parameters

Name	Type	Description
`file`	`File`	The file to open.

Return

Spreadsheet — the spreadsheet

Authorization

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

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

`openById(id)`

Opens the spreadsheet with the given ID. A spreadsheet ID can be extracted from its URL. For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is "abc1234567".

 // 
  
 The 
  
 code 
  
 below 
  
 opens 
  
 a 
  
 spreadsheet 
  
 using 
  
 its 
  
 ID 
  
 and 
  
 logs 
  
 the 
  
 name 
  
 for 
  
 it 
 . 
 // 
  
 Note 
  
 that 
  
 the 
  
 spreadsheet 
  
 is 
  
 NOT 
  
 physically 
  
 opened 
  
 on 
  
 the 
  
 client 
  
 side 
 . 
 // 
  
 It 
  
 is 
  
 opened 
  
 on 
  
 the 
  
 server 
  
 only 
  
 ( 
 for 
  
 modification 
  
 by 
  
 the 
  
 script 
 ) 
 . 
 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 "abc1234567" 
 ); 
 Logger 
 . 
 log 
 ( 
 ss 
 . 
 getName 
 ());

Parameters

Name	Type	Description
`id`	`String`	The unique identifier for the spreadsheet.

Return

Spreadsheet — the Spreadsheet object with the given id

Authorization

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

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

`openByUrl(url)`

Opens the spreadsheet with the given URL. Throws a scripting exception if the URL doesn't exist or the user doesn't have permission to access it.

 // 
  
 Opens 
  
 a 
  
 spreadsheet 
  
 by 
  
 its 
  
 URL 
  
 and 
  
 logs 
  
 its 
  
 name 
 . 
 // 
  
 Note 
  
 that 
  
 the 
  
 spreadsheet 
  
 doesn 
 't physically open on the client side. 
 // 
  
 It 
  
 opens 
  
 on 
  
 the 
  
 server 
  
 only 
  
 ( 
 for 
  
 modification 
  
 by 
  
 the 
  
 script 
 ) 
 . 
 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openByUrl 
 ( 
  
 'https://docs.google.com/spreadsheets/d/abc1234567/edit' 
 ); 
 console 
 . 
 log 
 ( 
 ss 
 . 
 getName 
 ());

Parameters

Name	Type	Description
`url`	`String`	The URL for the spreadsheet.

Return

Spreadsheet — The spreadsheet object with the given URL.

Authorization

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

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

`setActiveRange(range)`

Sets the specified range as the active range , with the top left cell in the range as the current cell .

The spreadsheet UI displays the sheet that contains the chosen range and selects the cells defined in the chosen range.

 // 
  
 The 
  
 code 
  
 below 
  
 sets 
  
 range 
  
 C1 
 : 
 D4 
  
 in 
  
 the 
  
 first 
  
 sheet 
  
 as 
  
 the 
  
 active 
  
 range 
 . 
 var 
  
 range 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
 . 
 getSheets 
 ()[ 
 0 
 ] 
 . 
 getRange 
 ( 
 'C1:D4' 
 ); 
 SpreadsheetApp 
 . 
 setActiveRange 
 ( 
 range 
 ); 
 var 
  
 selection 
  
 = 
  
 SpreadsheetApp 
 . 
 getSelection 
 (); 
 // 
  
 Current 
  
 cell 
 : 
  
 C1 
 var 
  
 currentCell 
  
 = 
  
 selection 
 . 
 getCurrentCell 
 (); 
 // 
  
 Active 
  
 Range 
 : 
  
 C1 
 : 
 D4 
 var 
  
 activeRange 
  
 = 
  
 selection 
 . 
 getActiveRange 
 ();

Parameters

Name	Type	Description
`range`	`Range`	The range to be made the active range.

Return

Range — the new active Range

`setActiveRangeList(rangeList)`

Sets the specified list of ranges as the active ranges . The last range in the list is set as the active range .

 // 
  
 The 
  
 code 
  
 below 
  
 sets 
  
 ranges 
  
 [ 
 D4 
 , 
  
 B2 
 : 
 C4 
 ] 
  
 in 
  
 the 
  
 active 
  
 sheet 
  
 as 
  
 the 
  
 active 
  
 ranges 
 . 
 var 
  
 rangeList 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 () 
 . 
 getRanges 
 ([ 
 'D4' 
 , 
  
 'B2:C4' 
 ]); 
 SpreadsheetApp 
 . 
 setActiveRangeList 
 ( 
 rangeList 
 ); 
 var 
  
 selection 
  
 = 
  
 SpreadsheetApp 
 . 
 getSelection 
 (); 
 // 
  
 Current 
  
 cell 
 : 
  
 B2 
 var 
  
 currentCell 
  
 = 
  
 selection 
 . 
 getCurrentCell 
 (); 
 // 
  
 Active 
  
 range 
 : 
  
 B2 
 : 
 C4 
 var 
  
 activeRange 
  
 = 
  
 selection 
 . 
 getActiveRange 
 (); 
 // 
  
 Active 
  
 range 
  
 list 
 : 
  
 [ 
 D4 
 , 
  
 B2 
 : 
 C4 
 ] 
 var 
  
 activeRangeList 
  
 = 
  
 selection 
 . 
 getActiveRangeList 
 ();

Parameters

Name	Type	Description
`rangeList`	`RangeList`	The list of ranges to select.

Return

RangeList — the newly selected list of ranges

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

`setActiveSheet(sheet)`

Sets the active sheet in a spreadsheet. The Google Sheets UI displays the chosen sheet unless the sheet belongs to a different spreadsheet.

 // 
  
 The 
  
 code 
  
 below 
  
 makes 
  
 the 
  
 2 
 nd 
  
 sheet 
  
 active 
  
 in 
  
 the 
  
 active 
  
 spreadsheet 
 . 
 var 
  
 spreadsheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 SpreadsheetApp 
 . 
 setActiveSheet 
 ( 
 spreadsheet 
 . 
 getSheets 
 ()[ 
 1 
 ]);

Parameters

Name	Type	Description
`sheet`	`Sheet`	The new active sheet.

Return

Sheet — the sheet that has been made the new active sheet

`setActiveSheet(sheet, restoreSelection)`

Sets the active sheet in a spreadsheet, with the option to restore the most recent selection within that sheet. The Google Sheets UI displays the chosen sheet unless the sheet belongs to a different spreadsheet.

 var 
  
 spreadsheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (); 
 var 
  
 firstSheet 
  
 = 
  
 spreadsheet 
 . 
 getSheets 
 ()[ 
 0 
 ]; 
 var 
  
 secondSheet 
  
 = 
  
 spreadsheet 
 . 
 getSheets 
 ()[ 
 1 
 ]; 
 // 
  
 Set 
  
 the 
  
 first 
  
 sheet 
  
 as 
  
 the 
  
 active 
  
 sheet 
  
 and 
  
 select 
  
 the 
  
 range 
  
 D4 
 : 
 F4 
 . 
 spreadsheet 
 . 
 setActiveSheet 
 ( 
 firstSheet 
 ) 
 . 
 getRange 
 ( 
 'D4:F4' 
 ) 
 . 
 activate 
 (); 
 // 
  
 Switch 
  
 to 
  
 the 
  
 second 
  
 sheet 
  
 to 
  
 do 
  
 some 
  
 work 
 . 
 spreadsheet 
 . 
 setActiveSheet 
 ( 
 secondSheet 
 ); 
 // 
  
 Switch 
  
 back 
  
 to 
  
 first 
  
 sheet 
 , 
  
 and 
  
 restore 
  
 its 
  
 selection 
 . 
 spreadsheet 
 . 
 setActiveSheet 
 ( 
 firstSheet 
 , 
  
 true 
 ); 
 // 
  
 The 
  
 selection 
  
 of 
  
 first 
  
 sheet 
  
 is 
  
 restored 
 , 
  
 and 
  
 it 
  
 logs 
  
 D4 
 : 
 F4 
 var 
  
 range 
  
 = 
  
 spreadsheet 
 . 
 getActiveSheet 
 () 
 . 
 getSelection 
 () 
 . 
 getActiveRange 
 (); 
 Logger 
 . 
 log 
 ( 
 range 
 . 
 getA1Notation 
 ());

Parameters

Name	Type	Description
`sheet`	`Sheet`	The new active sheet.
`restoreSelection`	`Boolean`	If `true` , the most recent selection of the new active sheet becomes selected again as the new sheet becomes active; if `false` , the new sheet becomes active without changing the current selection.

Return

Sheet — the new active 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

`setActiveSpreadsheet(newActiveSpreadsheet)`

Sets the active spreadsheet.

 // 
  
 The 
  
 code 
  
 below 
  
 makes 
  
 the 
  
 spreadsheet 
  
 with 
  
 key 
  
 "1234567890" 
  
 the 
  
 active 
  
 spreadsheet 
 var 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 "1234567890" 
 ); 
 SpreadsheetApp 
 . 
 setActiveSpreadsheet 
 ( 
 ss 
 );

Parameters

Name	Type	Description
`newActiveSpreadsheet`	`Spreadsheet`	The Spreadsheet to be made the active spreadsheet.

`setCurrentCell(cell)`

Sets the specified cell as the current cell .

If the specified cell is present in an already selected range, then that range becomes the active range with the cell as the current cell.

If the specified cell is not present in any selected range, then the existing selection is removed and the cell becomes the current cell and the active range.

Note: The specified Range must consist of one cell, otherwise this method throws an exception.

 // 
  
 The 
  
 code 
  
 below 
  
 sets 
  
 the 
  
 cell 
  
 B5 
  
 in 
  
 the 
  
 first 
  
 sheet 
  
 as 
  
 the 
  
 current 
  
 cell 
 . 
 var 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
 . 
 getSheets 
 ()[ 
 0 
 ] 
 . 
 getRange 
 ( 
 'B5' 
 ); 
 SpreadsheetApp 
 . 
 setCurrentCell 
 ( 
 cell 
 ); 
 var 
  
 selection 
  
 = 
  
 SpreadsheetApp 
 . 
 getSelection 
 (); 
 // 
  
 Current 
  
 cell 
 : 
  
 B5 
 var 
  
 currentCell 
  
 = 
  
 selection 
 . 
 getCurrentCell 
 ();

Parameters

Name	Type	Description
`cell`	`Range`	The cell to set as the current cell.

Return

Range — the newly set current cell

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