Class Spreadsheet

Spreadsheet

Access and modify Google Sheets files. Common operations are adding new sheets and adding collaborators.

Methods

MethodReturn typeBrief description
addDeveloperMetadata(key)SpreadsheetAdds developer metadata with the specified key to the top-level spreadsheet.
addDeveloperMetadata(key, visibility)SpreadsheetAdds developer metadata with the specified key and visibility to the spreadsheet.
addDeveloperMetadata(key, value)SpreadsheetAdds developer metadata with the specified key and value to the spreadsheet.
addDeveloperMetadata(key, value, visibility)SpreadsheetAdds developer metadata with the specified key, value, and visibility to the spreadsheet.
addEditor(emailAddress)SpreadsheetAdds the given user to the list of editors for the Spreadsheet.
addEditor(user)SpreadsheetAdds the given user to the list of editors for the Spreadsheet.
addEditors(emailAddresses)SpreadsheetAdds 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)SpreadsheetAdds the given user to the list of viewers for the Spreadsheet.
addViewer(user)SpreadsheetAdds the given user to the list of viewers for the Spreadsheet.
addViewers(emailAddresses)SpreadsheetAdds the given array of users to the list of viewers for the Spreadsheet.
appendRow(rowContents)SheetAppends a row to the bottom of the current data region in the sheet.
autoResizeColumn(columnPosition)SheetSets the width of the given column to fit its contents.
copy(name)SpreadsheetCopies the spreadsheet and returns the new one.
createDeveloperMetadataFinder()DeveloperMetadataFinderReturns a DeveloperMetadataFinder for finding developer metadata within the scope of this spreadsheet.
createTextFinder(findText)TextFinderCreates a text finder for the spreadsheet, which can be used to find and replace text within the spreadsheet.
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 selected range in the active sheet, or null if there is no active range.
getActiveRangeList()RangeListReturns the list of active ranges in the active sheet or null if there are no active ranges.
getActiveSheet()SheetGets the active sheet in a spreadsheet.
getAs(contentType)BlobReturn the data inside this object as a blob converted to the specified content type.
getBandings()Banding[]Returns all the bandings in this spreadsheet.
getBlob()BlobReturn the data inside this object as a blob.
getColumnWidth(columnPosition)IntegerGets the width in pixels of the given column.
getCurrentCell()RangeReturns the current cell in the active sheet or null if there is no current cell.
getDataRange()RangeReturns a Range corresponding to the dimensions in which data is present.
getDataSourceFormulas()DataSourceFormula[]Gets all the data source formulas.
getDataSourcePivotTables()DataSourcePivotTable[]Gets all the data source pivot tables.
getDataSourceRefreshSchedules()DataSourceRefreshSchedule[]Gets the refresh schedules of this spreadsheet.
getDataSourceSheets()DataSourceSheet[]Returns all the data source sheets in the spreadsheet.
getDataSourceTables()DataSourceTable[]Gets all the data source tables.
getDataSources()DataSource[]Returns all the data sources in the spreadsheet.
getDeveloperMetadata()DeveloperMetadata[]Gets the developer metadata associated with the top-level spreadsheet.
getEditors()User[]Gets the list of editors for this Spreadsheet.
getFormUrl()StringReturns the URL for the form that sends its responses to this spreadsheet, or null if this spreadsheet has no associated form.
getFrozenColumns()IntegerReturns the number of frozen columns.
getFrozenRows()IntegerReturns the number of frozen rows.
getId()StringGets a unique identifier for this spreadsheet.
getImages()OverGridImage[]Returns all over-the-grid images on the sheet.
getIterativeCalculationConvergenceThreshold()NumberReturns the threshold value used during iterative calculation.
getLastColumn()IntegerReturns the position of the last column that has content.
getLastRow()IntegerReturns the position of the last row that has content.
getMaxIterativeCalculationCycles()IntegerReturns the maximum number of iterations to use during iterative calculation.
getName()StringGets the name of the document.
getNamedRanges()NamedRange[]Gets all the named ranges in this spreadsheet.
getNumSheets()IntegerReturns the number of sheets in this spreadsheet.
getOwner()UserReturns the owner of the document, or null for a document in a shared drive.
getPredefinedSpreadsheetThemes()SpreadsheetTheme[]Returns the list of predefined themes.
getProtections(type)Protection[]Gets an array of objects representing all protected ranges or sheets in the spreadsheet.
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.
getRangeList(a1Notations)RangeListReturns the RangeList collection representing the ranges in the same sheet specified by a non-empty list of A1 notations or R1C1 notations.
getRecalculationInterval()RecalculationIntervalReturns the calculation interval for this spreadsheet.
getRowHeight(rowPosition)IntegerGets the height in pixels of the given row.
getSelection()SelectionReturns the current Selection in the spreadsheet.
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.
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.
getSpreadsheetTheme()SpreadsheetThemeReturns the current theme of the spreadsheet, or null if no theme is applied.
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 column or 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 given number of columns after the given column position.
insertColumnsBefore(beforePosition, howMany)SheetInserts a number of columns before the given column position.
insertDataSourceSheet(spec)DataSourceSheetInserts a new DataSourceSheet in the spreadsheet and starts data execution.
insertImage(blobSource, column, row)OverGridImageInserts a Spreadsheet as an image in the document at a given row and column.
insertImage(blobSource, column, row, offsetX, offsetY)OverGridImageInserts a Spreadsheet as an image in the document at a given row and column, with a pixel offset.
insertImage(url, column, row)OverGridImageInserts an image in the document at a given row and column.
insertImage(url, column, row, offsetX, offsetY)OverGridImageInserts 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 into the spreadsheet, using a default sheet name.
insertSheet(sheetIndex)SheetInserts a new sheet into the spreadsheet at the given index.
insertSheet(sheetIndex, options)SheetInserts a new sheet into the spreadsheet at the given index and uses optional advanced arguments.
insertSheet(options)SheetInserts a new sheet into the spreadsheet, using a default sheet name and optional advanced arguments.
insertSheet(sheetName)SheetInserts a new sheet into the spreadsheet with the given name.
insertSheet(sheetName, sheetIndex)SheetInserts a new sheet into the spreadsheet with the given name at the given index.
insertSheet(sheetName, sheetIndex, options)SheetInserts a new sheet into the spreadsheet with the given name at the given index and uses optional advanced arguments.
insertSheet(sheetName, options)SheetInserts a new sheet into the spreadsheet with the given name and uses optional advanced arguments.
insertSheetWithDataSourceTable(spec)SheetInserts a new sheet in the spreadsheet, creates a DataSourceTable spanning the entire sheet with the given data source specification, and starts data execution.
isColumnHiddenByUser(columnPosition)BooleanReturns whether the given column is hidden by the user.
isIterativeCalculationEnabled()BooleanReturns whether iterative calculation is activated in this spreadsheet.
isRowHiddenByFilter(rowPosition)BooleanReturns whether the given row is hidden by a filter (not a filter view).
isRowHiddenByUser(rowPosition)BooleanReturns whether the given row is hidden by the user.
moveActiveSheet(pos)voidMoves the active sheet to the given position in the list of sheets.
moveChartToObjectSheet(chart)SheetCreates a new SheetType.OBJECT sheet and moves the provided chart to it.
refreshAllDataSources()voidRefreshes all supported data sources and their linked data source objects, skipping invalid data source objects.
removeEditor(emailAddress)SpreadsheetRemoves the given user from the list of editors for the Spreadsheet.
removeEditor(user)SpreadsheetRemoves 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)SpreadsheetRemoves the given user from the list of viewers and commenters for the Spreadsheet.
removeViewer(user)SpreadsheetRemoves 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.
resetSpreadsheetTheme()SpreadsheetThemeRemoves the applied theme and sets the default theme on the spreadsheet.
setActiveRange(range)RangeSets the specified range as the active range in the active sheet, with the top left cell in the range as the current cell.
setActiveRangeList(rangeList)RangeListSets the specified list of ranges as the active ranges in 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.
setActiveSheet(sheet, restoreSelection)SheetSets the given sheet to be the active sheet in the spreadsheet, with an option to restore the most recent selection within that sheet.
setColumnWidth(columnPosition, width)SheetSets the width of the given column in pixels.
setCurrentCell(cell)RangeSets the specified cell as the current cell.
setFrozenColumns(columns)voidFreezes the given number of columns.
setFrozenRows(rows)voidFreezes the given number of rows.
setIterativeCalculationConvergenceThreshold(minThreshold)SpreadsheetSets the minimum threshold value for iterative calculation.
setIterativeCalculationEnabled(isEnabled)SpreadsheetSets whether iterative calculation is activated in this spreadsheet.
setMaxIterativeCalculationCycles(maxIterations)SpreadsheetSets the maximum number of calculation iterations that should be performed during iterative calculation.
setNamedRange(name, range)voidNames a range.
setRecalculationInterval(recalculationInterval)SpreadsheetSets how often this spreadsheet should recalculate.
setRowHeight(rowPosition, height)SheetSets the row height of the given row in pixels.
setSpreadsheetLocale(locale)voidSets the spreadsheet locale.
setSpreadsheetTheme(theme)SpreadsheetThemeSets a theme on the spreadsheet.
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).
waitForAllDataExecutionsCompletion(timeoutInSeconds)voidWaits until all the current executions in the spreadsheet complete, timing out after the provided number of seconds.

