Google Sheets is fantastic and free to use, but sometimes you hit a wall. You need a calculation in your spreadsheet that doesn’t exist, or you’re tired of repeating the same manual tasks over and over. That’s where custom functions come in. With Google Apps Script, you can write your own formulas that do exactly what you need—no compromises, no workarounds.
The good news is that you don’t need to be a coding wizard to create your own formulas. If you can understand basic logic, you can write your own functions in Google Sheets. Let us learn it with simple examples.
Table of Contents
Create Your First Function in Google Sheets in 3 Minutes
Step 1: First, open your Google Sheet and head to Extensions > Apps Script. A new tab opens with the script editor.
Now, let’s write something simple. Imagine you want a function that adds two numbers together (yes, Sheets already has this, but bear with me—it’s a great starting point). Enter below command in in script editor:
function ADD_TWO_NUMBERS(a, b) {
return a + b;
}
Click Save (or press Ctrl+S on Windows, Cmd+S on Mac), give your project a name, and you’re done.
Step 2: Back in your sheet to test your function. Type this into any cell:
=ADD_TWO_NUMBERS(5, 10)
Press Enter. You’ll see 15. Your first custom function works.
Real-World Function Examples That Actually Save Time
Example 1: Combining Names
Let’s say you have a spreadsheet with first names in one column and last names in another. You want a full name column without manually typing each one. Add this function in script editor and save it.
function COMBINE_NAMES(firstName, lastName) {
return firstName + " " + lastName;
}
To use it in your spreadsheet, enter below function:
=COMBINE_NAMES("Sarah", "Johnson")
Result: Sarah Johnson
Done. No more copy-pasting names around.
Example 2: Getting Live Weather Data
Now let’s get fancier. Say you’re tracking weather for different cities (maybe you’re running a business across multiple locations). You can pull real weather data straight into your sheet. Add this command in script editor:
function GET_WEATHER(city) {
var response = UrlFetchApp.fetch("https://api.openweathermap.org/data/2.5/weather?q=" + city + "&appid=YOUR_API_KEY");
var json = JSON.parse(response.getContentText());
return json.weather[0].description;
}
(Replace YOUR_API_KEY with your own key. You’ll need a free API key from OpenWeatherMap, but it takes two minutes to get one.)
Now to use it in your sheet:
=GET_WEATHER("New York")
Result: Partly cloudy (or whatever the actual conditions are right now)
Your spreadsheet becomes live and dynamic. No manual updates needed.
When Things Go Wrong in Your Function (And How to Fix Them)
Let’s be real—bugs happen. Google Apps Script gives you tools to figure out what went wrong. Use Logger.log() to see what’s happening:
function DEBUG_EXAMPLE(a, b) {
Logger.log(a + " plus " + b);
return a + b;
}
After running your function, go to View > Logs in the script editor. You’ll see exactly what your function is doing at each step.
Add error checking so your function doesn’t crash:
function SAFE_DIVIDE(a, b) {
if (b == 0) {
return "Error: Can't divide by zero";
} else {
return a / b;
}
}
Now if someone tries to divide by zero, they get a helpful message instead of an error.
How to Share Your Functions
Here’s the beautiful part: when you share your Google Sheet with someone, they automatically get access to your custom functions. They can use them just like any built-in formula.
If you want to share your functions more broadly (like with your whole team or even publicly), you can bundle them into an add-on that anyone can install. That’s a bigger project, but it’s totally doable.
The Takeaway
Custom functions aren’t scary or complicated. They’re just JavaScript code that lives in your spreadsheet and does what you tell it to do. Start small—combine some data, validate information, or pull in outside data. Once you see how powerful this is, you’ll find a dozen uses for it.
The best part? Every complex formula you build is one less manual task eating up your day.
