Поделиться через

Обработка и возврат ошибок из пользовательской функции

  • Статья

Если что-то пойдет не так во время выполнения пользовательской функции, верните сообщение об ошибке, информирующее пользователя. Если у вас есть определенные требования к параметрам, например только положительные числа, проверьте параметры и вызовите ошибку, если они не верны. Можно также использовать блок try...catch, чтобы отслеживать любые ошибки, возникающие при выполнении пользовательской функции.

Обнаружение и возвращение ошибки

Давайте рассмотрим случай, когда необходимо убедиться, что параметр zip code имеет правильный формат для работы пользовательской функции. В следующей пользовательской функции используется регулярное выражение для проверки почтового индекса. Если формат почтового индекса правильный, он будет искать город с помощью другой функции и возвращать значение. Если формат недопустим, функция возвращает ошибку #VALUE! в ячейку.

/**
* Gets a city name for the given U.S. zip code.
* @customfunction
* @param {string} zipCode
* @returns The city of the zip code.
*/
function getCity(zipCode: string): string {
  let isValidZip = /(^\d{5}$)|(^\d{5}-\d{4}$)/.test(zipCode);
  if (isValidZip) return cityLookup(zipCode);
  let error = new CustomFunctions.Error(CustomFunctions.ErrorCode.invalidValue, "Please provide a valid U.S. zip code.");
  throw error;
}

Объект CustomFunctions.Error

Объект CustomFunctions.Error используется для возврата ошибки обратно в ячейку. При создании объекта укажите, какую ошибку вы хотите использовать, выбрав одно из следующих ErrorCode значений перечисления.

Значение перечисления ErrorCode Значение ячейки Excel Описание
divisionByZero #DIV/0 Функция пытается разделить на ноль.
invalidName #NAME? В имени функции есть опечатка. Обратите внимание, что эта ошибка поддерживается как ошибка ввода пользовательской функции, но не как ошибка вывода пользовательской функции.
invalidNumber #NUM! Существует проблема с числом в формуле.
invalidReference #REF! Функция ссылается на недопустимую ячейку. Обратите внимание, что эта ошибка поддерживается как ошибка ввода пользовательской функции, но не как ошибка вывода пользовательской функции.
invalidValue #VALUE! Значение в формуле имеет неправильный тип.
notAvailable #N/A Функция или служба недоступны.
nullReference #NULL! Диапазоны в формуле не пересекаются.

В следующем примере кода показано, как создать и вернуть ошибку для неверного числа (#NUM!).

let error = new CustomFunctions.Error(CustomFunctions.ErrorCode.invalidNumber);
throw error;

Ошибки #VALUE! и #N/A также поддерживают пользовательские сообщения об ошибках. Пользовательские сообщения об ошибках отображаются в меню индикатора ошибки, доступ к которому осуществляется путем наведении указателя мыши на флаг ошибки в каждой ячейке с ошибкой. В следующем примере показано, как вернуть пользовательское сообщение об ошибке с ошибкой #VALUE! .

// You can only return a custom error message with the #VALUE! and #N/A errors.
let error = new CustomFunctions.Error(CustomFunctions.ErrorCode.invalidValue, "The parameter can only contain lowercase characters.");
throw error;

Обработка ошибок при работе с динамическими массивами

Помимо возврата одной ошибки, пользовательская функция может выводить динамический массив, содержащий ошибку. Например, пользовательская функция может выводить массив [1],[#NUM!],[3]. В следующем примере кода показано, как ввести три параметра в настраиваемую функцию, заменить один из входных параметров ошибкой #NUM! , а затем вернуть двухмерный массив с результатами обработки каждого входного параметра.

/**
* Returns the #NUM! error as part of a 2-dimensional array.
* @customfunction
* @param {number} first First parameter.
* @param {number} second Second parameter.
* @param {number} third Third parameter.
* @returns {number[][]} Three results, as a 2-dimensional array.
*/
function returnInvalidNumberError(first, second, third) {
  // Use the `CustomFunctions.Error` object to retrieve an invalid number error.
  const error = new CustomFunctions.Error(
    CustomFunctions.ErrorCode.invalidNumber, // Corresponds to the #NUM! error in the Excel UI.
  );

  // Enter logic that processes the first, second, and third input parameters.
  // Imagine that the second calculation results in an invalid number error. 
  const firstResult = first;
  const secondResult =  error;
  const thirdResult = third;

  // Return the results of the first and third parameter calculations and a #NUM! error in place of the second result. 
  return [[firstResult], [secondResult], [thirdResult]];
}

Ошибки в качестве входных данных пользовательской функции

Пользовательская функция может оценить, даже если входной диапазон содержит ошибку. Например, пользовательская функция может принимать диапазон A2:A7 в качестве входных данных, даже если A6:A7 содержит ошибку.

Для обработки входных данных, содержащих ошибки, настраиваемая функция должна иметь свойство allowErrorForDataTypeAny метаданных JSON, которое имеет значение true. Дополнительные сведения см. в статье Создание метаданных JSON вручную для пользовательских функций .

Важно!

Свойство allowErrorForDataTypeAny можно использовать только с созданными вручную метаданными JSON. Это свойство не работает с автоматически созданным процессом метаданных JSON.

Использование try...catch блоков

Как правило, используйте try...catch блоки в пользовательской функции, чтобы перехватывать возможные ошибки. Если вы не обрабатываете исключения в коде, они будут возвращены в Excel. По умолчанию Excel возвращает необработанных #VALUE! ошибок или исключений.

В следующем примере кода пользовательская функция создает запрос fetch в службу REST. Возможно, что вызов завершится сбоем (например, если служба REST возвращает ошибку или не работает сеть). В этом случае пользовательская функция вернется #N/A , чтобы указать, что веб-вызов завершился сбоем.

/**
 * Gets a comment from the hypothetical contoso.com/comments API.
 * @customfunction
 * @param {number} commentID ID of a comment.
 */
function getComment(commentID) {
  let url = "https://www.contoso.com/comments/" + commentID;
  return fetch(url)
    .then(function (data) {
      return data.json();
    })
    .then(function (json) {
      return json.body;
    })
    .catch(function (error) {
      throw new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable);
    })
}

Дальнейшие действия

Узнайте, как устранять проблемы с пользовательскими функциями.

Дополнительные ресурсы