Google Apps Script is a powerful tool that allows users to automate tasks in Google Sheets, Google Docs, and other G Suite applications. One of the key features of Google Apps Script is the ability to call functions directly from within a Google Sheets cell.
In this blog post, we’ll walk through how to call a Google Apps Script function from within a Google Sheets cell. We’ll cover the basics of creating a function, how to set it up for cell calling, and how to use it in your sheet.
Creating a Function
Before we dive in, let’s create a simple Google Apps Script function that we can call from a cell. In this example, we’ll create a function that adds two numbers together.
Here’s the code for our addNumbers function:
function addNumbers(a, b) {
return a + b;
}
This function takes in two parameters, a and b, and returns the sum of the two.
Setting Up for Cell Calling
Now that we have our function, we need to set it up so that it can be called from a cell in our sheet. To do this, we’ll add a trigger that listens for changes to the sheet.
First, let’s open the Google Sheets file that we’ll be working on. From there, navigate to Tools > Script editor. This will open a new tab in your browser with the Google Apps Script editor.
In the editor, paste in our addNumbers function.
Next, we’ll add a trigger that listens for changes to the sheet. This will allow us to call our function from within a cell.
To create a trigger, click on the Edit menu and select Current project’s triggers. In the new panel that opens, click the Add Trigger button.
In the dialog box that appears, set the following parameters:
- Choose which function to run: addNumbers
- Which runs at deployment: Head
- Select event source: From spreadsheet
- Select event type: On change
Click Save. You may be prompted to authorize the trigger; if so, follow the on-screen instructions.
Using the Function in Your Sheet
Now that we have our function set up to be called from a cell, let’s use it in our sheet.
First, let’s create two cells with numbers that we want to add together. For example, let’s say we have cells A1 with a value of 3 and B1 with a value of 5.
To call our addNumbers function in cell C1, we simply need to enter the following formula: =addNumbers(A1, B1)
Press enter, and the sum of the two cells should be displayed in cell C1.
Congratulations! You’ve successfully called a Google Apps Script function from within a Google Sheets cell.