How to return an error from a custom function?

In this tutorial, I will show you how to return an error from a custom function in Google Sheets.

Prerequisites

• Basic coding concepts in Google Apps Script

• Custom Functions in Google Sheets

• Values, types and operators in Apps Script (especially the typeof operator)

How to return an error from a custom function?

Consider the custom function GRADE() which takes a student's score on a test as input and returns the corresponding grade. Let's assume that the score has to be a number between 0 to 100.

function GRADE(score) {
  if(score >= 90) {
    return "A";
  } else if(score >= 80) {
    return "B";
  } else if(score >= 70) {
    return "C";
  } else if(score >= 60) {
    return "D";
  } else {
    return "F";
  }
}

This function can then be used to assign grades based on test scores.

However, the above function does not check if the input value is valid. For example, it does not check if the score is a number and if it is between 0 and 100.

The scores in rows 10, 11 and 12 in the screenshot below are invalid yet the function GRADE() incorrectly returns a grade instead of notifying the user that an error has occurred.

How should we deal with invalid user input to custom functions? One way would be to return an output that informs the user that the input was invalid. The code below uses this approach. It uses the typeof operator to check if the score is a number and returns an error otherwise. Similarly, it checks if the score is above 100 or below 0 and returns an error.

function GRADE(score) {
  if(typeof score != "number") {
    return "Invalid input: score must be a number between 0 and 100.";
  } else if(score > 100) {
    return "Invalid input: score must be a number between 0 and 100.";
  } else if(score >= 90) {
    return "A";
  } else if(score >= 80) {
    return "B";
  } else if(score >= 70) {
    return "C";
  } else if(score >= 60) {
    return "D";
  } else if(score >= 0) {
    return "F";
  } else {
    return "Invalid input: score must be a number between 0 and 100.";
  }
}

As you can see in the screenshot below, the user is informed that an error occurred but the user will have to pay attention to the output text to notice this.

Furthermore, Google Sheets functions like ISERROR() will not be able to detect that an error occurred. You'll notice that the ISERROR() function returns FALSE in cells C10, C11 and C12 even though the values in cells B10, B11 and B12 are actually errors.

A better approach in these situations is to return an error from the custom function that tells the user why the error occurred so they can then fix it. To return an error from a custom function, use the throw statement.

throw "Some error message that tells the user what error occurred and why it occurred?";

The function below throws errors, so to speak, instead of returning them.

function GRADE(score) {
  if(typeof score != "number") {
    throw "Invalid input: score must be a number between 0 and 100.";
  } else if(score > 100) {
    throw "Invalid input: score must be a number between 0 and 100.";
  } else if(score >= 90) {
    return "A";
  } else if(score >= 80) {
    return "B";
  } else if(score >= 70) {
    return "C";
  } else if(score >= 60) {
    return "D";
  } else if(score >= 0) {
    return "F";
  } else {
    throw "Invalid input: score must be a number between 0 and 100.";
  }
}

Now, you'll notice that the cells B10, B11 and B12 clearly communicate that an error has occurred.

When you hover over one of those cells, the error message will be displayed.

This approach also works well with the ISERROR() function. Unlike the previous approach, the ISERROR() function returns TRUE in cells C10, C11 and C12 since the values in cells B10, B11 and B12 are errors.

Therefore, when you have to return errors from custom functions, consider using the throw statement to throw errors instead of returning values to communicate that an error occurred.

Conclusion

In this tutorial I showed you how to return errors from a custom function in Google Sheets using the throw statement.

Hope this post was helpful! Thank you for reading.