Detailed documentation

addDeveloperMetadata(key)

Adds developer metadata with the specified key to the top-level spreadsheet.

// 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');

// Adds the key 'NAME' in the developer metadata for the spreadsheet.
ss.addDeveloperMetadata('NAME')

// Gets the first developer metadata object and logs its key.
const developerMetaData = ss.getDeveloperMetadata()[0]
console.log(developerMetaData.getKey())

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.

Return

Spreadsheet — This spreadsheet, for chaining.

Authorization

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

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

addDeveloperMetadata(key, visibility)

Adds developer metadata with the specified key and visibility to the spreadsheet.

// 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');

// Adds the key 'NAME' in the developer metadata for the spreadsheet and sets the visibility
// to the developer project that created the metadata.
ss.addDeveloperMetadata('NAME', SpreadsheetApp.DeveloperMetadataVisibility.PROJECT);

// Gets the first developer metadata object and logs its key and visibility setting.
const developerMetaData = ss.getDeveloperMetadata()[0];
console.log(developerMetaData.getKey());
console.log(`Key: ${developerMetaData.getKey()},
.             Visibility: ${developerMetaData.getVisibility()}`);

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.
visibilityDeveloperMetadataVisibilityThe visibility of the new developer metadata.

Return

Spreadsheet — This spreadsheet, for chaining.

