Google Sheets offers hundreds ofbuilt-in functions likeAVERAGE,SUM, andVLOOKUP. When these aren’tenough for your needs, you can use Google Apps Script to write custom functions— say, toconvert meters to miles orfetchlive content from the Internet — then use themin Google Sheets just like a built-in function.

Getting started

Custom functions are created using standard JavaScript. If you're new toJavaScript, Codecademy offers agreat course for beginners.(Note: this course wasn't developed by and isn't associated with Google.)

Here's a simple custom function, namedDOUBLE, which multiplies aninput value by 2:

/***Multipliesaninputvalueby2.*@param{number}inputThenumbertodouble.*@returnTheinputmultipliedby2.*@customfunction*/functionDOUBLE(input){returninput*2;}

If you don't know how to write JavaScript and don't have time to learn,check the add-on store tosee whether someone else has already built the custom function you need.

Creating a custom function

To write a custom function:

1. Createor open a spreadsheet in Google Sheets.
2. Select the menu itemExtensions>Apps Script.
3. Delete any code in the script editor. For theDOUBLE function above, simplycopy and paste the code into the script editor.
4. At the top, click Savesave.

Now you canuse the custom function.

Getting a custom function from the Google Workspace Marketplace

The Google Workspace Marketplace offers several customfunctions asadd-ons for Google Sheets.To use or explore these add-ons:

1. Createor open a spreadsheet in Google Sheets.
2. At the top, clickAdd-ons > Get add-ons.
3. Once theGoogle Workspace Marketplaceopens, click the search box in the top right corner.
4. Type "custom function" and press Enter.
5. If you find a custom function add-on you're interested in, clickInstallto install it.
6. A dialog box might tell you that the add-on requires authorization. If so,read the notice carefully, then clickAllow.
7. The add-on becomes available in the spreadsheet. To use the add-on in adifferent spreadsheet, open the other spreadsheet and at the top, clickAdd-ons > Manage add-ons. Find the add-on you want to use and clickOptionsmore_vert >Use in thisdocument.

Using a custom function

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

1. Click the cell where you want to use the function.
2. Type an equals sign (=) followed by the function name and any input value —for example,=DOUBLE(A1) — and press Enter.
3. The cell will momentarily displayLoading..., then return the result.

Guidelines for custom functions

Before writing your own custom function, there are a few guidelines to know.

Naming

In addition to the standard conventions for naming JavaScript functions, beaware of the following:

• The name of a custom function must be distinct from the names ofbuilt-in functions likeSUM().
• The name of a custom function cannot end with an underscore (_), whichdenotes a private function in Apps Script.
• The name of a custom function must be declared with the syntaxfunction myFunction(), notvar myFunction = new Function().
• Capitalization does not matter, although the names of spreadsheet functionsare traditionally uppercase.

Arguments

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

• If you call your function with a reference to a single cell as an argument(like=DOUBLE(A1)), the argument will be the value of the cell.

• If you call your function with a reference to a range of cells as anargument (like=DOUBLE(A1:B10)), the argument will be a two-dimensionalarray of the cells' values. For example, in the screenshot below, thearguments in=DOUBLE(A1:B2) are interpreted by Apps Script asdouble([[1,3],[2,4]]). Note that the sample code forDOUBLEfrom above would need to bemodified to accept an array as input.

  
  

• Custom function arguments must bedeterministic. Thatis, built-in spreadsheet functions that return a different result each timethey calculate — such asNOW() orRAND() — are not allowed as argumentsto a custom function. If a custom function tries to return a value based onone of these volatile built-in functions, it will displayLoading...indefinitely.

Return values

Every custom function must return a value to display, such that:

• If a custom function returns a value, the value displays in the cellthe function was called from.
• If a custom function returns a two-dimensional array of values, the valuesoverflow into adjacent cells as long as those cells are empty. If this wouldcause the array to overwrite existing cell contents, the custom function willthrow an error instead. For an example, see the section onoptimizing custom functions.
• A custom function cannot affect cells other than those it returns a value to.In other words, a custom function cannot edit arbitrary cells, only thecells it is called from and their adjacent cells. To edit arbitrary cells,use acustom menu to run a function instead.
• A custom function call must return within 30 seconds. If it does not, thecell displays#ERROR! and the cell note isExceeded maximum execution time(line 0).

Data types

Google Sheets stores data indifferent formats depending onthe nature of the data. When these values are used in custom functions, AppsScript treats them as theappropriate data type in JavaScript.These are the most common areas of confusion:

