nightwolfdevGoogle Sheets includes hundreds of built-in functions found in most desktop spreadsheet software. These functions can be used to create formulas that manipulate data and calculate strings and numbers. When these built-in functions aren’t enough for your needs, you can use Google Apps Script to create your own custom functions!
Custom Function Example
Let’s say you have a column of email addresses and you’d like to check if they look valid. Let’s create a custom function that will output TRUE if the email address looks valid, FALSE if the email address looks invalid, or display an error message if an email address was not provided.
By the way, if you’re not comfortable with writing Javascript or don’t have the time to learn, check out the add-on store for Google Sheets. Maybe someone has already written the custom function you’re looking for!
Creating a Custom Function
- Create or open an existing spreadsheet in Google Sheets.
- Navigate to Tools > Script Editor. If you are presented with a welcome screen, select Blank Project to start a new project.
- The script editor will include a starting function. Remove all the code and replace it with the code below.
- Navigate to File > Save. Give the script project a name and select Ok.
function VALIDEMAIL(email) {
if (!email) {
throw 'Please provide an email address.';
}
return /\S+@\S+\.\S+/.test(email);
}
Reviewing the Code
The VALIDEMAIL function gets passed an email value. If no email value was passed in, we throw an error which gets displayed in the cell.
If an email value was passed in correctly, we test the value against a regular expression. The result of the test gets displayed in the cell.
Using a Custom Function
- Select the cell where you’d like to use the custom function.
- Type an equals sign (=) followed by the custom function name and the cell that contains an email address to validate and press Enter.
- The cell will display Loading… and then return the result.
Custom Function Help
Whenever you use a function, Google Sheets displays helpful information about the function. When using your own custom function, you can also display helpful information by simply including a JsDoc @customfunction tag like the following:
/**
* Check if an email address looks valid.
*
* @param {string} email The email address to check.
* @return TRUE or FALSE.
* @customfunction
*/
function VALIDEMAIL(email) {
if (!email) {
throw 'Please provide an email address.';
}
return /\S+@\S+\.\S+/.test(email);
}