Authorization

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

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

addDeveloperMetadata(key, value)

Adds developer metadata with the specified key and value to the spreadsheet.

// 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');

// Adds the key 'NAME' and sets the value to 'GOOGLE' in the developer metadata for the
// spreadsheet.
ss.addDeveloperMetadata('NAME', 'GOOGLE');

// Gets the first developer metadata object and logs its key and value.
const developerMetaData = ss.getDeveloperMetadata()[0];
console.log(developerMetaData.getKey());
console.log(`Key: ${developerMetaData.getKey()}, Value: ${developerMetaData.getValue()}`);

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.
valueStringThe value for the new developer metadata.

Return

Spreadsheet — This spreadsheet, for chaining.

Authorization

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

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

addDeveloperMetadata(key, value, visibility)

Adds developer metadata with the specified key, value, and visibility to the spreadsheet.

// 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');

// Adds the key 'NAME', sets the value to 'GOOGLE', and sets the visibility
// to any developer project with document access.
ss.addDeveloperMetadata('NAME', 'GOOGLE', SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT);

// Gets the first developer metadata object and logs its key, value, and visibility setting.
const developerMetaData = ss.getDeveloperMetadata()[0];
console.log(`Key: ${developerMetaData.getKey()},
             Value: ${developerMetaData.getValue()},
             Visibility: ${developerMetaData.getVisibility()}`);

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.
valueStringThe value for the new developer metadata.
visibilityDeveloperMetadataVisibilityThe visibility of the new developer metadata.

Return

Spreadsheet — This spreadsheet, for chaining.

Authorization

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

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

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.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

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.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

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.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

addMenu(name, subMenus)

Creates a new menu in the Spreadsheet UI.

Each menu entry runs a user-defined function. Usually, you 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. You can use functions from included libraries, such as Library.libFunction1.

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

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.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

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.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

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.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

appendRow(rowContents)

Appends a row to the bottom of the current data region in the sheet. If a cell's content begins with =, it's interpreted as a formula.

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

// Appends a new row with 3 columns to the bottom of the current
// data region in the sheet 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.

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

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

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

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
nameStringThe name of the copy.

Return

Spreadsheet — This spreadsheet, for chaining.

Authorization

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

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

createDeveloperMetadataFinder()

Returns a DeveloperMetadataFinder for finding developer metadata within the scope of this spreadsheet. By default this considers all metadata associated with the spreadsheet, sheets, rows, and columns.

// 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');

// Adds developer metadata to the spreadsheet.
ss.addDeveloperMetadata('NAME', 'CHARLIE');
ss.addDeveloperMetadata('COMPANY', 'EXAMPLE ORGANIZATION');
ss.addDeveloperMetadata('TECHNOLOGY', 'JAVASCRIPT');

// Creates a developer metadata finder.
const developerMetadataFinder = ss.createDeveloperMetadataFinder();

// Finds the developer metadata objects with 'COMPANY' as the key.
const googleMetadataFromSpreadsheet = developerMetadataFinder.withKey('COMPANY').find();

// Gets the first result of developer metadata that has the key 'COMPANY' and logs its value.
console.log(googleMetadataFromSpreadsheet[0].getValue());

