Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these arent enough for your needs, you can use Google Apps Script to write custom functions say, to convert meters to miles or fetch live content from the Internet then use them in Google Sheets just like a built-in function. Show Getting startedCustom functions are created using standard JavaScript. If you're new to JavaScript, Codecademy offers a great course for beginners. (Note: this course wasn't developed by and isn't associated with Google.) Here's a simple custom function, named DOUBLE, which multiplies an input value by 2: function DOUBLE(input) { return input * 2; }If you don't know how to write JavaScript and don't have time to learn, check the add-on store to see whether someone else has already built the custom function you need. Creating a custom functionTo write a custom function:
Now you can use the custom function. Getting a custom function from the Google Workspace MarketplaceThe Google Workspace Marketplace offers several custom functions as add-ons for Google Sheets. To use or explore these add-ons:
Using a custom functionOnce you've written a custom function or installed one from the Google Workspace Marketplace, it's as easy to use as a built-in function:
Guidelines for custom functionsBefore writing your own custom function, there are a few guidelines to know. NamingIn addition to the standard conventions for naming JavaScript functions, be aware of the following:
ArgumentsLike a built-in function, a custom function can take arguments as input values:
Return valuesEvery custom function must return a value to display, such that:
Data typesGoogle Sheets stores data in different formats depending on the nature of the data. When these values are used in custom functions, Apps Script treats them as the appropriate data type in JavaScript. These are the most common areas of confusion:
AutocompleteGoogle Sheets supports autocomplete for custom functions much like for built-in functions. As you type a function name in a cell, you will see a list of built-in and custom functions that matches what you enter. Custom functions will appear in this list if their script includes a JsDoc @customfunction tag, as in the DOUBLE() example below. AdvancedUsing Apps Script servicesCustom functions can call certain Apps Script services to perform more complex tasks. For example, a custom function can call the Language service to translate an English phrase into Spanish. Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, specifically the following: Supported services Notes Cache Works, but not particularly useful in custom functions HTML Can generate HTML, but cannot display it (rarely useful) JDBC Language Lock Works, but not particularly useful in custom functions Maps Can calculate directions, but not display maps Properties getUserProperties() only gets the properties of the spreadsheet owner. Spreadsheet editors can't set user properties in a custom function. Spreadsheet Read only (can use most get*() methods, but not set*()).Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()). URL Fetch Utilities XML If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function. To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services. SharingCustom functions start out bound to the spreadsheet they were created in. This means that a custom function written in one spreadsheet can't be used in other spreadsheets unless you use one of the following methods:
OptimizationEach time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow. Consequently, if you plan to use a custom function multiple times on a large range of data, consider modifying the function so that it accepts a range as input in the form of a two-dimensional array, then returns a two-dimensional array that can overflow into the appropriate cells. For example, the DOUBLE() function shown above can be rewritten to accept a single cell or range of cells as follows: /** * Multiplies the input value by 2. * * @param {number|Array<Array<number>>} input The value or range of cells * to multiply. * @return The input multiplied by 2. * @customfunction */ function DOUBLE(input) { return Array.isArray(input) ? input.map(row => row.map(cell => cell * 2)) : input * 2; }The above approach uses the map method of JavaScript's Array object to recursively call DOUBLE on every value in the two-dimensional array of cells. It returns a two-dimensional array that contains the results. This way, you can call DOUBLE just once but have it calculate for a large number of cells at once, as shown in the screenshot below. (You could accomplish the same thing with nested if statements instead of the map call.) Similarly, the custom function below efficiently fetches live content from the Internet and uses a two-dimensional array to display two columns of results with just a single function call. If each cell required its own function call, the operation would take considerably more time, since the Apps Script server would have 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 */ function getBlogPosts() { var array = []; var url = 'https://gsuite-developers.googleblog.com/atom.xml'; var xml = UrlFetchApp.fetch(url).getContentText(); var document = XmlService.parse(xml); var root = document.getRootElement(); var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom'); var entries = document.getRootElement().getChildren('entry', atom); for (var i = 0; i < entries.length; i++) { var title = entries[i].getChild('title', atom).getText(); var date = entries[i].getChild('published', atom).getValue(); array.push([title, date]); } return array; }These techniques can be applied to nearly any custom function that is used repeatedly throughout a spreadsheet, although the implementation details will vary depending on the function's behavior. |