Apps Script Course: Functions

A function executes a series of instructions when called.

You've been using them since the beginning of this course:

function test() {


Executing a Function

So far, you have executed a single function and have done so from the Apps Script editor or by assigning the function to a shape or an image.

But a function can also be called from another function.

Here's an example with a function that will display an error message in the console and will take the error text as an argument:

// Displays a message in the console
function errorMessage(errorInfo) {
  console.log('Error: ' + errorInfo);

You can then call this function from other functions whenever it's useful:

function test() {
  errorMessage('the "Name" field is empty');
  errorMessage('the "First Name" field is empty');
  errorMessage('the "Postal Code" field is invalid');

When testing in the editor, make sure you execute the correct function:

functions execute apps script editor
To update the dropdown menu, click on "Save project" (or Ctrl + S).

Return Value

A function can return a value when it's useful.

Here is a simple function that calculates the average of 2 numbers for example:

// Calculates the average of 2 numbers
function average(number1, number2) {
  const result = (number1 + number2) / 2;
  return result;

The function then returns a value using the return statement.

In this case, the calculation of the average can also be added directly after return:

// Calculates the average of 2 numbers
function average(number1, number2) {
  return (number1 + number2) / 2;

You can then retrieve the value returned by the function, for example, in a constant or a variable:

function test() {

  const numArray = [10, 20, 30];

  // Average of the first and second number of the array
  const average1 = average(numArray[0], numArray[1])
  console.log(average1); // Displays: 15

  // Average of the second and third number of the array
  const average2 = average(numArray[1], numArray[2])
  console.log(average2); // Displays: 25
The return instruction ends the execution of the function (and can also be used without a value to exit a function).

Using a Function in Google Sheets

You can also use your functions (with a return value) in cells like other Google Sheets functions.

For example, this function will take a range as an argument and return the last row of the range:

// Returns the last row of a range
function LAST_ROW_RANGE(range) {
  return [range[range.length - 1]];

You can then use it on your sheet:

custom google sheets function functions