Return

DeveloperMetadataFinder — A developer metadata finder to search for metadata in the scope of this spreadsheet.


createTextFinder(findText)

Creates a text finder for the spreadsheet, which can be used to find and replace text within the spreadsheet. The search starts from the first sheet of the spreadsheet.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Creates  a text finder.
var textFinder = spreadsheet.createTextFinder('dog');

// Returns the first occurrence of 'dog' in the spreadsheet.
var firstOccurrence = textFinder.findNext();

// Replaces the last found occurrence of 'dog' with 'cat' and returns the number
// of occurrences replaced.
var numOccurrencesReplaced = findOccurrence.replaceWith('cat');

Parameters

NameTypeDescription
findTextStringThe text to search for.

Return

TextFinder — The TextFinder for the spreadsheet.


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.

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

deleteColumn(columnPosition)

Deletes the column at the given column position.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// Columns start at "1" - this deletes 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

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

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 deletes the first two columns
sheet.deleteColumns(1, 2);

Parameters

NameTypeDescription
columnPositionIntegerThe position of the first column to delete.
howManyIntegerThe number of columns to delete.

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

deleteRow(rowPosition)

Deletes the row at the given row position.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// Rows start at "1" - this deletes 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

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

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 deletes the first two rows
sheet.deleteRows(1, 2);

Parameters

NameTypeDescription
rowPositionIntegerThe position of the first row to delete.
howManyIntegerThe number of rows to delete.

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

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.

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

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.

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

getActiveCell()

Returns the active cell in this sheet.

Note: It's preferable to use getCurrentCell(), which returns the current highlighted cell.

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

Return

Range — the current active 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

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.

The term "active range" refers to the range that a user has selected in the active sheet, but in a custom function it refers to the cell being actively recalculated.

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

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

See also


getActiveRangeList()

Returns the list of active ranges in the active sheet or null if there are no active ranges.

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

var sheet = SpreadsheetApp.getActiveSheet();
// Returns the list of active ranges.
var activeRangeList = sheet.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

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.


getAs(contentType)

Return the data inside this object as a blob converted to the specified content type. This method adds the appropriate extension to the filename—for example, "myfile.pdf". However, it assumes that the part of the filename that follows the last period (if any) is an existing extension that should be replaced. Consequently, "ShoppingList.12.25.2014" becomes "ShoppingList.12.25.pdf".

To view the daily quotas for conversions, see Quotas for Google Services. Newly created Google Workspace domains might be temporarily subject to stricter quotas.

Parameters

NameTypeDescription
contentTypeStringThe MIME type to convert to. For most blobs, 'application/pdf' is the only valid option. For images in BMP, GIF, JPEG, or PNG format, any of 'image/bmp', 'image/gif', 'image/jpeg', or 'image/png' are also valid. For a Google Docs document, 'text/markdown' is also valid.

Return

Blob — The data as a blob.


getBandings()

Returns all the bandings in this spreadsheet.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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 an array of the bandings in the spreadsheet.
const bandings = ss.getBandings();

// Logs the range of the first banding in the spreadsheet to the console.
console.log(bandings[0].getRange().getA1Notation());

Return

Banding[] — The bandings in this spreadsheet.

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

getBlob()

Return the data inside this object as a blob.

Return

