Получение и обработка данных с помощью пользовательских функций

Одним из способов, которыми пользовательские функции расширяют возможности Excel, является получение данных из расположений, отличных от книги, таких как веб-сайт или сервер (через WebSocket). Внешние данные можно запрашивать с помощью API, например FetchXmlHttpRequestXHR, стандартного веб-API, который отправляет HTTP-запросы для взаимодействия с серверами.

Важно!

Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.

• Office в Интернете
• Office для Windows
  • Подписка на Microsoft 365
  • Розничный бессрочный Office 2016 и более поздних версий
  • Корпоративный бессрочный Office 2021 и более поздних версий
• Office для Mac

Пользовательские функции Excel в настоящее время не поддерживаются в следующих приложениях:

• Office для iPad
• корпоративные бессрочные версии Office 2019 или более ранних версий в Windows

Функции, которые возвращают данные из внешних источников

Если пользовательская функция извлекает данные из внешнего источника, например, сайта, она должна:

1. Возвращает JavaScript Promise в Excel.
2. Разрешите Promise с окончательным значением с помощью функции обратного вызова.

Пример получения данных

В следующем примере webRequest кода функция обращается к гипотетическому внешнему API, который отслеживает количество людей, которые в настоящее время находятся на Международной космической станции. Функция возвращает JavaScript Promise и использует fetch для запроса информации из гипотетического API. Полученные данные преобразуются в JSON, names а свойство преобразуется в строку, которая используется для разрешения обещания.

При разработке собственных функций может потребоваться выполнение действия, если веб-запрос не завершается своевременно. Также можно рассмотреть совмещение нескольких запросов API.

/**
 * Requests the names of the people currently on the International Space Station.
 * Note: This function requests data from a hypothetical URL. In practice, replace the URL with a data source for your scenario.
 * @customfunction
 */
function webRequest() {
  let url = "https://www.contoso.com/NumberOfPeopleInSpace"; // This is a hypothetical URL.
  return new Promise(function (resolve, reject) {
    fetch(url)
      .then(function (response){
        return response.json();
        }
      )
      .then(function (json) {
        resolve(JSON.stringify(json.names));
      })
  })
}

Примечание.

При использовании метода fetch не создаются вложенные обратные вызовы, что в некоторых случаях может быть предпочтительнее, чем использование метода XHR.

Пример XHR

В следующем примере кода функция вызывает API GitHub для обнаружения количества звезд, getStarCount предоставленных репозиторию конкретного пользователя. Это асинхронная функция, которая возвращает JavaScript Promise. При получении данных из веб-вызова выполняется разрешение обещания, которое возвращает данные в ячейку.

/**
 * Gets the star count for a given Github organization or user and repository.
 * @customfunction
 * @param userName string name of organization or user.
 * @param repoName string name of the repository.
 * @return number of stars.
 */
async function getStarCount(userName: string, repoName: string) {

  const url = "https://api.github.com/repos/" + userName + "/" + repoName;

  let xhttp = new XMLHttpRequest();

  return new Promise(function(resolve, reject) {
    xhttp.onreadystatechange = function() {
      if (xhttp.readyState !== 4) return;

      if (xhttp.status == 200) {
        resolve(JSON.parse(xhttp.responseText).watchers_count);
      } else {
        reject({
          status: xhttp.status,

          statusText: xhttp.statusText
        });
      }
    };

    xhttp.open("GET", url, true);

    xhttp.send();
  });
}

Создание функции потоковой передачи

Пользовательские функции потоковой передачи позволяют выводить данные в ячейки, которые повторно обновляются, не требуя от пользователя явно что-либо обновлять. Такие функции (например, функция из руководства по пользовательским функциям) могут быть полезны для проверки данных, обновляемых в реальном времени, из веб-службы.

Чтобы объявить функцию потоковой передачи, можно использовать один из следующих двух параметров.

