LinkedIn
Twitter
Custom functions in Sheets? Just as you read it, let me explain.
Imagine that you are part of an organization, and you are asked to analyze certain data from a Sheet. You can do some with the default Google Sheets functions like SUM, COUNT, etc.
But if you don't find a function that satisfies any of the tasks to be performed, you can use custom functions.
I think I've lost count of how many times I've needed to use the default built-in functions in Sheets to create formulas that manipulate data and calculate strings or numbers.
Many of which I have used in various files throughout my life repeatedly, but on many other occasions I have been in the situation of creating a formula based on the default functions for a particular purpose.
Have you ever wondered if there is a way to create functions based on your specific needs (business or personal) that can be easily integrated into a specific Sheets file? And at the same time be able to share that file with colleagues who can use its customized function?
I want to tell you that this is possible thanks to the integration of Sheets with Apps Script. Let's take a look at it.
In the following link, we can find the Google Sheets functions that are globally available. But in order to create our own functions and make them available in a specific Sheets file, we must resort to integration with Apps Script.
A custom function is similar to a built-in spreadsheet function like =SUM() or =COUNTIF() except that you define the functions’s behavior.
Google Apps Script lets you do new and cool custom functions with Google Sheets. It includes special APIs to let you programmatically create, read, and edit Google Sheets.
Why use custom functions in Google Sheets?
- Allows you to share them between team members through their attachment
- Provides consistency in the processing, manipulation and output of information in a specific file
- Save time by automating complex formulas
- And much more...
Apps Script can interact with Google Sheets in two main ways:
- Any script can create or modify a spreadsheet if the script's user has appropriate permissions for the spreadsheet, known as Standalone Scripts. A standalone script is any script that is not bound to a Google Sheets, Docs, Slides, or Forms file. These scripts appear in Google Drive files.
- Container-bound Scripts: A script is bound to a Google Sheets, Docs, Slides, or Forms file if it was created from that document rather than as a standalone script. The file that a bound script is attached to is called a "container." Bound scripts generally behave like standalone scripts except that they do not appear in Google Drive, they cannot be detached from the file they are bound to, and they gain a few special privileges over the parent file.
Step-by-step: How to create a custom function in Google Sheets with Apps Script
Our goal is to create a custom function called =countSpecLetter(), which will help us to count a number of occurrences of a specific letter in a given word.
1. To create a new spreadsheet:
- Open the Sheets home screen at sheets.google.com.
- Click New Plus. This will create and open your new spreadsheet.
- You can also create new spreadsheets from the URL sheets.google.com/create.
As you can see, if I start typing on the formula bar, it does not appear any result on the display menu
2. To create a bound script, select Extensions > Apps Script from within Google Sheets.
3. You’ll be redirected by a new tab, to the container-bound script screen. This is what the Apps Script pane looks like:
4. On line number 1, we are going to press the "ENTER" key twice to make space and on that same line we are going to enter the following comment.
/**
* Returns the times that a specific letter appears.
* @PARAM {word, letter} inputs that will be used on the function.
* @return the times that the letter appears in the word.
* @customfunction
*/
The syntax of comments is the same as in C++ and in many other languages:
// a one line comment
/* this is a longer,
* multi-line comment
*/
The comment will serve as a reference to identify both the function of the script, the value(s) it returns, and the description of its function itself.
- @ param {arg1, arg2 indicates the inputs that will be used on the function.
- @return will be the variable/value that the function gives you back
- @customfunction - This is the magic required to make your function appears as custom function in Sheets, if you forget to add this line, function will not be available
5. Below the comments, enter the following code.
//Starting by set the name of the function
//Along with the parameters received
function countSpecLetter(word, letter) {
//Get the lenght of the variable
//This will be useful when we need to use FOR Loop function
var wordLen = word.length;
//This variable was created as a counter
var count = 0;
//Starting the FOR loop with the variable i initialized in 0
//Then it will increase i value until is the same value as the variable lenght
for (i=0; i<wordLen; i++){
//Here I ask if the letter on the i position matches with the letter to look for
//Look that there is two conditions with an OR because analyze if the letter is uppercase too
if(word[i] == letter || word[i] == letter.toUpperCase()) {
//If TRUE, sums 1 to the "count" variable
//If FALSE, nothing happens, continue as usual
count++;
}
}
//Returns the final value of count variable
return count;
}
6. After adding the code into the file, your code should look something like this:
7. Click on the Save button in Apps Script page
8. Just to be sure, go back to the Sheets tab, refresh the page and… MAGIC your custom function is present in your Sheet:
- Click the cell where you want to use the function.
- Type an equals sign (=) followed by the function name and any input value, for example, =countSpecLetter(A3,B2) and press Enter.
- The cell will momentarily display Loading... and then return the result.
9. You can display the menu that appears in the formula to obtain its details, this is very useful when you need to know in advance the parameters that the function needs to be executed
10. In the image below we can see how the function is applied using the required parameters.
In our example, we pass two specific cells as parameters:
- A3: which contains the word to be analyzed
- B2: which contains the value to search in A3
11. Finally, we see how our custom function looks applied to the table. It shows us the number of times that a specific letter appears in the analyzed word.
Now you can save the file and share it in Google Drive so that other team members can use the customized function.
Resources
A few things to know:
Learn more about Google Apps Script: