Google Apps Script

Class Spreadsheet

This class allows users to access and modify Google Sheets files. Common operations are adding new sheets and adding collaborators.

Methods

MethodReturn typeBrief description
addEditor(emailAddress)voidAdds the given user to the list of editors for the Spreadsheet.
addEditor(user)voidAdds the given user to the list of editors for the Spreadsheet.
addEditors(emailAddresses)voidAdds the given array of users to the list of editors for the Spreadsheet.
addMenu(name, subMenus)voidCreates a new menu in the Spreadsheet UI.
addViewer(emailAddress)voidAdds the given user to the list of viewers for the Spreadsheet.
addViewer(user)voidAdds the given user to the list of viewers for the Spreadsheet.
addViewers(emailAddresses)voidAdds the given array of users to the list of viewers for the Spreadsheet.
appendRow(rowContents)SheetAppends a row to the spreadsheet.
autoResizeColumn(columnPosition)SheetSets the width of the given column to fit its contents
copy(name)SpreadsheetCopies the spreadsheet and returns the new one.
deleteActiveSheet()SheetDeletes the currently active sheet.
deleteColumn(columnPosition)SheetDeletes the column at the given column position.
deleteColumns(columnPosition, howMany)voidDeletes a number of columns starting at the given column position.
deleteRow(rowPosition)SheetDeletes the row at the given row position.
deleteRows(rowPosition, howMany)voidDeletes a number of rows starting at the given row position.
deleteSheet(sheet)voidDeletes the specified sheet.
duplicateActiveSheet()SheetDuplicates the active sheet and makes it the active sheet.
getActiveCell()RangeReturns the active cell in this sheet.
getActiveRange()RangeReturns the active range for the active sheet.
getActiveSheet()SheetGets the active sheet in a spreadsheet.
getColumnWidth(columnPosition)IntegerGets the width in pixels of the given column.
getDataRange()RangeReturns a Range corresponding to the dimensions in which data is present.
getEditors()User[]Gets the list of editors for this Spreadsheet.
getFormUrl()StringReturns the url for the form attached to the spreadsheet, null if there is no form.
getFrozenColumns()IntegerReturns the number of frozen columns.
getFrozenRows()IntegerReturns the number of frozen rows.
getId()StringGets a unique identifier for this spreadsheet.
getLastColumn()IntegerReturns the position of the last column that has content.
getLastRow()IntegerReturns the position of the last row that has content.
getName()StringGets the name of the document.
getNumSheets()IntegerReturns the number of sheets in this spreadsheet.
getOwner()UserReturns the owner of the document.
getRange(a1Notation)RangeReturns the range as specified in A1 notation or R1C1 notation.
getRangeByName(name)RangeReturns a named range, or null if no range with the given name is found.
getRowHeight(rowPosition)IntegerGets the height in pixels of the given row.
getSheetByName(name)SheetReturns a sheet with the given name.
getSheetId()IntegerReturns the ID of the sheet represented by this object.
getSheetName()StringReturns the sheet name.
getSheetProtection()PageProtectionReturns a PageProtection instance describing the permissions for the current sheet.
getSheetValues(startRow, startColumn, numRows, numColumns)Object[][]Returns the rectangular grid of values for this range starting at the given coordinates.
getSheets()Sheet[]Gets all the sheets in this spreadsheet.
getSpreadsheetLocale()StringGets the spreadsheet locale.
getSpreadsheetTimeZone()StringGets the time zone for the spreadsheet.
getUrl()StringReturns the url for the given spreadsheet.
getViewers()User[]Gets the list of viewers and commenters for this Spreadsheet.
hideColumn(column)voidHides the columns in the given range.
hideRow(row)voidHides the rows in the given range.
insertColumnAfter(afterPosition)SheetInserts a column after the given column position.
insertColumnBefore(beforePosition)SheetInserts a column before the given column position.
insertColumnsAfter(afterPosition, howMany)SheetInserts a number of columns after the given column position.
insertColumnsBefore(beforePosition, howMany)SheetInserts a number of columns before the given column position.
insertImage(url, column, row)voidInserts an image in the document at a given row and column.
insertImage(url, column, row, offsetX, offsetY)voidInserts an image in the document at a given row and column with a pixel offset.
insertRowAfter(afterPosition)SheetInserts a row after the given row position.
insertRowBefore(beforePosition)SheetInserts a row before the given row position.
insertRowsAfter(afterPosition, howMany)SheetInserts a number of rows after the given row position.
insertRowsBefore(beforePosition, howMany)SheetInserts a number of rows before the given row position.
insertSheet()SheetInserts a new sheet in the spreadsheet, with a default name.
insertSheet(sheetIndex)SheetInserts a new sheet in the spreadsheet at the given index.
insertSheet(sheetIndex, options)SheetInserts a new sheet in the spreadsheet at the given index and uses optional advanced arguments.
insertSheet(options)SheetInserts a new sheet in the spreadsheet, with a default name and uses optional advanced arguments.
insertSheet(sheetName)SheetInserts a new sheet in the spreadsheet with the given name.
insertSheet(sheetName, sheetIndex)SheetInserts a new sheet in the spreadsheet with the given name at the given index.
insertSheet(sheetName, sheetIndex, options)SheetInserts a new sheet in the spreadsheet with the given name at the given index and uses optional advanced arguments.
insertSheet(sheetName, options)SheetInserts a new sheet in the spreadsheet with the given name and uses optional advanced arguments.
moveActiveSheet(pos)voidMoves the active sheet to the given position in the list of sheets.
removeEditor(emailAddress)voidRemoves the given user from the list of editors for the Spreadsheet.
removeEditor(user)voidRemoves the given user from the list of editors for the Spreadsheet.
removeMenu(name)voidRemoves a menu that was added by addMenu(name, subMenus).
removeNamedRange(name)voidDeletes a named range with the given name.
removeViewer(emailAddress)voidRemoves the given user from the list of viewers and commenters for the Spreadsheet.
removeViewer(user)voidRemoves the given user from the list of viewers and commenters for the Spreadsheet.
rename(newName)voidRenames the document.
renameActiveSheet(newName)voidRenames the current active sheet to the given new name.
setActiveRange(range)RangeSets the active range for the active sheet.
setActiveSelection(range)RangeSets the active selection region for this sheet.
setActiveSelection(a1Notation)RangeSets the active selection, as specified in A1 notation or R1C1 notation.
setActiveSheet(sheet)SheetSets the given sheet to be the active sheet in the spreadsheet.
setColumnWidth(columnPosition, width)SheetSets the width of the given column in pixels.
setFrozenColumns(columns)voidFreezes the given number of columns.
setFrozenRows(rows)voidFreezes the given number of rows.
setNamedRange(name, range)voidNames a range.
setRowHeight(rowPosition, height)SheetSets the row height of the given row in pixels.
setSheetProtection(permissions)voidSets the permissions for the current sheet.
setSpreadsheetLocale(locale)voidSets the spreadsheet locale.
setSpreadsheetTimeZone(timezone)voidSets the time zone for the spreadsheet.
show(userInterface)voidDisplays a custom user interface component in a dialog centered in the user's browser's viewport.
sort(columnPosition)SheetSorts a sheet by column, ascending.
sort(columnPosition, ascending)SheetSorts a sheet by column.
toast(msg)voidShows a popup window in the lower right corner of the spreadsheet with the given message.
toast(msg, title)voidShows a popup window in the lower right corner of the spreadsheet with the given message and title.
toast(msg, title, timeoutSeconds)voidShows a popup window in the lower right corner of the spreadsheet with the given title and message, that stays visible for a certain length of time.
unhideColumn(column)voidUnhides the column in the given range.
unhideRow(row)voidUnhides the row in the given range.
updateMenu(name, subMenus)voidUpdates a menu that was added by addMenu(name, subMenus).