• Тег @streaming JSDoc.
• Параметр CustomFunctions.StreamingInvocation вызова.

Следующий пример кода — это пользовательская функция, которая добавляет число к результату каждую секунду. Обратите внимание на указанные ниже аспекты этого кода.

• Excel отображает каждое новое значение автоматически с помощью метода setResult.
• Второй параметр ввода, invocation, не отображается для конечных пользователей в Excel, когда они выбирают функцию в меню "Автозаполнение".
• Обратный onCanceled вызов определяет функцию, которая выполняется при отмене функции.
• Потоковая передача не обязательно связана с выполнением веб-запроса. В этом случае функция не выполняет веб-запрос, но по-прежнему получает данные через заданные интервалы, поэтому для нее требуется использовать параметр потоковой передачи invocation .

/**
 * Increments a value once a second.
 * @customfunction INC increment
 * @param {number} incrementBy Amount to increment
 * @param {CustomFunctions.StreamingInvocation<number>} invocation
 */
function increment(incrementBy, invocation) {
  let result = 0;
  const timer = setInterval(() => {
    result += incrementBy;
    invocation.setResult(result);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Примечание.

Пример возврата динамического массива разлива из функции потоковой передачи см. в разделе Возврат нескольких результатов из пользовательской функции: примеры кода.

Отмена функции

Excel отменяет выполнение функции в следующих ситуациях.

• Когда пользователь редактирует или удаляет ячейку, ссылающуюся на функцию.
• Когда изменяется один из аргументов (входных параметров) функции. В этом случае после отмены выполняется новый вызов функции.
• Когда пользователь вручную вызывает пересчет. В этом случае после отмены выполняется новый вызов функции.

Также можно настроить стандартное значение потоковой передачи, чтобы обрабатывать случаи выполнения запроса, когда вы находитесь в автономном режиме.

Примечание.

Существует также категория функций, называемых отменяемыми функциями @cancelable , которые используют тег JSDoc. Отменяемые функции позволяют завершить веб-запрос в середине запроса.

Функция потоковой передачи @cancelable не может использовать тег, но функции потоковой передачи могут включать функцию обратного onCanceled вызова. Только асинхронные пользовательские функции, возвращающие одно значение, могут использовать @cancelable тег JSDoc. Дополнительные сведения о теге см. в @cancelable этой Autogenerate JSON metadata: @cancelable статье.

Использование параметра вызова

Параметр invocation является по умолчанию последним в любой пользовательской функции. Параметр invocation предоставляет контекст ячейки (например, ее адрес и содержимое) и позволяет использовать setResult метод и onCanceled событие, чтобы определить, что функция делает при потоковой передаче (setResult) или отмене (onCanceled).

Обработчик вызова должен иметь тип CustomFunctions.StreamingInvocation или CustomFunctions.CancelableInvocation для обработки веб-запросов.

См. раздел Параметр вызова , чтобы узнать о других потенциальных возможностях использования аргумента invocation и о том, как он соответствует объекту Вызова .

Получение данных через WebSockets

В пределах пользовательской функции можно использовать WebSockets для обмена данными через постоянное соединение с сервером. С помощью WebSocket пользовательская функция может открыть соединение с сервером, а затем автоматически получать сообщения от сервера при возникновении определенных событий без необходимости явного опроса данных на сервере.

Пример WebSockets

Следующий примера кода устанавливает соединение WebSocket, а затем заносит в журнал каждое входящее сообщение от сервера.

let ws = new WebSocket('wss://bundles.office.com');

ws.onmessage(message) {
    console.log(`Received: ${message}`);
}

ws.onerror(error){
    console.err(`Failed: ${error}`);
}

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

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

См. также

• Пересчитываемые значения в функциях
• Создание метаданных JSON для пользовательских функций
• Создание метаданных JSON вручную для пользовательских функций
• Создание пользовательских функций в Excel
• Руководство по пользовательским функциям в Excel