How to create Custom Functions in Sheets with Google Apps Script code

How to create Custom Functions in Sheets with Google Apps Script code

How to create Custom Functions in Sheets with Google Apps Script code

Google Sheets comes with 100s of built in functions

You can also create your own custom functions using Google Apps Script. Custom functions are created using standard JavaScript syntax within Google Apps Script.
Custom functions never ask users to authorize access to personal data.

To use custom functions click on the cell, and just like any other custom function you can select it in the formula bar for within sheets. Start by typing = in the formula bar, followed by the function name and provide the required arguments for that function. This can be values from other cells in the sheet. The Loading… will display in the sheet and once complete it will return the value in the cell. If there is an error in the input format that isn’t expected in the function you will see the returned error in the cell.

When naming your custom functions in sheets, avoid the built-in function names, as well do not end the name with _ as this is reserved for private functions. The name of the function must be set as the function name in the Apps Script. Best practice is to capitalize the name although capitalization is not required, it does make it easier to read the functions in the code.

Custom functions can be selected and run from the formula bar, by selecting the cell you want to apply the function to and starting to type with the = equal sign then typing in the function name.

How to Create custom Number Functions

1. Create a function name which expects a numeric input value
2. Create the calculation of the data from the input, using the return to send the calculated results back into the cell.

How to Create a custom function that uses the string values and calculates the length of the string.

1. Create a function that requires two parameters, both string values
2. Add the strings together , return the result
3. Add to the returned result the string length value

How to Create a custom function to calculate sales tax

1. Create the function with a numeric parameter
2. Multiply the input value by the value for the tax and return the results.

How to Create a custom function to return Latitude and Longitude of a string value location name.

1. Create a custom function that takes in one string argument
2. Using the Maps object creates a new geocoder. Maps.newGeocoder();

1. Use the geocoder and send the string value from the function into it. If there are results, select the latitude and longitude from the returned object for location data.
2. Return the custom string with Lat and Long back to the function return.