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

Question

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.

11

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

Источник

user4100635 02 окт '14 в 00:09

4 ответа

Другие вопросы по тегам javascript google-sheets google-apps-script google-spreadsheet try-catch custom-function google-sheets-custom-function

user5372400 30 мар '18 в 14:16 2018-03-30 14:16 · Answer 1 · 2018-03-30 14:16

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

8

Источник

user5372400 30 мар '18 в 14:16

user1677912 02 июн '15 в 18:13 2015-06-02 18:13 · Answer 2 · 2015-06-02 18:13

Это сообщенная проблема.

Посетите выпуск 4422, отметьте его, чтобы голосовать и за обновления.

3

Источник

user1677912 02 июн '15 в 18:13

user8111596 09 мар '23 в 16:36 2023-03-09 16:36 · Answer 3 · 2023-03-09 16:36

Добавляю к комментарию выше по поводу броска. Это хорошо работает, потому что в листе Google оно переносит внутреннее сообщение во всплывающее окно.

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

1

Источник

user8111596 09 мар '23 в 16:36

user1595451 14 июн '20 в 03:02 2020-06-14 03:02 · Answer 4 · 2020-06-14 03:02

Использовать try...catchи свойство message объекта ошибки, а затем вернуть сообщение об ошибке вместо выдачи ошибки. IE:

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

}

Ссылка