The new version of Google Sheets includes changes to several Apps Script features, as well as a few known issues. If you are affected by these issues or any other unsupported features, please continue to use the current version of Sheets for now.
Permanent changes
Add-ons instead of script gallery
The script gallery is not available in the new version of Sheets. It has been replaced by the add-on store.
Autocomplete for custom functions
The new version of Sheets supports autocomplete for
custom functions, much like
autocomplete for built-in functions.
Custom functions only appear in autocomplete if they are preceded by a
JsDoc comment that includes a
@customfunction tag, as in the following example:
/**
* Multiplies the input value by 2.
*
* @param {number} input The value to multiply.
* @customfunction
*/
function double(input) {
return input * 2;
}
Custom function arguments must be deterministic
Built-in spreadsheet functions that return a different result each time they
calculate — such as NOW() or TODAY() — are not allowed as arguments to a
custom function in the new version of Sheets. If a custom function tries to
return a value based on a volatile built-in function, it will display
Loading... indefinitely.
Newer syntax for menus, dialogs, and sidebars
The new version of Google Sheets supports the same
getUi() syntax
used to create menus,
dialogs, and sidebars in Google Docs and Forms.
Although the Browser class continues to work for both old and new
spreadsheets, new Sheets scripts should use the getUi() syntax if possible.
Number and date formats
The number formats used by the new version of Sheets are now compatible with Excel's number format specification. This format has some differences from the old format, especially for date or time values. Specifically:
- Literal text is surrounded by quotation marks.
- Both months and minutes are represented by a lowercase
m. - The AM/PM section is represented as
am/pm.
Known issues
The list of known issues below is current as of early April 2014, but not exhaustive. For a more current list of issues, see the Apps Script issue tracker.
Ranges in installable edit triggers
In the event object for
installable edit triggers, the value for
e.range is always a Range object that corresponds to cell A1, regardless
of which cell was edited.
Setting dates in cells with custom formats
If a script sets a date value in a cell with a custom date format, the cell reverts to the default date format.
Issues with individual methods
Various spreadsheet-related methods behave incorrectly or are not yet available, as listed in the following table.
| Method | Status |
|---|---|
Sheet.autoResizeColumn(columnPosition) |
These methods are not yet available. |
Spreadsheet.getFormUrl() |
|
Sheet.getActiveRange() |
When used in a custom function, these methods return an incorrect value if the custom function is used in more than one cell with the same function arguments. They also return an incorrect value if called from an installable edit trigger. |
Spreadsheet.getActiveRange() |
|
SpreadsheetApp.getActiveRange() |
|
Spreadsheet.insertSheet() |
Exceptions thrown by this method cannot be caught. |
Range.getNumberFormat() |
When called on a cell that uses the default number format, these methods return a value that, if set on a cell, causes a decimal point to appear regardless of whether the number requires a decimal point. |
Range.getNumberFormats() |
|
DocsList.File.makeCopy() |
Use the DriveApp version of File.makeCopy() to make copies of new spreadsheets. |
DocsList.File.getAs(contentType) |
These methods cannot generate PDFs from spreadsheets created in the new version of Sheets. |
DriveApp.File.getAs(contentType) |
Deprecated methods
The following Spreadsheet methods have been deprecated in the new version of
Sheets. To determine information about a file's sharing settings, use the
DriveApp methods
File.getSharingAccess()
and File.getSharingPermission()
instead.