Detailed documentation

addEditor(emailAddress)

Adds the given user to the list of editors for the Spreadsheet. If the user was already on the list of viewers, this method promotes the user out of the list of viewers.

Parameters

NameTypeDescription
emailAddressStringthe email address of the user to add

addEditor(user)

Adds the given user to the list of editors for the Spreadsheet. If the user was already on the list of viewers, this method promotes the user out of the list of viewers.

Parameters

NameTypeDescription
userUsera representation of the user to add

addEditors(emailAddresses)

Adds the given array of users to the list of editors for the Spreadsheet. If any of the users were already on the list of viewers, this method promotes them out of the list of viewers.

Parameters

NameTypeDescription
emailAddressesString[]an array of email addresses of the users to add

addMenu(name, subMenus)

Creates a new menu in the Spreadsheet UI. Each menu entry runs a user-defined function. Usually, you will want to call it from the onOpen function so that the menu is automatically created when the Spreadsheet is loaded.

 
// The onOpen function is executed automatically every time a Spreadsheet is loaded
 function onOpen() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var menuEntries = [];
   // When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is
   // executed.
   menuEntries.push({name: "Menu Entry 1", functionName: "function1"});
   menuEntries.push(null); // line separator
   menuEntries.push({name: "Menu Entry 2", functionName: "function2"});

   ss.addMenu("addMenuExample", menuEntries);
 }
 

Parameters

NameTypeDescription
nameStringthe name of the menu to be created
subMenusObject[]an array of JavaScript maps with name and functionName parameters

addViewer(emailAddress)

Adds the given user to the list of viewers for the Spreadsheet. If the user was already on the list of editors, this method has no effect.

Parameters

NameTypeDescription
emailAddressStringthe email address of the user to add

addViewer(user)

Adds the given user to the list of viewers for the Spreadsheet. If the user was already on the list of editors, this method has no effect.

Parameters

