Returning values from custom functions in Google Sheets

In this tutorial, I'll describe how to return values, errors and arrays from a custom function. This knowledge will hopefully be useful to you as you write your own custom functions in Google Sheets.

We'll be writing a custom function called ONE_TO_N() in this tutorial. This function accepts a number N as a parameter and it outputs all of the numbers from 1 to N. This output is displayed on a single row starting with the cell where you entered the formula =ONE_TO_N(<some number>).

The screenshot below shows you the output returned by the function for N = 5.

We'll start simple and we'll progressively make changes to the function until we get the output we want. Along the way, we'll learn more about returning values from a custom function.

Returning a value from a custom function

We'll start by simply returning the value N that is passed to the function. Here value is called an argument (or parameter) of the function. This variable value will contain the number N that users enter into the formula =ONE_TO_N(N) in the spreadsheet.

// Returning a value
function ONE_TO_N(value) {
 return value;
}

Open the script editor in Google Sheets and copy paste the above code into the editor. Once you do that, when you enter the formula =ONE_TO_N(5) in a cell, the output of the formula will be the value returned by your function. Since the function will return the value you provide to it, the value 5 will be displayed in the cell.

Returning an error from a custom function

When writing a custom function, it is a good practice to check if the input(s) provided by the user are valid. This is because your code may not work or produce incorrect output if the input is invalid. In this example, the function ONE_TO_N(value) expects the value to be a number. So, let's explicitly check if the parameter value is a number or not using the typeof operator.

If it is a number, we'll return the value just like before. However, if it is not a number, we will return an error. To return an error, use the keyword throw followed by the error message.

throw 'Error message'

// Returning an error
function ONE_TO_N(value) {

 // Check if the argument value is a number
 if(typeof value != "number")
   throw "Expected a number but got an input of another type.";

 return value;
}

Replace the code in your script editor with the above code and then try entering the formula =ONE_TO_N("five") in your spreadsheet.

Here, "five" is a string and is not a number so an error will be returned.

Returning an array of values from a custom function

So far, we've written a custom function that returns a single value. However, we want it to return all of the numbers from one to N where N is the value you specify. That is, we want it to return an array of numbers from 1 to N.

We'll create an array called output and use a for-loop to populate it with numbers from 1 to N. Then, we'll return this array.

// Returning an array of values
function ONE_TO_N(value) {

 // Check if the argument value is a number
 if(typeof value != "number")
   throw "Expected a number but got an input of another type.";

 // Since value may not be an integer, we'll round it down to nearest integer that is lesser than
 // equal to value.
 value = Math.floor(value);

 // Create an empty array called output
 var output = [];

 // Use a for-loop to add all numbers from 1 to N to the array output.
 for(var i = 1; i <= value; i++) {
   output.push(i);
 }

 // Finally we return the array output.
 return output;
}

Replace the code in the script editor with the above code. Now, if you enter the formula =ONE_TO_N(5) in a cell, the custom function will be run and it will return the array [1,2,3,4,5]. This array will be displayed in your spreadsheet.

Notice that each value in the array is displayed in a separate row? This is because the spreadsheet is a two-dimensional grid consisting of rows and columns. This grid is represented in code as a two-dimensional array where the outer array contains rows and each row in turn is just an array of columns.

Example: A range in Google Sheets is represented in Apps Script as a two-dimensional array

Consider the following range in a Google Sheets spreadsheet. There are three rows and each row contains three columns.

The above range is represented in Apps Script as a two-dimensional array. The outer array contains three inner arrays, one for each row. Each of these inner arrays themselves contain three values, one for each column in that row.

[
    [row1col1, row1col2, row1col3],
    [row2col1, row2col2, row2col3],
    [row3col1, row3col2, row3col3]
]

Please also refer to the following tutorials for more information:

• Range in Google Sheets

• Reading from and writing to a range in Google Sheets using Apps Script

Returning a two-dimensional array from a custom function

Although, the code we've written so far displays the correct set of numbers, we're not yet done. We want these numbers to be displayed in a single row. Therefore, we need to modify our code to return a two-dimensional array such that the outer array contains a single array (i.e., a single row) and this inner array contains the values 1 to 5.

// Returning a two-dimensional array of values
function ONE_TO_N(value) {

 // Check if the argument value is a number
 if(typeof value != "number")
   throw "Expected a number but got an input of another type.";

 // Since value may not be an integer, we'll round it down to nearest integer that is lesser than
 // equal to value.
 value = Math.floor(value);

 // Create an outer array to contain rows
 var rows = [];

 // Create an inner array to contain the values 1 to N
 var cols = [];

 // Use a for-loop to add all numbers from 1 to N to the array cols.
 for(var i = 1; i <= value; i++) {
   cols.push(i);
 }

 // Create a two-dimensional array by making the array
 // cols an element of the array rows
 // The push() Array method is used to add an element to an array.
 rows.push(cols);

 // Return this two-dimensional array.
 return rows;
}

Replace the code in your script editor with the above code.

Now when you enter the formula =ONE_TO_N(5) into a cell, instead of returning [1,2,3,4,5], the custom function will return a two-dimensional array containing a single row with five columns:

[
  [1,2,3,4,5]
]

We finally see the desired output in the spreadsheet!

Conclusion

In this tutorial I described how to return values, errors and arrays from a custom function. Hope you found this tutorial helpful and thanks for reading!