Google Sheets is an incredibly powerful tool. But did you know you can extend its powers by creating your own formulas? Although Google Sheets already comes with multiple functions to do higher-level things (IF..THEN, SWITCH, QUERY), custom formulas can help you do more advanced stuff and can often be simpler and more maintainable solutions.
Note: Custom formulas are created in Javascript (via Google Apps Script). Therefore, preliminary knowledge of the language is necessary.
Custom Formula to Find Primality
To understand how they work, we will be creating a simple formula to check if a number is prime.
First, open the “Script Editor” in your spreadsheet by going to the Tools menu.
This will take you to Google Apps Script Editor. Save your project by using the save option from the file menu. By default, you’ll have a script named called Code.gs in which you’ll write the code for the custom formula. You can rename it, but it’s not necessary.
As I mentioned earlier, Google App Scripts are written in Javascript. To check if a number is prime, a JS function will look something like this:
function ISPRIME(number) {
for (var i = 2; i <= Math.sqrt (number); i++) {
if(number % i === 0) {
return false;
}
}
return number > 1;
}How do we use it as a custom formula? Answer: by adding a small comment attribute at the top of function (@customfunction). Code here.
Save the above as your script’s content and your formula should be ready to use. To see it at work, try typing =ISPRIME in your spreadsheet.
Note: It might take a few seconds before the custom formula is available in your spreadsheet. In case it doesn’t happen, try refreshing your spreadsheet.
Documenting Custom Formulas
When you use any formula, Google Sheets shows a helpful popup about what the function does and what params it expects. You can make the same happen with your own formulas too by using JSDoc—a format that helps you document your code. Here’s how we can modify our comment to make Sheets show the popup.
/**
* Checks if the given number is prime
*
* @param {number} number The number to check
*
* @customfunction
*/After doing this, Google sheets will show a popup like this when you use the formula.
We saw how we can use custom formulas to do a basic computation, which would be very difficult (impossible?) to do with only built-in functions of Google Sheets. Next time we will be looking at more complex stuff we can do with them.