Access and modify Google Sheets files. Common operations are adding new sheets and adding collaborators.
Methods
Method | Return type | Brief description |
---|---|---|
addDeveloperMetadata(key) | Spreadsheet | Adds developer metadata with the specified key to the top-level spreadsheet. |
addDeveloperMetadata(key, visibility) | Spreadsheet | Adds developer metadata with the specified key and visibility to the spreadsheet. |
addDeveloperMetadata(key, value) | Spreadsheet | Adds developer metadata with the specified key and value to the spreadsheet. |
addDeveloperMetadata(key, value, visibility) | Spreadsheet | Adds developer metadata with the specified key, value, and visibility to the spreadsheet. |
addEditor(emailAddress) | Spreadsheet | Adds the given user to the list of editors for the Spreadsheet . |
addEditor(user) | Spreadsheet | Adds the given user to the list of editors for the Spreadsheet . |
addEditors(emailAddresses) | Spreadsheet | Adds the given array of users to the list of editors for the Spreadsheet . |
addMenu(name, subMenus) | void | Creates a new menu in the Spreadsheet UI. |
addViewer(emailAddress) | Spreadsheet | Adds the given user to the list of viewers for the Spreadsheet . |
addViewer(user) | Spreadsheet | Adds the given user to the list of viewers for the Spreadsheet . |
addViewers(emailAddresses) | Spreadsheet | Adds the given array of users to the list of viewers for the Spreadsheet . |
appendRow(rowContents) | Sheet | Appends a row to the bottom of the current data region in the sheet. |
autoResizeColumn(columnPosition) | Sheet | Sets the width of the given column to fit its contents. |
copy(name) | Spreadsheet | Copies the spreadsheet and returns the new one. |
createDeveloperMetadataFinder() | DeveloperMetadataFinder | Returns a DeveloperMetadataFinder for finding developer metadata within the scope of
this spreadsheet. |
createTextFinder(findText) | TextFinder | Creates a text finder for the spreadsheet, which can be used to find and replace text within the spreadsheet. |
deleteActiveSheet() | Sheet | Deletes the currently active sheet. |
deleteColumn(columnPosition) | Sheet | Deletes the column at the given column position. |
deleteColumns(columnPosition, howMany) | void | Deletes a number of columns starting at the given column position. |
deleteRow(rowPosition) | Sheet | Deletes the row at the given row position. |
deleteRows(rowPosition, howMany) | void | Deletes a number of rows starting at the given row position. |
deleteSheet(sheet) | void | Deletes the specified sheet. |
duplicateActiveSheet() | Sheet | Duplicates the active sheet and makes it the active sheet. |
getActiveCell() | Range | Returns the active cell in this sheet. |
getActiveRange() | Range | Returns the selected range in the active sheet, or null if there is no active range. |
getActiveRangeList() | RangeList | Returns the list of active ranges in the active sheet or null if there are no active
ranges. |
getActiveSheet() | Sheet | Gets the active sheet in a spreadsheet. |
getAs(contentType) | Blob | Return the data inside this object as a blob converted to the specified content type. |
getBandings() | Banding[] | Returns all the bandings in this spreadsheet. |
getBlob() | Blob | Return the data inside this object as a blob. |
getColumnWidth(columnPosition) | Integer | Gets the width in pixels of the given column. |
getCurrentCell() | Range | Returns the current cell in the active sheet or null if there is no current cell. |
getDataRange() | Range | Returns 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() | String | Returns the URL for the form that sends its responses to this spreadsheet, or null if
this spreadsheet has no associated form. |
getFrozenColumns() | Integer | Returns the number of frozen columns. |
getFrozenRows() | Integer | Returns the number of frozen rows. |
getId() | String | Gets a unique identifier for this spreadsheet. |
getImages() | OverGridImage[] | Returns all over-the-grid images on the sheet. |
getIterativeCalculationConvergenceThreshold() | Number | Returns the threshold value used during iterative calculation. |
getLastColumn() | Integer | Returns the position of the last column that has content. |
getLastRow() | Integer | Returns the position of the last row that has content. |
getMaxIterativeCalculationCycles() | Integer | Returns the maximum number of iterations to use during iterative calculation. |
getName() | String | Gets the name of the document. |
getNamedRanges() | NamedRange[] | Gets all the named ranges in this spreadsheet. |
getNumSheets() | Integer | Returns the number of sheets in this spreadsheet. |
getOwner() | User | Returns 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) | Range | Returns the range as specified in A1 notation or R1C1 notation. |
getRangeByName(name) | Range | Returns a named range, or null if no range with the given name is found. |
getRangeList(a1Notations) | RangeList | Returns the RangeList collection representing the ranges in the same sheet specified
by a non-empty list of A1 notations or R1C1 notations. |
getRecalculationInterval() | RecalculationInterval | Returns the calculation interval for this spreadsheet. |
getRowHeight(rowPosition) | Integer | Gets the height in pixels of the given row. |
getSelection() | Selection | Returns the current Selection in the spreadsheet. |
getSheetByName(name) | Sheet | Returns a sheet with the given name. |
getSheetId() | Integer | Returns the ID of the sheet represented by this object. |
getSheetName() | String | Returns 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() | String | Gets the spreadsheet locale. |
getSpreadsheetTheme() | SpreadsheetTheme | Returns the current theme of the spreadsheet, or null if no theme is applied. |
getSpreadsheetTimeZone() | String | Gets the time zone for the spreadsheet. |
getUrl() | String | Returns the URL for the given spreadsheet. |
getViewers() | User[] | Gets the list of viewers and commenters for this Spreadsheet . |
hideColumn(column) | void | Hides the column or columns in the given range. |
hideRow(row) | void | Hides the rows in the given range. |
insertColumnAfter(afterPosition) | Sheet | Inserts a column after the given column position. |
insertColumnBefore(beforePosition) | Sheet | Inserts a column before the given column position. |
insertColumnsAfter(afterPosition, howMany) | Sheet | Inserts a given number of columns after the given column position. |
insertColumnsBefore(beforePosition, howMany) | Sheet | Inserts a number of columns before the given column position. |
insertDataSourceSheet(spec) | DataSourceSheet | Inserts a new DataSourceSheet in the spreadsheet and starts data execution. |
insertImage(blobSource, column, row) | OverGridImage | Inserts a Spreadsheet as an image in the document at a given row and column. |
insertImage(blobSource, column, row, offsetX, offsetY) | OverGridImage | Inserts a Spreadsheet as an image in the document at a given row and column, with a
pixel offset. |
insertImage(url, column, row) | OverGridImage | Inserts an image in the document at a given row and column. |
insertImage(url, column, row, offsetX, offsetY) | OverGridImage | Inserts an image in the document at a given row and column, with a pixel offset. |
insertRowAfter(afterPosition) | Sheet | Inserts a row after the given row position. |
insertRowBefore(beforePosition) | Sheet | Inserts a row before the given row position. |
insertRowsAfter(afterPosition, howMany) | Sheet | Inserts a number of rows after the given row position. |
insertRowsBefore(beforePosition, howMany) | Sheet | Inserts a number of rows before the given row position. |
insertSheet() | Sheet | Inserts a new sheet into the spreadsheet, using a default sheet name. |
insertSheet(sheetIndex) | Sheet | Inserts a new sheet into the spreadsheet at the given index. |
insertSheet(sheetIndex, options) | Sheet | Inserts a new sheet into the spreadsheet at the given index and uses optional advanced arguments. |
insertSheet(options) | Sheet | Inserts a new sheet into the spreadsheet, using a default sheet name and optional advanced arguments. |
insertSheet(sheetName) | Sheet | Inserts a new sheet into the spreadsheet with the given name. |
insertSheet(sheetName, sheetIndex) | Sheet | Inserts a new sheet into the spreadsheet with the given name at the given index. |
insertSheet(sheetName, sheetIndex, options) | Sheet | Inserts a new sheet into the spreadsheet with the given name at the given index and uses optional advanced arguments. |
insertSheet(sheetName, options) | Sheet | Inserts a new sheet into the spreadsheet with the given name and uses optional advanced arguments. |
insertSheetWithDataSourceTable(spec) | Sheet | Inserts 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) | Boolean | Returns whether the given column is hidden by the user. |
isIterativeCalculationEnabled() | Boolean | Returns whether iterative calculation is activated in this spreadsheet. |
isRowHiddenByFilter(rowPosition) | Boolean | Returns whether the given row is hidden by a filter (not a filter view). |
isRowHiddenByUser(rowPosition) | Boolean | Returns whether the given row is hidden by the user. |
moveActiveSheet(pos) | void | Moves the active sheet to the given position in the list of sheets. |
moveChartToObjectSheet(chart) | Sheet | Creates a new SheetType.OBJECT sheet and moves the provided chart to it. |
refreshAllDataSources() | void | Refreshes all supported data sources and their linked data source objects, skipping invalid data source objects. |
removeEditor(emailAddress) | Spreadsheet | Removes the given user from the list of editors for the Spreadsheet . |
removeEditor(user) | Spreadsheet | Removes the given user from the list of editors for the Spreadsheet . |
removeMenu(name) | void | Removes a menu that was added by addMenu(name, subMenus) . |
removeNamedRange(name) | void | Deletes a named range with the given name. |
removeViewer(emailAddress) | Spreadsheet | Removes the given user from the list of viewers and commenters for the Spreadsheet . |
removeViewer(user) | Spreadsheet | Removes the given user from the list of viewers and commenters for the Spreadsheet . |
rename(newName) | void | Renames the document. |
renameActiveSheet(newName) | void | Renames the current active sheet to the given new name. |
resetSpreadsheetTheme() | SpreadsheetTheme | Removes the applied theme and sets the default theme on the spreadsheet. |
setActiveRange(range) | Range | Sets 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) | RangeList | Sets the specified list of ranges as the active ranges in the
active sheet. |
setActiveSelection(range) | Range | Sets the active selection region for this sheet. |
setActiveSelection(a1Notation) | Range | Sets the active selection, as specified in A1 notation or R1C1 notation. |
setActiveSheet(sheet) | Sheet | Sets the given sheet to be the active sheet in the spreadsheet. |
setActiveSheet(sheet, restoreSelection) | Sheet | Sets 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) | Sheet | Sets the width of the given column in pixels. |
setCurrentCell(cell) | Range | Sets the specified cell as the current cell . |
setFrozenColumns(columns) | void | Freezes the given number of columns. |
setFrozenRows(rows) | void | Freezes the given number of rows. |
setIterativeCalculationConvergenceThreshold(minThreshold) | Spreadsheet | Sets the minimum threshold value for iterative calculation. |
setIterativeCalculationEnabled(isEnabled) | Spreadsheet | Sets whether iterative calculation is activated in this spreadsheet. |
setMaxIterativeCalculationCycles(maxIterations) | Spreadsheet | Sets the maximum number of calculation iterations that should be performed during iterative calculation. |
setNamedRange(name, range) | void | Names a range. |
setRecalculationInterval(recalculationInterval) | Spreadsheet | Sets how often this spreadsheet should recalculate. |
setRowHeight(rowPosition, height) | Sheet | Sets the row height of the given row in pixels. |
setSpreadsheetLocale(locale) | void | Sets the spreadsheet locale. |
setSpreadsheetTheme(theme) | SpreadsheetTheme | Sets a theme on the spreadsheet. |
setSpreadsheetTimeZone(timezone) | void | Sets the time zone for the spreadsheet. |
show(userInterface) | void | Displays a custom user interface component in a dialog centered in the user's browser's viewport. |
sort(columnPosition) | Sheet | Sorts a sheet by column, ascending. |
sort(columnPosition, ascending) | Sheet | Sorts a sheet by column. |
toast(msg) | void | Shows a popup window in the lower right corner of the spreadsheet with the given message. |
toast(msg, title) | void | Shows a popup window in the lower right corner of the spreadsheet with the given message and title. |
toast(msg, title, timeoutSeconds) | void | Shows a popup window in the lower right corner of the spreadsheet with the given title and message, that stays visible for a certain length of time. |
unhideColumn(column) | void | Unhides the column in the given range. |
unhideRow(row) | void | Unhides the row in the given range. |
updateMenu(name, subMenus) | void | Updates a menu that was added by addMenu(name, subMenus) . |
waitForAllDataExecutionsCompletion(timeoutInSeconds) | void | Waits 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
Name | Type | Description |
---|---|---|
key | String | The 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
Name | Type | Description |
---|---|---|
key | String | The key for the new developer metadata. |
visibility | DeveloperMetadataVisibility | The 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
Name | Type | Description |
---|---|---|
key | String | The key for the new developer metadata. |
value | String | The 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
Name | Type | Description |
---|---|---|
key | String | The key for the new developer metadata. |
value | String | The value for the new developer metadata. |
visibility | DeveloperMetadataVisibility | The 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
Name | Type | Description |
---|---|---|
emailAddress | String | The 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
Name | Type | Description |
---|---|---|
user | User | A 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
Name | Type | Description |
---|---|---|
emailAddresses | String[] | 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
Name | Type | Description |
---|---|---|
name | String | The name of the menu to be created. |
subMenus | Object[] | 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
Name | Type | Description |
---|---|---|
emailAddress | String | The 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
Name | Type | Description |
---|---|---|
user | User | A 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
Name | Type | Description |
---|---|---|
emailAddresses | String[] | 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
Name | Type | Description |
---|---|---|
rowContents | Object[] | 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
Name | Type | Description |
---|---|---|
columnPosition | Integer | The 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
Name | Type | Description |
---|---|---|
name | String | The 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
Name | Type | Description |
---|---|---|
findText | String | The 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
Name | Type | Description |
---|---|---|
columnPosition | Integer | The 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
Name | Type | Description |
---|---|---|
columnPosition | Integer | The position of the first column to delete. |
howMany | Integer | The 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
Name | Type | Description |
---|---|---|
rowPosition | Integer | The 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
Name | Type | Description |
---|---|---|
rowPosition | Integer | The position of the first row to delete. |
howMany | Integer | The 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
Name | Type | Description |
---|---|---|
sheet | Sheet | The 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
Name | Type | Description |
---|---|---|
contentType | String | The 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()
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
Name | Type | Description |
---|---|---|
columnPosition | Integer | The 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
Name | Type | Description |
---|---|---|
type | ProtectionType | The 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
Name | Type | Description |
---|---|---|
a1Notation | String | The 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
Name | Type | Description |
---|---|---|
name | String | The 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
Name | Type | Description |
---|---|---|
a1Notations | String[] | 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
Name | Type | Description |
---|---|---|
rowPosition | Integer | The 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
Name | Type | Description |
---|---|---|
name | String | The 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
Name | Type | Description |
---|---|---|
startRow | Integer | The position of the starting row. |
startColumn | Integer | The position of the starting column. |
numRows | Integer | The number of rows to return values for. |
numColumns | Integer | The 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
Name | Type | Description |
---|---|---|
column | Range | The 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
Name | Type | Description |
---|---|---|
row | Range | The 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
Name | Type | Description |
---|---|---|
afterPosition | Integer | The 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
Name | Type | Description |
---|---|---|
beforePosition | Integer | The 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
Name | Type | Description |
---|---|---|
afterPosition | Integer | The column after which the new column should be added. |
howMany | Integer | The 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
Name | Type | Description |
---|---|---|
beforePosition | Integer | The column before which the new column should be added. |
howMany | Integer | The 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
Name | Type | Description |
---|---|---|
spec | DataSourceSpec | The 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
Name | Type | Description |
---|---|---|
blobSource | BlobSource | The blob containing the image contents, MIME type, and (optionally) name. |
column | Integer | The column position. |
row | Integer | The 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
Name | Type | Description |
---|---|---|
blobSource | BlobSource | The blob containing the image contents, MIME type, and (optionally) name. |
column | Integer | The column position. |
row | Integer | The row position. |
offsetX | Integer | The horizontal offset from cell corner in pixels. |
offsetY | Integer | The 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
Name | Type | Description |
---|---|---|
url | String | The URL of the image. |
column | Integer | The grid column position. |
row | Integer | The 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
Name | Type | Description |
---|---|---|
url | String | The URL for the image. |
column | Integer | The column position. |
row | Integer | The row position. |
offsetX | Integer | The horizontal offset from cell corner in pixels. |
offsetY | Integer | The 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
Name | Type | Description |
---|---|---|
afterPosition | Integer | The 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
Name | Type | Description |
---|---|---|
beforePosition | Integer | The 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
Name | Type | Description |
---|---|---|
afterPosition | Integer | The row after which the new rows should be added. |
howMany | Integer | The 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
Name | Type | Description |
---|---|---|
beforePosition | Integer | The row before which the new rows should be added. |
howMany | Integer | The 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
Name | Type | Description |
---|---|---|
sheetIndex | Integer | The 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
Name | Type | Description |
---|---|---|
sheetIndex | Integer | The index of the newly created sheet. To insert a sheet as the first one in the spreadsheet, set it to 0. |
options | Object | Optional JavaScript advanced arguments. |
Advanced parameters
Name | Type | Description |
---|---|---|
template | Sheet | All 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
Name | Type | Description |
---|---|---|
options | Object | Optional JavaScript advanced arguments, listed below. |
Advanced parameters
Name | Type | Description |
---|---|---|
template | Sheet | All 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
Name | Type | Description |
---|---|---|
sheetName | String | The 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
Name | Type | Description |
---|---|---|
sheetName | String | The name of the new sheet. |
sheetIndex | Integer | The 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
Name | Type | Description |
---|---|---|
sheetName | String | The name of the new sheet. |
sheetIndex | Integer | The index of the newly inserted sheet. To insert a sheet as the first one in a spreadsheet, set it to 0. |
options | Object | Optional JavaScript advanced arguments. |
Advanced parameters
Name | Type | Description |
---|---|---|
template | Sheet | All 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
Name | Type | Description |
---|---|---|
sheetName | String | The name of the new sheet. |
options | Object | Optional JavaScript advanced arguments. |
Advanced parameters
Name | Type | Description |
---|---|---|
template | Sheet | All 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
Name | Type | Description |
---|---|---|
spec | DataSourceSpec | The 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
Name | Type | Description |
---|---|---|
columnPosition | Integer | The position of the column to examine. |
Return
Boolean
— true
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
Boolean
— true
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
Name | Type | Description |
---|---|---|
rowPosition | Integer | The position of the row to examine. |
Return
Boolean
— true
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
Name | Type | Description |
---|---|---|
rowPosition | Integer | The position of the row to examine. |
Return
Boolean
— true
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
Name | Type | Description |
---|---|---|
pos | Integer | The 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
Name | Type | Description |
---|---|---|
chart | EmbeddedChart | The 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
Name | Type | Description |
---|---|---|
emailAddress | String | The 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
Name | Type | Description |
---|---|---|
user | User | A 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
Name | Type | Description |
---|---|---|
name | String | The 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
Name | Type | Description |
---|---|---|
name | String | The 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
Name | Type | Description |
---|---|---|
emailAddress | String | The 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
removeViewer(user)
Removes the given user from the list of viewers and commenters for the Spreadsheet
. This
method has no effect if the user is an editor, not a viewer. This method also 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
Name | Type | Description |
---|---|---|
user | User | A 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
rename(newName)
Renames the document.
var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.rename("This is the new name");
Parameters
Name | Type | Description |
---|---|---|
newName | String | The new name for the document. |
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
renameActiveSheet(newName)
Renames the current active sheet to the given new name.
// The code below renames the active sheet to "Hello world" SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet("Hello world");
Parameters
Name | Type | Description |
---|---|---|
newName | String | The new name for the current 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
resetSpreadsheetTheme()
Removes the applied theme and sets the default theme on the spreadsheet.
// The code below applies default theme on the spreadsheet. SpreadsheetApp.getActiveSpreadsheet().resetSpreadsheetTheme();
Return
SpreadsheetTheme
— The default 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
setActiveRange(range)
Sets the specified range as the active range
in the active sheet, with
the top left cell in the range as the current cell
.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('A1:D4'); sheet.setActiveRange(range); var selection = sheet.getSelection(); // Current cell: A1 var currentCell = selection.getCurrentCell(); // Active Range: A1:D4 var activeRange = selection.getActiveRange();
Parameters
Name | Type | Description |
---|---|---|
range | Range | The range to set as the active range. |
Return
Range
— the newly 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
setActiveRangeList(rangeList)
Sets the specified list of ranges as the active ranges
in the
active sheet. The last range in the list is set as the active range
.
var sheet = SpreadsheetApp.getActiveSheet(); var rangeList = sheet.getRangeList(['D4', 'B2:C4']); sheet.setActiveRangeList(rangeList);