How to Create Custom Functions in Google Sheets (Using Google Apps Script)

Custom functions in Google Sheets empower users to go beyond standard formulas, offering the flexibility to perform unique calculations or automate repetitive tasks. By leveraging Google Apps Script, you can create tailored functions that address your specific data processing needs. This guide will explore how to write custom functions in Google Sheets, enhance them with advanced features, debug potential issues, and adopt best practices for smooth implementation.

How to Write a Custom Function in Google Sheets

Custom functions in Google Sheets are written in Google Apps Script, which is based on JavaScript. These functions allow you to perform calculations and actions that are not natively available in Sheets.

Step 1: Open Google Sheets

Open the Google Sheet where you want to add the custom function.

Step 2: Open the App Script Editor

To access the Apps Script editor:

• Click on Extensions in the top menu.
• Select Apps Script from the dropdown menu. This will open the script editor in a new tab.

Step 3: Write Your Custom Function

In the script editor, you can write your custom function using JavaScript. Here’s an example of a simple custom function that adds two numbers:

function ADD_TWO_NUMBERS(a, b) {
  return a + b;
}

In this example:

• ADD_TWO_NUMBERS is the name of the custom function.
• a and b are the two parameters that the function takes.
• The function returns the sum of a and b.

Step 4: Save Your Script

After writing your function, click on the Save button in the top left corner or just click CTRL +s(for Windows) and CMD + s(for Mac) of the script editor, and give your project a name.

Step 5: Use the Custom Function in Google Sheets

Now that you have created your custom function, you can use it directly in your Google Sheets, just like any other built-in function. In a cell, type:

=ADD_TWO_NUMBERS(5, 10)

Then enter and the result will be 15 in this case, as the custom function adds the two numbers.

Advanced Custom Functions

You can write more complex custom functions that perform tasks such as manipulating strings, interacting with other Google services (e.g., Google Calendar or Gmail), or even pulling data from external APIs.

Example 1: Concatenating Strings

Here’s an example of a custom function that combines two strings:

function CONCATENATE_STRINGS(str1, str2) {
  return str1 + " " + str2;
}

If you use this function in Google Sheets like this:

=CONCATENATE_STRINGS("Hello", "World")

It will return:

Hello World

Example 2: Fetching Data from an External API

If you want to get data from an external API (such as a weather API), you can use Apps Script’s built-in UrlFetchApp service. Here’s an example of how you can fetch weather data:

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;
}

You can then use this function in your sheet:

=GET_WEATHER("London")

This will return the weather description for the specified city (e.g., "clear sky").

Debugging Custom Functions in Google Sheets

When creating custom functions, it's important to test and debug them properly. Google Apps Script provides the following debugging tools:

Logger.log() - Use this to print messages to the logs. For example:

Logger.log(a + " " + b);

You can view the logs by clicking View > Logs in the script editor.

Error Handling - Add error handling in your custom function to prevent it from crashing unexpectedly:

function SAFE_DIVIDE(a, b) {
  if (b == 0) {
    return "Error: Division by zero";
  } else {
    return a / b;
  }
}

Execution Transcript - To better understand what happens when your function runs, click View > Execution Transcript to see a detailed log of actions.

Sharing Custom Functions

Once you’ve created a custom function, you can share your Google Sheets file with others. When you share the sheet, the custom functions will also be available to those with access. However, if you want others to use the function outside your Google Sheet, you can create an add-on using Apps Script that others can install.

Best Practices for Writing Custom Functions

• Keep it Simple: Write functions that are simple and serve a single purpose. If your function is too complex, break it into smaller helper functions.
• Use Caching: If your custom function fetches external data (e.g., an API call), use caching to avoid exceeding Google Apps Script's daily quotas.
• Test Thoroughly: Always test your custom functions with different inputs to ensure they work as expected.
• Document Your Functions: Add comments in your script so that others can understand the purpose of your functions and how to use them.

Conclusion

Creating custom functions in Google Sheets allows you to personalize your data management experience, tackling challenges that standard formulas cannot address. By learning how to write, debug, and optimize these functions, you unlock greater efficiency and functionality in your spreadsheets. Embrace these tips to make your workflows more dynamic and productive.