How to Create a Custom Function in Google Sheets

You thought Google was the only one to be able to add functionality to Google Sheets

If you are an Excel or Google Sheets pro, I am sure you have built some extensive one-line formulas in your function bar. I am also sure that you have shared your sheet with coworkers, and they have struggled to replicate the function. This is where custom functions built-in Apps Script come into play.

App Script Concepts

• Custom Functions

JavaScript Concepts

• JsDocs

• const

• function

I will keep this simple and create a function that converts miles to kilometers. There is a built-in function within Google Sheets that will do that for you already, so if this is an actual need of yours, I would use the built-in function.

Open up your Apps Script Environment.

Trying to be fancy by making GIFs, but if it is too small, you go to your toolbar, click Extensions, and then click Apps Script.

This will open up a separate window: your Apps Script Integrated Development Environment (IDE). That sounds scarier than it is; it is where you write code.

When you open this up, you can delete the prepopulated code. Copy and paste the code below into your IDE and hit the save icon.

/**
 * Convert miles to kilometers
 * @param {Number} miles the number of miles you want to convert
 * @returns {Number} the kilometer conversion
 * 
 * @customfunction
 */
function MILEStoKM(miles){
  const conversionRate = 1.60934;
  return miles * conversionRate
}

Now, go into your Google Sheet and type =MILEStoKM(125) into a cell. You should see the output of the calculation in the cell of 201.1675.

What is happening in the code?

/**
 * Convert miles to kilometers
 * @param {Number} miles the number of miles you want to convert
 * @returns {Number} the kilometer conversion
 * 
 * @customfunction
 */

This first part of the code is called JsDoc. This is where you can create a summary of what your function will do, the inputs, and the outputs.

As you can see, the first line is where I put the summary of the function. This is helpful for other people that will use this function within the sheet and if you ever have to come back and edit the code.

The second line is where you let the function know what parameters you should expect and a description.

The third line is where you let the function know what the function is outputting to your Google Sheet.

Let me know what you think of this tutorial in the comments section 👇

After we describe what the function will do in our JsDoc, it is time to write it. To declare a function, you type function then you follow with the name and open and close parentheses. Within the parentheses, you will put the variable name that you named in the JsDoc. To finish the declaration, you add an open and closed { }. Within these brackets is where you write the code your computer will follow when running this function.

function MILEStoKM(miles){
 const conversionRate = 1.60934;
 return miles * conversionRate
}

Within the function, you declare the conversion rate variable with const, which means once you set the variable, you cannot change the value later in the code. This makes sense for a conversion rate since you wouldn’t expect that to change.

In the last line, before the closing brackets, you have your return which is what this function will output.

You did it; you wrote your own custom function within Google Sheets!

Until next time 👋

---

If this tutorial was helpful to you and you want to support my work, consider buying me a slice of 🍕, my favorite food, by quite a wide margin!