NameTypeDescription
userUsera representation of the user to add

addViewers(emailAddresses)

Adds the given array of users to the list of viewers for the Spreadsheet. If any of the users were already on the list of editors, this method has no effect for them.

Parameters

NameTypeDescription
emailAddressesString[]an array of email addresses of the users to add

appendRow(rowContents)

Appends a row to the spreadsheet. This operation is atomic; it prevents issues where a user asks for the last row, and then writes to that row, and an intervening mutation occurs between getting the last row and writing to it.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Appends a new row with 3 columns to the bottom of the
 // spreadsheet containing the values in the array
 sheet.appendRow(["a man", "a plan", "panama"]);
 

Parameters

NameTypeDescription
rowContentsObject[]an array of values to insert after the last row in the sheet

Return

Sheet — the sheet, useful for method chaining


autoResizeColumn(columnPosition)

Sets the width of the given column to fit its contents

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 sheet.getRange('a1').setValue('Whenever it is a damp, drizzly November in my soul...');

 // Sets the first column to a width which fits the text
 sheet.autoResizeColumn(1);
 

Parameters

NameTypeDescription
columnPositionIntegerthe position of the given column to resize

Return

Sheet — the sheet, useful for method chaining


copy(name)

Copies the spreadsheet and returns the new one.

 
// This code makes a copy of the current spreadsheet and names it appropriately
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.copy("Copy of " + ss.getName());
 

Parameters

NameTypeDescription
nameString

Return

Spreadsheet — the spreadsheet for chaining


deleteActiveSheet()

Deletes the currently active sheet.

 
// The code below deletes the currently active sheet and stores the new active sheet in a
 // variable
 var newSheet = SpreadsheetApp.getActiveSpreadsheet().deleteActiveSheet();
 

Return

Sheet — the new active sheet


deleteColumn(columnPosition)

Deletes the column at the given column position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Columns start at "1" - this will delete the first column
 sheet.deleteColumn(1);
 

Parameters

NameTypeDescription
columnPositionIntegerthe position of the column, starting at 1 for the first column

Return

Sheet — the sheet, useful for method chaining


deleteColumns(columnPosition, howMany)

Deletes a number of columns starting at the given column position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Columns start at "1" - this will delete the first two columns
 sheet.deleteColumns(1, 2);
 

Parameters

NameTypeDescription
columnPositionIntegerthe position of the first column to delete
howManyIntegerthe number of columns to delete

deleteRow(rowPosition)

Deletes the row at the given row position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Rows start at "1" - this will delete the first row
 sheet.deleteRow(1);
 

Parameters

NameTypeDescription
rowPositionIntegerthe position of the row, starting at 1 for the first row

Return

Sheet — the sheet, useful for method chaining


deleteRows(rowPosition, howMany)

Deletes a number of rows starting at the given row position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Rows start at "1" - this will delete the first two rows
 sheet.deleteRows(1, 2);
 

Parameters

NameTypeDescription
rowPositionIntegerthe position of the first row to delete
howManyIntegerthe number of rows to delete

deleteSheet(sheet)

Deletes the specified sheet.

 
// The code below deletes the specified sheet.
 var ss = SpreadsheetApp.getActive();
 var sheet = ss.getSheetByName('My Sheet');
 ss.deleteSheet(sheet);
 

Parameters

NameTypeDescription
sheetSheetthe sheet to delete

duplicateActiveSheet()

Duplicates the active sheet and makes it the active sheet.

 
// The code below makes a duplicate of the active sheet
 SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
 

Return

Sheet — the new sheet


getActiveCell()

Returns the active cell in this sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Returns the active cell
 var cell = sheet.getActiveCell();
 

Return

Range — the current active cell


getActiveRange()

Returns the active range for the active sheet. 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. Note that when called on a SpreadsheetApp it effectively calls getActiveSpreadsheet and then getActiveSheet to act on the active Sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Returns the active cell
 var range = sheet.getActiveRange();
 

Return

Range — the active range

See also


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.

 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 

Return

Sheet — the active sheet in the spreadsheet


getColumnWidth(columnPosition)

Gets the width in pixels of the given column.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Columns start at 1
 Logger.log(sheet.getColumnWidth(1));
 

Parameters

NameTypeDescription
columnPositionIntegerthe position of the column to examine

Return

Integer — column width in pixels


getDataRange()

Returns a Range corresponding to the dimensions in which data is present. This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This represents ALL the data
 var range = sheet.getDataRange();
 var values = range.getValues();

 // This logs the spreadsheet in CSV format with a trailing comma
 for (var i = 0; i < values.length; i++) {
   var row = "";
   for (var j = 0; j < values[i].length; j++) {
     if (values[i][j]) {
       row = row + values[i][j];
     }
     row = row + ",";
   }
   Logger.log(row);
 }
 

Return

Range — a range consisting of all the data in the spreadsheet


getEditors()

