In Google Sheets (as with Excel, etc) if a user enters bad input into a formula, an error code will be printed in the offending cell and a small pop-up provides more detail about the error. So if I enter =SQRT(-1), #NUM! is printed in the cell and the pop-up explains that the value needs to be equal to or greater than 0.

How can I replicate this with a custom function in Google Sheets? Throwing an exception mostly works. For example, if I catch an out-of-bounds input value and then throw a custom exception, #ERROR! is printed in the offending cell (which is fine) and the exception's accompanying string is printed in the corresponding pop-up (which is also fine). The problem is that the Google Apps Script engine also appends to the exception message the corresponding source code line that generated the exception. So instead of the pop-up simply reading "The input value must be between 0.0 and 1.0", it will read "The input value must be between 0.0 and 1.0 (line 199)".

Is there any way to suppress the printing of the line number, or to overwrite the pop-up's message? I want to be able to throw custom errors and provide detailed error messages for my users. But I don't want them to be confused by a reference to a line number that is irrelevant to them.

In Google Sheets (as with Excel, etc) if a user enters bad input into a formula, an error code will be printed in the offending cell and a small pop-up provides more detail about the error. So if I enter =SQRT(-1), #NUM! is printed in the cell and the pop-up explains that the value needs to be equal to or greater than 0.

How can I replicate this with a custom function in Google Sheets? Throwing an exception mostly works. For example, if I catch an out-of-bounds input value and then throw a custom exception, #ERROR! is printed in the offending cell (which is fine) and the exception's accompanying string is printed in the corresponding pop-up (which is also fine). The problem is that the Google Apps Script engine also appends to the exception message the corresponding source code line that generated the exception. So instead of the pop-up simply reading "The input value must be between 0.0 and 1.0", it will read "The input value must be between 0.0 and 1.0 (line 199)".

Is there any way to suppress the printing of the line number, or to overwrite the pop-up's message? I want to be able to throw custom errors and provide detailed error messages for my users. But I don't want them to be confused by a reference to a line number that is irrelevant to them.

• javascript
• google-sheets
• google-apps-script
• try-catch
• custom-function

4 Answers 4

if (some_condition)
{
  // will end execution with error    
  throw 'Error. My custom error description.';
}

This is a reported problem.

Visit Issue 4422, star it to vote and for updates.

Just Adding to the comment above about the throw. This works nicely because in a google sheet it carries the inner emessage to the pop up.

if (!searchString || !list)
    throw "ERROR: NearMatchList requires 2 arguments.";

Use try...catch and the message property of the error object, then return the error message instead of throwing an error. I.E.:

/**
 *
 * @customfunction
 */
function myDiv(dividend,divisor){
  var quotient;
  try{
     quotient = dividend / divisor;
  } catch(error) {
     quotient = error.message;
  } finally {
     return quotient;
  }

}

Reference

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/try...catch
• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Error/Error