Blob — The data as a blob.


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

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 cell in the active sheet or null if there is no current cell. The current cell is the cell that has focus in the Google Sheets UI, and is highlighted by a dark border. There is never more than one current cell. When a user selects one or more cell ranges, one of the cells in the selection is the current cell.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Returns the current highlighted cell in the one of the active ranges.
var currentCell = sheet.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

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 (Sheet.getLastColumn(), Sheet.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

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

getDataSourceFormulas()

Gets all the data source formulas.

// Opens the spreadsheet by its ID. If you created your script from within a Google Sheets
// file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
const ss = SpreadsheetApp.openById('abc123456');

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets an array of the data source formulas on Sheet1.
// To get an array of data source formulas for the entire spreadsheet,
// replace 'sheet' with 'ss'.
const dataSourceFormulas = sheet.getDataSourceFormulas();

// Logs the first data source formula in the array.
console.log(dataSourceFormulas[0].getFormula());

Return

DataSourceFormula[] — A list of data source formulas.

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

getDataSourcePivotTables()

Gets all the data source pivot tables.

// 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 Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets an array of the data source pivot tables on Sheet1.
// To get an array of data source pivot tables for the entire
// spreadsheet, replace 'sheet' with 'ss'.
const dataSourcePivotTables = sheet.getDataSourcePivotTables();

// Logs the last time that the first pivot table in the array was refreshed.
console.log(dataSourcePivotTables[0].getStatus().getLastRefreshedTime());

Return

DataSourcePivotTable[] — A list of data source pivot tables.

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

getDataSourceRefreshSchedules()

Gets the refresh schedules of this spreadsheet.

// 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');

// Activates BigQuery operations for the connected spreadsheet.
SpreadsheetApp.enableBigQueryExecution();

// Gets the frequency type of the first referesh schedule in the array.
const frequencyType = ss.getDataSourceRefreshSchedules()[0]
                        .getFrequency()
                        .getFrequencyType()
                        .toString();

// Logs the frequency type to the console.
console.log(frequencyType);

Return

DataSourceRefreshSchedule[] — The refresh schedules of this spreadsheet.

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

getDataSourceSheets()

Returns all the data source sheets in the spreadsheet.

// Turns data execution on for BigQuery data sources.
SpreadsheetApp.enableBigQueryExecution();

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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 the first data source sheet in the spreadsheet.
const dataSource = ss.getDataSourceSheets()[0];

// Gets the name of the data source sheet.
console.log(dataSource.asSheet().getName());

Return

DataSourceSheet[] — An array of all the data source sheets.

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

getDataSourceTables()

Gets all the data source tables.

// 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 Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets an array of data source tables on Sheet1.
// To get an array of data source tables for the entire spreadsheet,
// replace 'sheet' with 'ss'.
const dataSourceTables = sheet.getDataSourceTables();

// Logs the last completed data execution time on the first data source table.
console.log(dataSourceTables[0].getStatus().getLastExecutionTime());

Return

DataSourceTable[] — A list of data source tables.

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

getDataSources()

Returns all the data sources in the spreadsheet.

// Turns data execution on for BigQuery data sources.
SpreadsheetApp.enableBigQueryExecution();

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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 the data sources on the spreadsheet.
const dataSources = ss.getDataSources();

// Logs the name of the first column on the first data source.
console.log(dataSources[0].getColumns()[0].getName());

Return

DataSource[] — An array of all the data sources.

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

getDeveloperMetadata()

Gets the developer metadata associated with the top-level spreadsheet.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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');

// Adds 'Google' as a key to the spreadsheet metadata.
ss.addDeveloperMetadata('Google');

// Gets the spreadsheet's metadata.
const ssMetadata = ss.getDeveloperMetadata();

// Gets the first set of the spreadsheet's metadata and logs the key to the console.
console.log(ssMetadata[0].getKey());

Return

DeveloperMetadata[] — The developer metadata associated with this 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

getEditors()

Gets the list of editors for this Spreadsheet.

Return

User[] — An array of users with edit permission.

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

getFormUrl()

Returns the URL for the form that sends its responses to this spreadsheet, or null if this spreadsheet has no associated form. If multiple forms send responses to this spreadsheet, the form URL returned is indeterminate. As an alternative, per sheet form URL associations can be retrieved through the Sheet.getFormUrl() method.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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 the form URL from the spreadsheet.
const formUrl = ss.getFormUrl();

// Logs the form URL to the console.
console.log(formUrl);

Return

String — The URL for the form that places its responses in this spreadsheet, or null if this spreadsheet doesn't have an associated form.

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

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

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

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

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

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.


getImages()

Returns all over-the-grid images on the sheet.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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 the over-the-grid images from Sheet1.
// To get the over-the-grid images from the entire spreadsheet, use ss.getImages() instead.
const images = sheet.getImages();

// For each image, logs the anchor cell in A1 notation.
for (const image of images) {
 console.log(image.getAnchorCell().getA1Notation());
}

Return

OverGridImage[] — An array of over-the-grid images.

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

getIterativeCalculationConvergenceThreshold()

Returns the threshold value used during iterative calculation. When the results of successive calculation differ by less than this value, the iterative calculation stops.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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');

// Sets the iterative calculation convergence threshold for the spreadsheet.
ss.setIterativeCalculationConvergenceThreshold(2);

// Logs the threshold to the console.
console.log(ss.getIterativeCalculationConvergenceThreshold());

Return

Number — The convergence threshold.

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

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

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

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

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

getMaxIterativeCalculationCycles()

Returns the maximum number of iterations to use during iterative calculation.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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');

// Sets the max iterative calculation cycles for the spreadsheet.
ss.setMaxIterativeCalculationCycles(10);

// Logs the max iterative calculation cycles to the console.
console.log(ss.getMaxIterativeCalculationCycles());

Return

Integer — The maximum number of calculation iterations.

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

getName()

Gets the name of the document.

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

Return

String — The name of the spreadsheet.

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

getNamedRanges()

Gets all the named ranges in this spreadsheet.

// The code below logs the name of the first named range.
var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
for (var i = 0; i < namedRanges.length; i++) {
  Logger.log(namedRanges[i].getName());
}

Return

NamedRange[] — An array of all the named ranges in the spreadsheet.

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

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, or null for a document in a shared drive.

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

Return

User — The owner of the document, or null if the document is in a shared drive.

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

getPredefinedSpreadsheetThemes()

Returns the list of predefined themes.

// The code below returns the list of predefined themes.
var predefinedThemesList = SpreadsheetApp.getActiveSpreadsheet()
                               .getPredefinedSpreadsheetThemes();

Return

SpreadsheetTheme[] — List of predefined themes.

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

getProtections(type)

Gets an array of objects representing all protected ranges or sheets in the spreadsheet.

// Remove all range protections in the spreadsheet that the user has permission to edit.
var ss = SpreadsheetApp.getActive();
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
  var protection = protections[i];
  if (protection.canEdit()) {
    protection.remove();
  }
}
// Remove all sheet protections in the spreadsheet that the user has permission to edit.
var ss = SpreadsheetApp.getActive();
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
for (var i = 0; i < protections.length; i++) {
  var protection = protections[i];
  if (protection.canEdit()) {
    protection.remove();
  }
}