Gets the list of editors for this Spreadsheet. If the user who executes the script does not have edit access to the Spreadsheet, this method throws an exception.

Return

User[] — an array of users with edit permission


getFormUrl()

Returns the url for the form attached to the spreadsheet, null if there is no form.

Return

String — the url for the spreadsheet form or null


getFrozenColumns()

Returns the number of frozen columns.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 Logger.log("Number of frozen columns: %s", sheet.getFrozenColumns());
 

Return

Integer — the number of frozen columns


getFrozenRows()

Returns the number of frozen rows.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 Logger.log("Number of frozen rows: %s", sheet.getFrozenRows());
 

Return

Integer — the number of frozen rows


getId()

Gets a unique identifier for this spreadsheet. 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 logs the ID for the active spreadsheet.
 Logger.log(SpreadsheetApp.getActiveSpreadsheet().getId());
 

Return

String — the unique id (or key) for the spreadsheet


getLastColumn()

Returns the position of the last column that has content.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This logs the value in the very last cell of this sheet
 var lastRow = sheet.getLastRow();
 var lastColumn = sheet.getLastColumn();
 var lastCell = sheet.getRange(lastRow, lastColumn);
 Logger.log(lastCell.getValue());
 

Return

Integer — the last column of the sheet that contains content


getLastRow()

Returns the position of the last row that has content.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This logs the value in the very last cell of this sheet
 var lastRow = sheet.getLastRow();
 var lastColumn = sheet.getLastColumn();
 var lastCell = sheet.getRange(lastRow, lastColumn);
 Logger.log(lastCell.getValue());
 

Return

Integer — the last row of the sheet that contains content


getName()

Gets the name of the document.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 Logger.log(ss.getName());
 

Return

String — the name of the spreadsheet


getNumSheets()

Returns the number of sheets in this spreadsheet.

 
// The code below logs the number of sheets in the active spreadsheet.
 Logger.log(SpreadsheetApp.getActiveSpreadsheet().getNumSheets());
 

Return

Integer — the number of sheets in the spreadsheet


getOwner()

Returns the owner of the document.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var owner = ss.getOwner();
 Logger.log(owner.getEmail());
 

Return

User — the owner of the document


getRange(a1Notation)

Returns the range as specified in A1 notation or R1C1 notation.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 // Returns the first cell
 var cell = sheet.getRange("A1");
 

Parameters

NameTypeDescription
a1NotationStringthe range to return, as specified in A1 notation or R1C1 notation

Return

Range — the range at the location designated


getRangeByName(name)

Returns a named range, or null if no range with the given name is found.

 
// The code below will log the number of columns for the range named "TaxRates"
 // in the active spreadsheet
 var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("TaxRates");
 if (range != null) {
   Logger.log(range.getNumColumns());
 }
 

Parameters

NameTypeDescription
nameStringthe name of the range to get

Return

Range — range of cells with the given name


getRowHeight(rowPosition)

Gets the height in pixels of the given row.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Rows start at 1
 Logger.log(sheet.getRowHeight(1));
 

Parameters

NameTypeDescription
rowPositionIntegerthe *position* of the row to examine

Return

Integer — row height in pixels


getSheetByName(name)

Returns a sheet with the given name. If multiple sheets have the same name, the leftmost one is returned. Returns null if there is no sheet with the given name.

 
// The code below will log the index of a sheet named "Expenses"
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expenses");
 if (sheet != null) {
   Logger.log(sheet.getIndex());
 }
 

Parameters

NameTypeDescription
nameStringthe name of the sheet to get

Return

Sheet — the sheet with the given name


getSheetId()

Returns the ID of the sheet represented by this object. This is an ID for the sheet that is unique to the spreadsheet. The ID is a monotonically increasing integer assigned at sheet creation time that is independent of sheet position. This is useful in conjunction with methods such as Range.copyFormatToRange(gridId, column, columnEnd, row, rowEnd) that take a gridId parameter rather than a Sheet instance.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 Logger.log(sheet.getSheetId());
 

Return

Integer — an ID for the sheet unique to the spreadsheet


getSheetName()

Returns the sheet name.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 Logger.log(sheet.getSheetName());
 

Return

String — the name of the sheet


getSheetProtection()

Returns a PageProtection instance describing the permissions for the current sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var permissions = sheet.getSheetProtection();

 permissions.setProtected(true);
 permissions.addUser("user@example.com");

 // Logs the users that have access to edit this sheet. Note that this
 // is different from access to the entire spreadsheet - getUsers() will
 // only return users if permissions.isProtected() is set to true.
 var users = permissions.getUsers();
 Logger.log(users);
 

Return

PageProtection — an object describing sheet access permissions


getSheetValues(startRow, startColumn, numRows, numColumns)

