Throwing custom exceptions and error messages in Google Sheets custom function?

Question

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.

Encrimson

Answer 1

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

Psychological

Answer 2

This is a reported problem.

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

Rancourt

Answer 3

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.";

Tantalus

Answer 4

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

Virescence