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:
- Createor open a spreadsheet in Google Sheets.
- Select menu itemextensions >application script.
- Delete any code in the script editor. For him
GUTabove, just copy and paste the code into the script editor. - Click Save at the topsave on computer.
now you canUse custom function.
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:
- Createor open a spreadsheet in Google Sheets.
- Click abovePlugins > Get Plugins.
- once theGoogle Workspace Marketplaceopen it, click the search box in the upper-right corner.
- Type "custom function" and press Enter.
- When you find a custom function plugin that interests you, click on itTo installto install it
- A dialog box may inform you that the plugin requires authorization. If so, please read the notice carefully and click on itAllow.
- 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:
- Click in the cell where you want to use the function.
- Enter an equal sign (
=) followed by the name of the function and any input values, e.g. B.=DOUBLE(A1)- and press Enter. - The cell is displayed momentarily
Laden..., then return the result.
Custom Function Guidelines
Before writing your own custom function, there are a few guidelines to keep in mind.
Us
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 functionsif
SOMA(). - 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 syntax
function myFunction(), NOvar myFunction = new function(). - Capitalization does not matter, although spreadsheet function names are traditionally capitalized.
argument
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 function arguments must bedeterministic. That is, built-in spreadsheet functions that return a different result with each calculation, e.g
NOW()Ö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 converted
Dataobjects, butWorking with them can be difficult. - Percentages in sheets are converted to decimals in Apps Script. For example a cell with the value
10%becomes0,1in the application script.
autocomplete
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.
/** * 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;}Progressive
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) |
| JDBC | |
| Language | |
| 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 | |
| XML |
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.
Exchange
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.
improvement
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.
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) ? input.map(row => row.map(cell => 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.)
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 = '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 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.