Google Apps Script

Class SpreadsheetApp

This class allows users to open Google Sheets files and to create new ones. This class is the parent class for the Spreadsheet service.

Properties

PropertyTypeDescription
DataValidationCriteriaDataValidationCriteriaAn enumeration representing the data-validation criteria that can be set on a range.

Methods

MethodReturn typeBrief description
create(name)SpreadsheetCreates a new spreadsheet with the given name.
create(name, rows, columns)SpreadsheetCreates a new spreadsheet with the given name and the specified number of rows and columns.
flush()voidApplies all pending Spreadsheet changes.
getActive()SpreadsheetReturns the currently active spreadsheet, or null if there is none.
getActiveRange()RangeReturns the range of cells that is currently considered active.
getActiveSheet()SheetGets the active sheet in a spreadsheet.
getActiveSpreadsheet()SpreadsheetReturns the currently active spreadsheet, or null if there is none.
getUi()UiReturns an instance of the spreadsheet's user-interface environment that allows the script to add features like menus, dialogs, and sidebars.
newDataValidation()DataValidationBuilderCreates a builder for a data-validation rule.
open(file)SpreadsheetOpens the spreadsheet that corresponds to the given File object.
openById(id)SpreadsheetOpens the spreadsheet with the given ID.
openByUrl(url)SpreadsheetOpens the spreadsheet with the given url.
setActiveRange(range)RangeSets the active range for the application.
setActiveSheet(sheet)SheetSets the active sheet in a spreadsheet.
setActiveSpreadsheet(newActiveSpreadsheet)voidSets the active spreadsheet.

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

NameTypeDescription
nameStringthe name for the spreadsheet

Return

Spreadsheet — a new spreadsheet


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

NameTypeDescription
nameStringthe name for the spreadsheet
rowsIntegerthe number of rows for the spreadsheet
columnsIntegerthe number of columns for the spreadsheet

Return

Spreadsheet — a new spreadsheet


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


getActiveRange()

Returns the range of cells that is currently considered active. 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 will log the background color for the active range
 var color = SpreadsheetApp.getActiveRange().getBackgroundColor();
 Logger.log(color);
 

Return

Range — the active Range object


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 will log the name of the active sheet.
 Logger.log(SpreadsheetApp.getActiveSheet().getName());
 

Return

Sheet — the active Sheet object


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


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.

This method only works in the new version of Google Sheets. The older version of Sheets continues to use an alternate syntax shown in the guides above.

 // 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


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


open(file)

Opens the spreadsheet that corresponds to the given File object.

 
// This code gets 10 files of type spreadsheet from Google Drive and then
 // opens the spreadsheets by the File and logs the spreadsheet names
 var files = DocsList.getFilesByType('spreadsheet', 0, 10);
 for (var i = 0; i < files.length; i++) {
   var spreadsheet = SpreadsheetApp.open(files[i]);
   Logger.log(spreadsheet.getName());
 }
 

Parameters

NameTypeDescription
fileFilethe File to open

Return

Spreadsheet — the spreadsheet


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

NameTypeDescription
idStringthe unique identifier for the spreadsheet

Return

Spreadsheet — the Spreadsheet object with the given id


openByUrl(url)

Opens the spreadsheet with the given url.

 
// 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.openByUrl(
     'https://docs.google.com/spreadsheets/d/abc1234567/edit');
 Logger.log(ss.getName());
 

Parameters

NameTypeDescription
urlStringthe url for the spreadsheet

Return

Spreadsheet — the Spreadsheet object with the given id


setActiveRange(range)

Sets the active range for the application. The spreadsheet UI will display the sheet that contains the chosen range and will select the cells defined in the chosen range.

 
// The code below will set range C1:D4 in the first sheet as the active range
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var range = ss.getSheets()[0].getRange("C1:D4");
 SpreadsheetApp.setActiveRange(range);
 

Parameters

NameTypeDescription
rangeRangethe Range to be made the active Range

Return

Range — the new active Range


setActiveSheet(sheet)

Sets the active sheet in a spreadsheet. The spreadsheet UI will display the chosen sheet unless the sheet belongs to a different spreadsheet.

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

Parameters

NameTypeDescription
sheetSheetthe new active Sheet

Return

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


setActiveSpreadsheet(newActiveSpreadsheet)

Sets the active spreadsheet.

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

Parameters

NameTypeDescription
newActiveSpreadsheetSpreadsheetthe Spreadsheet to be made the active spreadsheet

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.