Returns the rectangular grid of values for this range starting at the given coordinates. A -1 value given as the row or column position is equivalent to getting the very last row or column that has data in the sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // The two samples below produce the same output
 var values = sheet.getSheetValues(1, 1, 3, 3);
 Logger.log(values);

 var range = sheet.getRange(1, 1, 3, 3);
 values = range.getValues();
 Logger.log(values);
 

Parameters

NameTypeDescription
startRowIntegerthe position of the starting row
startColumnIntegerthe position of the starting column
numRowsIntegerthe number of rows to return values for
numColumnsIntegerthe number of columns to return values for

Return

Object[][] — a two dimension array of values


getSheets()

Gets all the sheets in this spreadsheet.

 
// The code below will log the name of the second sheet
 var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
 if (sheets.length > 1) {
   Logger.log(sheets[1].getName());
 }
 

Return

Sheet[] — an array of all the sheets in the spreadsheet


getSpreadsheetLocale()

Gets the spreadsheet locale.

Return

String — the spreadsheet locale


getSpreadsheetTimeZone()

Gets the time zone for the spreadsheet.

Return

String — the time zone, specified in "long" format (e.g., "America/New_York", as listed by Joda.org)


getUrl()

Returns the url for the given spreadsheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 Logger.log(ss.getUrl());
 

Return

String — the url for the given spreadsheet


getViewers()

Gets the list of viewers and commenters for this Spreadsheet. If the user who executes the script does not have edit access to the Spreadsheet, this method throws an exception.

Return

User[] — an array of users with view or comment permission


hideColumn(column)

Hides the columns in the given range.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This hides the first column
 var range = sheet.getRange("A1");
 sheet.hideColumn(range);
 

Parameters

NameTypeDescription
columnRangethe column range to hide

hideRow(row)

Hides the rows in the given range.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This hides the first row
 var range = sheet.getRange("A1");
 sheet.hideRow(range);
 

Parameters

NameTypeDescription
rowRangethe row range to hide

insertColumnAfter(afterPosition)

Inserts a column after the given column position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts a column after the first column position
 sheet.insertColumnAfter(1);
 

Parameters

NameTypeDescription
afterPositionIntegerthe column after which the new column should be added

Return

Sheet — the sheet, useful for method chaining


insertColumnBefore(beforePosition)

Inserts a column before the given column position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts a column in the first column position
 sheet.insertColumnBefore(1);
 

Parameters

NameTypeDescription
beforePositionIntegerthe column before which the new column should be added

Return

Sheet — the sheet, useful for method chaining


insertColumnsAfter(afterPosition, howMany)

Inserts a number of columns after the given column position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts a column in the second column position
 sheet.insertColumnsAfter(1);
 

Parameters

NameTypeDescription
afterPositionIntegerthe column after which the new column should be added
howManyIntegerthe number of columns to insert

Return

Sheet — the sheet, useful for method chaining


insertColumnsBefore(beforePosition, howMany)

Inserts a number of columns before the given column position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts five columns before the first column
 sheet.insertColumnsBefore(1, 5);
 

Parameters

NameTypeDescription
beforePositionIntegerthe column before which the new column should be added
howManyIntegerthe number of columns to insert

Return

Sheet — the sheet, useful for method chaining


insertImage(url, column, row)

Inserts an image in the document at a given row and column.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 sheet.insertImage("https://www.google.com/images/srpr/logo3w.png", 1, 1);
 

Parameters

NameTypeDescription
urlStringthe url of the image
columnIntegerthe grid column position
rowIntegerthe grid row position

insertImage(url, column, row, offsetX, offsetY)

Inserts an image in the document at a given row and column with a pixel offset.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 sheet.insertImage("https://www.google.com/images/srpr/logo3w.png", 1, 1, 10, 10);
 

Parameters

NameTypeDescription
urlStringthe url for the image
columnIntegerthe column position
rowIntegerthe row position
offsetXIntegerhorizontal offset from cell corner in pixels
offsetYIntegervertical offset from cell corner in pixels

insertRowAfter(afterPosition)

Inserts a row after the given row position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts a row after the first row position
 sheet.insertRowAfter(1);
 

Parameters

NameTypeDescription
afterPositionIntegerthe row after which the new row should be added

Return

Sheet — the sheet, useful for method chaining


insertRowBefore(beforePosition)

Inserts a row before the given row position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts a row before the first row position
 sheet.insertRowBefore(1);
 

Parameters

NameTypeDescription
beforePositionIntegerthe row before which the new row should be added

Return

Sheet — the sheet, useful for method chaining


insertRowsAfter(afterPosition, howMany)

Inserts a number of rows after the given row position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts five rows after the first row
 sheet.insertRowsAfter(1, 5);
 

Parameters

NameTypeDescription
afterPositionIntegerthe row after which the new rows should be added
howManyIntegerthe number of rows to insert

Return

Sheet — the sheet, useful for method chaining


insertRowsBefore(beforePosition, howMany)

