Custom Functions in Google Sheets | Application Script | google developer (2023)

Google Sheets offers hundreds ofbuilt-in functionsifAVERAGE,SOMA, miVLOOKUP. If this is not sufficient for your needs, you can use Google Apps Script to write custom functions, e.g. B. toConvert meters to milesÖGet live content from the web- Then use them in Google Sheets as a built-in function.

Out of

Custom functions are created using standard JavaScript. If you are new to JavaScript, Codecademy offers agreat course for beginners.(Note: This course was not developed by Google and is not affiliated with Google.)

A simple user-defined function is called hereGUT, which multiplies an input value by 2:

/** * Multiplies an input value by 2. * @param {number} input The number to duplicate. * @return The input multiplied by 2. * @customfunction*/function DOUBLE(input) { return input * 2;}

If you don't know how to write JavaScript and don't have time to learn it,Check out the plugin storeto see if someone has already created the custom function you need.

Creating a custom function

To write a custom function:

  1. Createor open a spreadsheet in Google Sheets.
  2. Select menu itemextensions >application script.
  3. Delete any code in the script editor. For himGUTabove, just copy and paste the code into the script editor.
  4. Click Save at the topsave on computer.

now you canUse custom function.

(Video) Create Custom Functions on Google Sheets

Get a custom function from the Google Workspace Marketplace

Google Workspace Marketplace offers many custom features such as:Addons for Google Sheets. How to use or explore these add-ons:

  1. Createor open a spreadsheet in Google Sheets.
  2. Click abovePlugins > Get Plugins.
  3. once theGoogle Workspace Marketplaceopen it, click the search box in the upper-right corner.
  4. Type "custom function" and press Enter.
  5. When you find a custom function plugin that interests you, click on itTo installto install it
  6. A dialog box may inform you that the plugin requires authorization. If so, please read the notice carefully and click on itAllow.
  7. The plugin is available in the spreadsheet. To use the plugin in another worksheet, open the other worksheet and click at the topPlugins > Manage Plugins. Find the plugin you want to use and click Optionsmore_vert>Use in this document.

Using a custom function

Once you've written a custom function or installed one from the Google Workspace Marketplace, it's just as easy to use as a built-in function:

  1. Click in the cell where you want to use the function.
  2. Enter an equal sign (=) followed by the name of the function and any input values, e.g. B.=DOUBLE(A1)- and press Enter.
  3. The cell is displayed momentarilyLaden..., then return the result.

Custom Function Guidelines

Before writing your own custom function, there are a few guidelines to keep in mind.


In addition to the standard JavaScript function naming conventions, note the following:

  • A user-defined function name must be different from the names ofbuilt-in functionsifSOMA().
  • The name of a user-defined function cannot end with an underscore (_), indicating a private function in Apps Script.
  • A user-defined function name must be declared using the syntaxfunction myFunction(), NOvar myFunction = new function().
  • Capitalization does not matter, although spreadsheet function names are traditionally capitalized.


Like a built-in function, a user-defined function can take arguments as input values:

  • If you call your function with a single cell reference as an argument (e.g=DOUBLE(A1)), the argument is the value of the cell.
  • If you call your function with a reference to a range of cells as an argument (e.g=DOUBLE(A1:B10)), the argument is a two-dimensional array of cell values. For example, in the screenshot below, the arguments are in=DUPLO(A1:B2)are interpreted by Apps Script asdouble([[1,3],[2,4]]). Note that the sample code forGUTTopIt should bechanged to accept an array as input.

    (Video) How to Make Custom Functions in Google Sheets with Javascript and Apps Script

    Custom Functions in Google Sheets | Application Script | google developer (1)

  • Custom function arguments must bedeterministic. That is, built-in spreadsheet functions that return a different result with each calculation, e.gNOW()ÖARBITRARILY()— are not allowed as arguments to a user-defined function. If a user-defined function tries to return a value based on one of these volatile built-in functions, it will appearLaden...unlimited.

return values

Each user-defined function must return a value to be displayed so that:

  • If a user-defined function returns a value, the value is displayed in the cell from which the function was called.
  • When a user-defined function returns a two-dimensional array of values, the values ​​are expanded into adjacent cells as long as those cells are empty. If this causes the array to overwrite the existing cell contents, the user-defined function throws an error. See the above section for an exampleoptimize custom functions.
  • A user-defined function cannot affect cells other than those for which it returns a value. In other words, a user-defined function cannot operate on arbitrary cells, only the cells it is called from and their adjacent cells. To edit any cell, use acustom menuto perform a function.
  • A custom function call should return within 30 seconds. Otherwise the cell will show an error:Internal error executing user-defined function.

type of data

Google Sheets stores data indifferent formatsdepending on the type of data. When these values ​​are used in custom functions, AppsScript treats them ascorrect data type in JavaScriptThese are the most common areas of confusion:

  • Times and Dates in SheetsDataObjects in Apps Script. If the worksheet and the script use different time zones (a rare problem), the custom function needs to compensate.
  • Permanent values ​​in sheets are also convertedDataobjects, butWorking with them can be difficult.
  • Percentages in sheets are converted to decimals in Apps Script. For example a cell with the value10%becomes0,1in the application script.


Google Sheets supports auto-completion for custom functions, e.gbuilt-in functions. When you type a function name in a cell, you see a list of built-in and custom functions that match what you typed.

User-defined functions appear in this list when the script contains:JsDoc@customfunctionmark, of courseGUT()example below.