Parameters

NameTypeDescription
typeProtectionTypeThe type of protected area, either SpreadsheetApp.ProtectionType.RANGE or SpreadsheetApp.ProtectionType.SHEET.

Return

Protection[] — An array of objects representing all protected ranges or sheets in the spreadsheet.

Authorization

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

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

getRange(a1Notation)

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

// Get a range A1:D4 on sheet titled "Invoices"
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRange("Invoices!A1:D4");

// Get cell A1 on the first sheet
var sheet = ss.getSheets()[0];
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

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

getRangeByName(name)

Returns a named range, or null if no range with the given name is found. If multiple sheets of the spreadsheet use the same range name, specify the sheet name without additional quotation marks — for example, getRangeByName('TaxRates') or getRangeByName('Sheet Name!TaxRates'), but not getRangeByName('"Sheet Name"!TaxRates').

// 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 — The range of cells with the given name.


getRangeList(a1Notations)

Returns the RangeList collection representing the ranges in the same sheet specified by a non-empty list of A1 notations or R1C1 notations.

// Get a list of ranges A1:D4, F1:H4.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList  = sheet.getRangeList(['A1:D4', 'F1:H4']);

Parameters

NameTypeDescription
a1NotationsString[]The list of ranges to return, as specified in A1 notation or R1C1 notation.

Return

RangeList — the range list at the location designated

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

getRecalculationInterval()

Returns the calculation interval for this spreadsheet.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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');

// Logs the calculation interval for the spreadsheet to the console.
console.log(ss.getRecalculationInterval().toString());

Return

RecalculationInterval — The calculation interval for this spreadsheet.

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

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

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.getActiveSpreadsheet().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

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

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

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

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

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-dimensional array of values

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

getSheets()

Gets all the sheets in this spreadsheet.

// The code below logs 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.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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 the spreadsheet locale.
const ssLocale = ss.getSpreadsheetLocale();

// Logs the locale to the console.
console.log(ssLocale);

Return

String — The spreadsheet locale.

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

getSpreadsheetTheme()

Returns the current theme of the spreadsheet, or null if no theme is applied.

// The code below returns the current theme of the spreadsheet.
var currentTheme = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTheme();

Return

SpreadsheetTheme — The current applied theme.

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

getSpreadsheetTimeZone()

Gets the time zone for the spreadsheet.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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');

// Sets the time zone of the spreadsheet.
ss.setSpreadsheetTimeZone('America/New_York');

// Gets the time zone of the spreadsheet.
const ssTimeZone = ss.getSpreadsheetTimeZone();

// Logs the time zone to the console.
console.log(ssTimeZone);

Return

String — The time zone, specified in "long" format (for example, "America/New_York", as listed by Joda.org).

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

getUrl()

Returns the URL for the given spreadsheet.

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

Return

String — The URL for the given spreadsheet.

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

getViewers()

Gets the list of viewers and commenters for this Spreadsheet.

Return

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

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

hideColumn(column)

Hides the column or columns in the given range.

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

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

// This hides the first 3 columns
let range = sheet.getRange("A:C");
sheet.hideColumn(range);

Parameters

NameTypeDescription
columnRangeThe column range to hide.

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

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.

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

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

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

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

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

insertColumnsAfter(afterPosition, howMany)

Inserts a given number of columns after the given column position.

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

// Inserts two columns after the first column on the first sheet of the spreadsheet.
sheet.insertColumnsAfter(1,2);

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

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

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

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