Inserts a number of rows before the given row position.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts five rows before the first row
 sheet.insertRowsBefore(1, 5);
 

Parameters

NameTypeDescription
beforePositionIntegerthe row before which the new rows should be added
howManyIntegerthe number of rows to insert

Return

Sheet — the sheet, useful for method chaining


insertSheet()

Inserts a new sheet in the spreadsheet, with a default name. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.insertSheet();
 

Return

Sheet — the new sheet


insertSheet(sheetIndex)

Inserts a new sheet in the spreadsheet at the given index. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.insertSheet(1);
 

Parameters

NameTypeDescription
sheetIndexIntegerthe index of the newly created sheet. To insert a sheet as the first one in the spreadsheet, set it to 0.

Return

Sheet — the new sheet


insertSheet(sheetIndex, options)

Inserts a new sheet in the spreadsheet at the given index and uses optional advanced arguments. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var templateSheet = ss.getSheetByName('Sales');
 ss.insertSheet(1, {template: templateSheet});
 

Parameters

NameTypeDescription
sheetIndexIntegerindex of the newly created sheet. To insert a sheet as the first one in the spreadsheet, set it to 0.
optionsObjectoptional JavaScript advanced arguments

Advanced parameters

NameTypeDescription
templateSheetall data from the template sheet object will be copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — the new sheet


insertSheet(options)

Inserts a new sheet in the spreadsheet, with a default name and uses optional advanced arguments. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var templateSheet = ss.getSheetByName('Sales');
 ss.insertSheet({template: templateSheet});
 

Parameters

NameTypeDescription
optionsObjectoptional JavaScript advanced arguments

Advanced parameters

NameTypeDescription
templateSheetall data from the template sheet object will be copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — the new sheet


insertSheet(sheetName)

Inserts a new sheet in the spreadsheet with the given name. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.insertSheet('My New Sheet');
 

Parameters

NameTypeDescription
sheetNameStringthe name of the new sheet

Return

Sheet — the new sheet


insertSheet(sheetName, sheetIndex)

Inserts a new sheet in the spreadsheet with the given name at the given index. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.insertSheet('My New Sheet', 1);
 

Parameters

NameTypeDescription
sheetNameStringthe name of the new sheet
sheetIndexIntegerthe index of the newly created sheet. To insert a sheet as the first one in the spreadsheet, set it to 0.

Return

Sheet — the new sheet


insertSheet(sheetName, sheetIndex, options)

Inserts a new sheet in the spreadsheet with the given name at the given index and uses optional advanced arguments. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var templateSheet = ss.getSheetByName('Sales');
 ss.insertSheet('My New Sheet', 1, {template: templateSheet});
 

Parameters

NameTypeDescription
sheetNameStringthe name of the new sheet
sheetIndexIntegerindex of th newly inserted sheet. To insert a sheet as the first one in a spreadsheet, set it to 0.
optionsObjectoptional JavaScript advanced arguments

Advanced parameters

NameTypeDescription
templateSheetall data from the template sheet object will be copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — the new sheet


insertSheet(sheetName, options)

Inserts a new sheet in the spreadsheet with the given name and uses optional advanced arguments. As a side effect, it makes it the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var templateSheet = ss.getSheetByName('Sales');
 ss.insertSheet('My New Sheet', {template: templateSheet});
 

Parameters

NameTypeDescription
sheetNameStringthe name of the new sheet
optionsObjectoptional JavaScript advanced arguments

Advanced parameters

NameTypeDescription
templateSheetall data from the template sheet object will be copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — the new sheet


moveActiveSheet(pos)

Moves the active sheet to the given position in the list of sheets. Throws an exception if the position is negative or greater than the number of sheets.

 
// This example assumes there are two sheets in the current
 // active spreadsheet: one named "first", and another named "second",
 // and that the current active sheet (first) is in position 1
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = spreadsheet.getSheetByName("first");

 // This should output 'Current index of sheet: 1'
 Logger.log("Current index of sheet: %s", sheet.getIndex());

 spreadsheet.moveActiveSheet(2);

 // This should output 'New index of sheet: 2'
 Logger.log("New index of sheet: %s", sheet.getIndex());
 

Parameters

NameTypeDescription
posIntegera position to move the active sheet to in the list of sheets

removeEditor(emailAddress)

Removes the given user from the list of editors for the Spreadsheet. This method does not block users from accessing the Spreadsheet if they belong to a class of users who have general access — for example, if the Spreadsheet is shared with the user's entire domain.

Parameters

NameTypeDescription
emailAddressStringthe email address of the user to remove

removeEditor(user)

Removes the given user from the list of editors for the Spreadsheet. This method does not block users from accessing the Spreadsheet if they belong to a class of users who have general access — for example, if the Spreadsheet is shared with the user's entire domain.

Parameters

NameTypeDescription
userUsera representation of the user to remove

removeMenu(name)