(Video) How to create custom functions in Google sheets

/** * Multiplies the input value by 2. * * @param {number} input The value to multiply. * @return The input multiplied by 2. * @customfunction */function DOUBLE(input) { return input * 2;}


How to use Apps Script services

User-defined functions can call specific onesApplication Script Servicesto do more complex tasks. For example, a user-defined function can call theLanguageService to translate a sentence from English to Spanish.

Unlike most other types of application scripts, custom functions never prompt users to authorize access to personal data. Consequently, they can only access services that do not have access to personal data, namely the following:

supported services Nuts
cache It works, but isn't particularly useful in custom functions
HTML Can output HTML but not display (rarely useful)
To block It works, but isn't particularly useful in custom functions
cards Can calculate directions but not display maps
Characteristics getUserProperties()preserves sheet owner properties only. Table editors cannot set user properties for a custom role.
spreadsheet Read-only (you can use mosttake*()methods, but notdefine*()).
Cannot open other spreadsheets (Spreadsheet app.openById()ÖSpreadsheet app.openByUrl()).
URL Search
public utilities

If your user-defined function throws the error messageYou are not allowed to call Service X., the service requires user authorization and therefore cannot be used in a custom role.

To use a service other than those listed above, create acustom menuwhich runs an Apps Script function instead of writing a custom function. A function that is invoked from a menu may ask the user for authorization and can therefore use all Apps Script services.


User-defined functions beginconnectedto the table in which they were created. This means that a user-defined function written in one sheet cannot be used in other sheets unless you use one of the following methods:

  • Cliqueextensions > application scriptTo open the script editor, copy the script text from the original worksheet and paste it into the script editor of another worksheet.
  • Make a copy of the table containing the user-defined function by clickingFile > Make Copy. When a worksheet is copied, all attached scripts are also copied. Anyone with access to the spreadsheet can copy the script. (Read-only collaborators can't open the script editor on the original sheet, but if they make a copy, they own the copy and can view the script.)
  • Publish the script as Google SheetsEditor-Plugin.


Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains tens (or hundreds or thousands!) of custom function calls, this process can be quite slow.

(Video) Google Sheets - How to Create a Custom Function

Therefore, if you intend to use a user-defined function multiple times for a variety of data, you should consider modifying the function to accept a range as input in the form of a two-dimensional array and then return a two-dimensional array that overflows can in the appropriate cells.

For example himGUT()The function shown above can be rewritten to accept a single cell or a range of cells as follows:

/** * Multiplies the input value by 2. * * @param {number|Array<Array<number>>} input The value or range of cells to be multiplied *. * @return The input multiplied by 2. * @customfunction */function DOUBLE(input) { return Array.isArray(input) ? => => cell * 2)) : input * 2;}

The above approach uses theMapJavascript Methoddiversityobject to be called recursivelyGUTfor each value in the two-dimensional array of cells. Returns a two-dimensional array containing the results. so you can callGUTonly once, but do the calculation for a large number of cells at once, as the following screenshot shows. (You could also achieve the same thing with nestedconstatements insteadMapFinancial support.)

Custom Functions in Google Sheets | Application Script | google developer (2)

Similarly, the following user-defined function efficiently retrieves live content from the web and uses a two-dimensional array to return two result columns with just one function call. If each cell required its own function call, the process would take much longer because the Apps Script server would have to download and parse the XML source each time.

/** * Displays the title and date of the first page of posts in the * developer blog. * * @return Two columns of data representing posts in the * developer blog. * @customfunction */function getBlogPosts() { var array = []; var url = ''; var xml = UrlFetchApp.fetch(url).getContentText(); var document = XmlService.parse(xml); var root = document.getRootElement(); var atom = XmlService.getNamespace(''); var input = document.getRootElement().getChildren('input', atom); for (var i = 0; i <posts.length; i++) { var title = posts[i].getChild('title', atom).getText(); var data = input[i].getChild('posted', atom).getValue(); array.push([title, date]); } returns an array;}

These techniques can be applied to virtually any user-defined function that is used repeatedly in a worksheet, although implementation details vary depending on the function's behavior.

(Video) Google Sheets - Create Custom Functions (UDF) using Apps Script with AutoComplete Tutorial - Part 5


1. How to create Custom Functions in Sheets with Google Apps Script code
(Laurence Svekis)
2. How can I use JavaScript in Google Sheets?
(Timothy James)
3. Building Custom Functions: Introduction to Programming with Google Sheets 09-E
(Timothy James)
4. UDF (User Defined Functions) in Google Sheets, Arguments & Returns
(Learn Google Sheets & Excel Spreadsheets)
5. Create Your Own Custom Formula in Google Sheets | GoogleApps Script
6. Adding custom functions to Google Sheets - Code Bite #7
(Taste The Code)
Top Articles
Latest Posts
Article information

Author: Arielle Torp

Last Updated: 03/23/2023

Views: 6227

Rating: 4 / 5 (41 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Arielle Torp

Birthday: 1997-09-20

Address: 87313 Erdman Vista, North Dustinborough, WA 37563

Phone: +97216742823598

Job: Central Technology Officer

Hobby: Taekwondo, Macrame, Foreign language learning, Kite flying, Cooking, Skiing, Computer programming

Introduction: My name is Arielle Torp, I am a comfortable, kind, zealous, lovely, jolly, colorful, adventurous person who loves writing and wants to share my knowledge and understanding with you.