insertDataSourceSheet(spec)

Inserts a new DataSourceSheet in the spreadsheet and starts data execution. As a side effect, this also makes the new sheet the active sheet.

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

// Activates BigQuery operations.
SpreadsheetApp.enableBigQueryExecution();

// 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');

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

Parameters

NameTypeDescription
specDataSourceSpecThe data source specification to insert with.

Return

DataSourceSheet — The new data source 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

insertImage(blobSource, column, row)

Inserts a Spreadsheet as an image in the document at a given row and column. The image size is retrieved from the blob contents. The maximum supported blob size is 2MB.

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

var blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');
sheet.insertImage(blob, 1, 1);

Parameters

NameTypeDescription
blobSourceBlobSourceThe blob containing the image contents, MIME type, and (optionally) name.
columnIntegerThe column position.
rowIntegerThe row position.

Return

OverGridImage — The inserted image.

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

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

Inserts a Spreadsheet as an image in the document at a given row and column, with a pixel offset. The image size is retrieved from the blob contents. The maximum supported blob size is 2MB.

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

var blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');
sheet.insertImage(blob, 1, 1, 10, 10);

Parameters

NameTypeDescription
blobSourceBlobSourceThe blob containing the image contents, MIME type, and (optionally) name.
columnIntegerThe column position.
rowIntegerThe row position.
offsetXIntegerThe horizontal offset from cell corner in pixels.
offsetYIntegerThe vertical offset from cell corner in pixels.

Return

OverGridImage — The inserted image.

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

insertImage(url, column, row)

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

The provided URL must be publicly accessible.

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.

Return

OverGridImage — The inserted image.

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

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

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

The provided URL must be publicly accessible.

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.
offsetXIntegerThe horizontal offset from cell corner in pixels.
offsetYIntegerThe vertical offset from cell corner in pixels.

Return

OverGridImage — The Inserted image.

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

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

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

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

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

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

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

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

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

insertSheet()

Inserts a new sheet into the spreadsheet, using a default sheet name. The new sheet becomes the active sheet.

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

Return

Sheet — The new 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

insertSheet(sheetIndex)

Inserts a new sheet into the spreadsheet at the given index. The new sheet becomes 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.

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

insertSheet(sheetIndex, options)

Inserts a new sheet into the spreadsheet at the given index and uses optional advanced arguments. The new sheet becomes the active sheet.

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

Parameters

NameTypeDescription
sheetIndexIntegerThe index 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 is copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — The new 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

insertSheet(options)

Inserts a new sheet into the spreadsheet, using a default sheet name and optional advanced arguments. The new sheet becomes the active sheet.

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

Parameters

NameTypeDescription
optionsObjectOptional JavaScript advanced arguments, listed below.

Advanced parameters

NameTypeDescription
templateSheetAll data from the template sheet object is copied to the new sheet. The sheet template must be one of the sheets of this Spreadsheet object.

Return

Sheet — The new 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

insertSheet(sheetName)

Inserts a new sheet into the spreadsheet with the given name. The new sheet becomes 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.

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

insertSheet(sheetName, sheetIndex)

Inserts a new sheet into the spreadsheet with the given name at the given index. The new sheet becomes 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.

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

insertSheet(sheetName, sheetIndex, options)

Inserts a new sheet into the spreadsheet with the given name at the given index and uses optional advanced arguments. The new sheet becomes 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.
sheetIndexIntegerThe index of the 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 is copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — The new 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

insertSheet(sheetName, options)

Inserts a new sheet into the spreadsheet with the given name and uses optional advanced arguments. The new sheet becomes 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 is copied to the new sheet. The sheet template must be one of the sheets of the Spreadsheet object.

Return

Sheet — The new 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

insertSheetWithDataSourceTable(spec)

Inserts a new sheet in the spreadsheet, creates a DataSourceTable spanning the entire sheet with the given data source specification, and starts data execution. As a side effect, makes the new sheet the active sheet.

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

// Activates BigQuery operations.
SpreadsheetApp.enableBigQueryExecution();

// 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');

// Adds a sheet and sets cell A1 as the parameter cell.
const parameterCell = ss.insertSheet('parameterSheet').getRange('A1');

// Sets the value of the parameter cell to 'Duke'.
parameterCell.setValue('Duke');

const query = 'select * from `bigquery-public-data`.`ncaa_basketball`.' +
  '`mbb_historical_tournament_games` WHERE win_school_ncaa = @SCHOOL';

// Adds a data source with a query parameter.
// TODO(developer): Update the project ID to your own Google Cloud project ID.
const dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
 .asBigQuery()
 .setProjectId('project-id-1')
 .setRawQuery(query)
 .setParameterFromCell('SCHOOL', 'parameterSheet!A1')
 .build();