Removes a menu that was added by addMenu(name, subMenus). The name argument should have the same value as the corresponding call to addMenu(name, subMenus).

 
// The onOpen function is executed automatically every time a Spreadsheet is loaded
 function onOpen() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   ss.addMenu("badMenu", [{name: "remove bad menu", functionName: "removeBadMenu"},
                                        {name: "foo", functionName: "foo"}]);
 }
 function removeBadMenu() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   ss.removeMenu("badMenu");//name must match the name we used when added the menu
 }
 function foo(){
 //do nothing
 }
 

Parameters

NameTypeDescription
nameStringthe name of the menu to remove

removeNamedRange(name)

Deletes a named range with the given name. Throws an exception if no range with the given name is found in the spreadsheet.

// The code below will create a new named range "foo", and then remove it.
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.setNamedRange("foo", ss.getActiveRange());
 ss.removeNamedRange("foo");
 

Parameters

NameTypeDescription
nameStringthe range name

removeViewer(emailAddress)

Removes the given user from the list of viewers and commenters for the Spreadsheet. This method has no effect if the user is an editor, not a viewer or commenter. This method also does not block users from accessing the Spreadsheet if they belong to a class of users who have general access — for example, if the Spreadsheet is shared with the user's entire domain.

Parameters

NameTypeDescription
emailAddressStringthe email address of the user to remove

removeViewer(user)

Removes the given user from the list of viewers and commenters for the Spreadsheet. This method has no effect if the user is an editor, not a viewer. This method also does not block users from accessing the Spreadsheet if they belong to a class of users who have general access — for example, if the Spreadsheet is shared with the user's entire domain.

Parameters

NameTypeDescription
userUsera representation of the user to remove

rename(newName)

Renames the document.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.rename("This is the new name");
 

Parameters

NameTypeDescription
newNameStringthe new name for the document

renameActiveSheet(newName)

Renames the current active sheet to the given new name.

 
// The code below will rename the active sheet to "Hello world"
 SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet("Hello world");
 

Parameters

NameTypeDescription
newNameStringthe new name for the current active sheet

setActiveRange(range)

Sets the active range for the active sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var range = sheet.getRange("A1:D4");
 sheet.setActiveRange(range);
 

Parameters

NameTypeDescription
rangeRangethe range to set as the active range

Return

Range — the newly active range


setActiveSelection(range)

Sets the active selection region for this sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var range = sheet.getRange("A1:D4");
 sheet.setActiveSelection(range);
 

Parameters

NameTypeDescription
rangeRangethe range to set as the active selection

Return

Range — the newly active range


setActiveSelection(a1Notation)

Sets the active selection, as specified in A1 notation or R1C1 notation.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 sheet.setActiveSelection("A1:D4");
 

Parameters

NameTypeDescription
a1NotationStringthe range to set as active, as specified in A1 notation or R1C1 notation

Return

Range — the newly active range


setActiveSheet(sheet)

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

 
// The code below will make the first sheet active in the active workbook
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.setActiveSheet(ss.getSheets()[0]);
 

Parameters

NameTypeDescription
sheetSheetthe sheet to set as the active sheet

Return

Sheet — the active sheet


setColumnWidth(columnPosition, width)

Sets the width of the given column in pixels.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Sets the first column to a width of 200 pixels
 sheet.setColumnWidth(1, 200);
 

Parameters

NameTypeDescription
columnPositionIntegerthe position of the given column to set
widthIntegerthe width in pixels to set it to

Return

Sheet — the sheet, useful for method chaining


setFrozenColumns(columns)

Freezes the given number of columns. If zero, no columns are frozen.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Freezes the first column
 sheet.setFrozenColumns(1);
 

Parameters

NameTypeDescription
columnsIntegerthe number of columns to freeze

setFrozenRows(rows)

Freezes the given number of rows. If zero, no rows are frozen.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 Freezes the first row
 sheet.setFrozenRows(1);
 

Parameters

NameTypeDescription
rowsIntegerthe number of rows to freeze

setNamedRange(name, range)

Names a range.

 
// The code below will create a new named range "TaxRates" in the active spreadsheet
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.setNamedRange("TaxRates", SpreadsheetApp.getActiveRange());
 

Parameters

NameTypeDescription
nameStringthe name to give the range
rangeRangethe range specification

setRowHeight(rowPosition, height)

Sets the row height of the given row in pixels.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Sets the first row to a height of 200 pixels
 sheet.setRowHeight(1, 200);
 

Parameters

NameTypeDescription
rowPositionIntegerthe row position to change
heightIntegerheight in pixels to set it to

Return

Sheet — the sheet, useful for method chaining


setSheetProtection(permissions)

Sets the permissions for the current sheet.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var permissions = sheet.getSheetProtection();

 // This copies the permissions on the first sheet to the second sheet
 var sheetToClonePermissionsTo = ss.getSheets()[1];
 sheetToClonePermissionsTo.setSheetProtection(permissions);
 