• Times and dates in Sheets becomeDate objects in Apps Script. If the spreadsheet and thescript use different time zones (a rare problem), the custom function willneed to compensate.
• Duration values in Sheets also becomeDate objects, butworking with them can be complicated.
• Percentage values in Sheets become decimal numbers in Apps Script. Forexample, a cell with a value of10% becomes0.1 in Apps Script.

Autocomplete

Google Sheets supports autocomplete for custom functions much like forbuilt-in functions. As youtype a function name in a cell, you will see a list of built-in and customfunctions that matches what you enter.

Custom functions will appear in this list if their script includes aJsDoc@customfunction tag, as in theDOUBLE() example below.

/***Multipliestheinputvalueby2.**@param{number}inputThevaluetomultiply.*@returnTheinputmultipliedby2.*@customfunction*/functionDOUBLE(input){returninput*2;}

Advanced

Using Google Apps Script services

Custom functions can call certainGoogle Apps Script services to perform more complextasks. For example, a custom function can call theLanguage service to translate an Englishphrase into Spanish.

Unlike most other types of Apps Scripts, custom functions never ask users toauthorize access to personal data. Consequently, they can only call servicesthat do not have access to personal data, specifically the following:

If your custom function throws the error messageYou do not have permission tocall X service., the service requires user authorization and thus cannot beused in a custom function.

To use a service other than those listed above, create acustom menu that runs an Apps Script functioninstead of writing a custom function. A function that is triggered from a menuwill ask the user for authorization if necessary and can consequently use allApps Script services.

Sharing

Custom functions start outbound to thespreadsheet they were created in. This means that a custom function written inone spreadsheet can't be used in other spreadsheets unless you use one of thefollowing methods:

• ClickExtensions>Apps Script toopen the script editor, then copy thescript text from the original spreadsheet and paste it into the script editorof another spreadsheet.
• Make a copy of the spreadsheet that contains the custom function by clickingFile > Make a copy. When a spreadsheet is copied, any scripts attached toit are copied as well. Anyone who has access to the spreadsheet can copy thescript. (Collaborators who have only view access cannot open the script editorin the original spreadsheet. However, when they make a copy, they become theowner of the copy and can see the script.)
• Publish the script as a Google SheetsEditor add-on.

Optimization

Each time a custom function is used in a spreadsheet, Google Sheets makes aseparate call to the Apps Script server. If your spreadsheet contains dozens (orhundreds, or thousands!) of custom function calls, this process can be quiteslow. Some projects with many or complex custom functions might experience atemporary delay in executions.

Consequently, if you plan to use a custom function multiple times on a largerange of data, consider modifying the function so that it accepts a range asinput in the form of a two-dimensional array, then returns a two-dimensionalarray that can overflow into the appropriate cells.

For example, theDOUBLE() function shown above can be rewritten to accept asingle cell or range of cells as follows:

/***Multipliestheinputvalueby2.**@param{number|Array<Array<number>>}inputThevalueorrangeofcells*tomultiply.*@returnTheinputmultipliedby2.*@customfunction*/functionDOUBLE(input){returnArray.isArray(input)?input.map(row=>row.map(cell=>cell*2)):input*2;}

The above approach uses themap method of JavaScript'sArray object to method on thetwo-dimensional array of cells to get each row, then for each row, it usesmap again to return double each cell's value. It returns atwo-dimensional array that contains the results. This way, you can callDOUBLEjust once but have it calculate for a large number of cells at once, as shown inthe screenshot below. (You could accomplish the same thing with nestedifstatements instead of themap call.)

Similarly, the custom function below efficiently fetches live content from theInternet and uses a two-dimensional array to display two columns of results withjust a single function call. If each cell required its own function call, theoperation would take considerably more time, since the Apps Script server wouldhave to download and parse the XML feed each time.

/** * Show the title and date for the first page of posts on the * Developer blog. * * @return Two columns of data representing posts on the *     Developer blog. * @customfunction */functiongetBlogPosts(){vararray=[];varurl='https://gsuite-developers.googleblog.com/atom.xml';varxml=UrlFetchApp.fetch(url).getContentText();vardocument=XmlService.parse(xml);varroot=document.getRootElement();varatom=XmlService.getNamespace('http://www.w3.org/2005/Atom');varentries=document.getRootElement().getChildren('entry',atom);for(vari=0;i <entries.length;i++){vartitle=entries[i].getChild('title',atom).getText();vardate=entries[i].getChild('published',atom).getValue();array.push([title, date]);}returnarray;}

These techniques can be applied to nearly any custom function that is usedrepeatedly throughout a spreadsheet, although the implementation details willvary depending on the function's behavior.