// Adds sheets for the data source and data source table to the spreadsheet.
ss.insertSheetWithDataSourceTable(dataSourceSpec);

Parameters

NameTypeDescription
specDataSourceSpecThe data source specification to insert with.

Return

Sheet — The new 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

isColumnHiddenByUser(columnPosition)

Returns whether the given column is hidden by the user.

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

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

Parameters

NameTypeDescription
columnPositionIntegerThe position of the column to examine.

Return

Booleantrue if the column is hidden, false otherwise.

Authorization

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

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

isIterativeCalculationEnabled()

Returns whether iterative calculation is activated in this spreadsheet.

// Opens the spreadsheet file by its URL. If you created your script from within a
// Google Sheets spreadsheet, 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');

// Activates iterative calculation on the spreadsheet.
ss.setIterativeCalculationEnabled(true);

// Logs whether iterative calculation is activated for the spreadsheet.
console.log(ss.isIterativeCalculationEnabled());

Return

Booleantrue if iterative calculation is activated, false otherwise.

Authorization

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

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

isRowHiddenByFilter(rowPosition)

Returns whether the given row is hidden by a filter (not a filter view).

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

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

Parameters

NameTypeDescription
rowPositionIntegerThe position of the row to examine.

Return

Booleantrue if the row is hidden, false otherwise.

Authorization

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

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

isRowHiddenByUser(rowPosition)

Returns whether the given row is hidden by the user.

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

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

Parameters

NameTypeDescription
rowPositionIntegerThe position of the row to examine.

Return

Booleantrue if the row is hidden, false otherwise.

Authorization

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

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

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 that there are 2 sheets in the current
// active spreadsheet: one named "first" in position 1 and another named "second"
// in position 2.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Gets the "first" sheet and activates it.
var sheet = spreadsheet.getSheetByName("first").activate();

// Logs 'Current index of sheet: 1'
console.log("Current index of sheet: %s", sheet.getIndex());

spreadsheet.moveActiveSheet(2);

// Logs 'New index of sheet: 2'
console.log("New index of sheet: %s", sheet.getIndex());

Parameters

NameTypeDescription
posIntegerThe 1-index position to move the active sheet to in the list of sheets.

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

moveChartToObjectSheet(chart)

Creates a new SheetType.OBJECT sheet and moves the provided chart to it. If the chart is already on its own sheet, that sheet is returned without creating a new one.

var sheet = SpreadsheetApp.getActiveSheet();
var chart = sheet.newChart().setPosition(1, 1, 0, 0).build();
sheet.insertChart(chart);
var objectSheet = SpreadsheetApp.getActive().moveChartToObjectSheet(chart);

Parameters

NameTypeDescription
chartEmbeddedChartThe chart to move.

Return

Sheet — The sheet that the chart is on.

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

refreshAllDataSources()

Refreshes all supported data sources and their linked data source objects, skipping invalid data source objects.

Use SpreadsheetApp#enable...Execution() methods to enable data execution for specific data source type.

// Activates BigQuery operations.
SpreadsheetApp.enableBigQueryExecution();

// 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 the first data source sheet on the spreadsheet.
const dataSheet = ss.getDataSourceSheets()[0];

// Refreshes all data sources on the spreadsheet.
ss.refreshAllDataSources();

// Logs the last refreshed time of the first data source sheet.
console.log(`Last refresh time: ${dataSheet.getStatus().getLastRefreshedTime()}`);

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

removeEditor(emailAddress)

Removes the given user from the list of editors for the Spreadsheet. This method doesn't 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, or if the Spreadsheet is in a shared drive that the user can access.

For Drive files, this also removes the user from the list of viewers.

Parameters

NameTypeDescription
emailAddressStringThe email address of the user to remove.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

removeEditor(user)

Removes the given user from the list of editors for the Spreadsheet. This method doesn't 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, or if the Spreadsheet is in a shared drive that the user can access.

For Drive files, this also removes the user from the list of viewers.

Parameters

NameTypeDescription
userUserA representation of the user to remove.

Return

Spreadsheet — This Spreadsheet, for chaining.

Authorization

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

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

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 used when added the menu
}
function foo(){
// Do nothing
}

Parameters

NameTypeDescription
nameStringThe name of the menu to remove.

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

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

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

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 doesn't 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, or if the Spreadsheet is in a shared drive that the user can access.

For Drive files, this also removes the user from the list of editors.

Parameters

NameTypeDescription
emailAddressStringThe email address of the user to remove.

Return

Spreadsheet — This Spreadsheet for chaining.

Authorization

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

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