Parameters

NameTypeDescription
permissionsPageProtectionthe access permissions object to set on this sheet

setSpreadsheetLocale(locale)

Sets the spreadsheet locale.

Parameters

NameTypeDescription
localeStringthe locale code to use (e.g., 'en', 'fr', 'en_US')

setSpreadsheetTimeZone(timezone)

Sets the time zone for the spreadsheet.

Parameters

NameTypeDescription
timezoneStringthe time zone, specified in "long" format (e.g., "America/New_York", as listed by Joda.org)

show(userInterface)

Displays a custom user interface component in a dialog centered in the user's browser's viewport. The server-side script's execution will not be suspended. To communicate with the server side, the user interface component must make asynchronous callbacks to the server-side script.

If the server-side script previously displayed a dialog that has not yet been dismissed, then the existing dialog will be replaced with the newly requested dialog's user interface. The following code snippet displays a simple HtmlService application in a dialog with the specified title, height, and width:

 
var htmlApp = HtmlService
     .createHtmlOutput('<p>A change of speed, a change of style...</p>')
     .setTitle('My HtmlService Application')
     .setWidth(250)
     .setHeight(300);

 SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);

 // The script resumes execution immediately after showing the dialog.
 
Likewise, the following code snippet displays a simple UiApp application in a dialog with the specified title, height, and width:
 
var uiApp = UiApp.createApplication()
     .setTitle('My UiApp Application')
     .setWidth(250)
     .setHeight(300);
 uiApp.add(uiApp.createLabel('The photograph on the dashboard taken years ago...'));

 SpreadsheetApp.getActiveSpreadsheet().show(uiApp);

 // The script resumes execution immediately after showing the dialog.
 

Parameters

NameTypeDescription
userInterfaceObjecteither an HtmlOutput Web application or a UiApp GUI application.

sort(columnPosition)

Sorts a sheet by column, ascending.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Sorts the sheet by the first column, ascending
 sheet.sort(1);
 

Parameters

NameTypeDescription
columnPositionIntegerthe column to sort by

Return

Sheet — the sheet, useful for method chaining


sort(columnPosition, ascending)

Sorts a sheet by column. Takes a parameter to specify ascending or descending.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // Sorts the sheet by the first column, descending
 sheet.sort(1, false);
 

Parameters

NameTypeDescription
columnPositionIntegerthe column to sort by
ascendingBooleantrue for ascending, false for descending

Return

Sheet — the sheet, useful for method chaining


toast(msg)

Shows a popup window in the lower right corner of the spreadsheet with the given message.

 
// Show a popup with the message "Task started".
 SpreadsheetApp.getActiveSpreadsheet().toast('Task started');
 

Parameters

NameTypeDescription
msgStringthe message to be shown in the toast

toast(msg, title)

Shows a popup window in the lower right corner of the spreadsheet with the given message and title.

 
// Show a popup with the title "Status" and the message "Task started".
 SpreadsheetApp.getActiveSpreadsheet().toast('Task started', 'Status');
 

Parameters

NameTypeDescription
msgStringthe message to be shown in the toast
titleStringthe title of the toast, which is optional

toast(msg, title, timeoutSeconds)

Shows a popup window in the lower right corner of the spreadsheet with the given title and message, that stays visible for a certain length of time.

 
// Show a 3-second popup with the title "Status" and the message "Task started".
 SpreadsheetApp.getActiveSpreadsheet().toast('Task started', 'Status', 3);
 

Parameters

NameTypeDescription
msgStringmessage to be shown in the toast
titleStringthe title of the toast, which is optional
timeoutSecondsNumberthe timeout in seconds; if null, the toast defaults to 5 seconds; if negative, the toast remains until dismissed

unhideColumn(column)

Unhides the column in the given range.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This unhides the first column if it was previously hidden
 var range = sheet.getRange("A1");
 sheet.unhideColumn(range);
 

Parameters

NameTypeDescription
columnRangethe range to unhide, if hidden

unhideRow(row)

Unhides the row in the given range.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This unhides the first row if it was previously hidden
 var range = sheet.getRange("A1");
 sheet.unhideRow(range);
 

Parameters

NameTypeDescription
rowRangethe range to unhide, if hidden

updateMenu(name, subMenus)

Updates a menu that was added by addMenu(name, subMenus). Works exactly like addMenu(name, subMenus).

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var menuEntries = [];
 menuEntries.push({name: "Lone Menu Entry", functionName: "function1"});
 ss.updateMenu("addMenuExample", menuEntries);
 

Parameters

NameTypeDescription
nameStringthe name of the menu to update
subMenusObject[]an array of JavaScript maps with name and functionName parameters

Deprecated methods



Authentification requise

Vous devez être connecté à Google+ pour effectuer cette opération.

Connexion en cours…

Le site Google pour les développeurs a besoin de votre autorisation pour effectuer cette opération.