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.
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.
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.
if (some_condition)
{
// will end execution with error
throw 'Error. My custom error description.';
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With