Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 708
Claude Rubinson Avatar asked Oct 02 '14 00:10

Claude Rubinson


People also ask

Is error function in Google Sheets?

The ISERROR function in Google Sheets can be used to check whether a value in a specific cell is an error. This function returns TRUE if the value in cell A1 is an error, otherwise it returns FALSE.

How do I hide error messages in Google Sheets?

One great way to hide errors is by using the IFERROR function. The IFERROR function will allow you to return any value you want if there is an error value, or you can make it so that errors show up as blanks.


1 Answers

if (some_condition)
{
  // will end execution with error    
  throw 'Error. My custom error description.';
}
like image 168
Max Makhrov Avatar answered Oct 18 '22 08:10

Max Makhrov