Recibir y controlar datos con funciones personalizadas

Una de las maneras en que las funciones personalizadas mejoran la eficacia de Excel es recibir datos de ubicaciones distintas del libro, como la web o un servidor (a través de WebSockets). Puede solicitar datos externos a través de una API como Fetch o mediante XmlHttpRequest(XHR), una API web estándar que emite solicitudes HTTP para interactuar con los servidores.

Puntos clave

• Devuelve un JavaScript Promise de funciones que capturan datos externos.
• Use funciones de streaming para actualizar continuamente los valores de celda sin interacción del usuario.
• Las funciones de streaming usan la etiqueta y CustomFunctions.StreamingInvocation el @streaming parámetro .
• La onCanceled devolución de llamada controla la limpieza cuando se cancela una función.
• Los WebSockets permiten actualizaciones de datos en tiempo real desde servidores con conexiones persistentes.

Funciones que devuelven datos de orígenes externos

Las funciones personalizadas que recuperan datos de orígenes externos, como las API REST o los servicios web, son asincrónicas por naturaleza. Excel debe esperar a que lleguen los datos antes de mostrar los resultados en la celda. Para controlar esto, la función debe:

1. Devolver un JavaScript Promise a Excel.
2. Promise Resuelva con el valor final mediante la función de devolución de llamada.

Excel espera automáticamente a que se resuelva la promesa antes de mostrar el resultado en la celda. Este patrón funciona para solicitudes de datos únicas. Para las actualizaciones continuas, use funciones de streaming en su lugar.

Ejemplo con fetch

En el ejemplo de código siguiente, la webRequest función llega a una hipotética API externa que realiza el seguimiento del número de personas que se encuentran actualmente en la Estación Espacial Internacional. La función devuelve un JavaScript Promise y usa fetch para solicitar información de la hipotética API. Los datos resultantes se transforman en JSON y la names propiedad se convierte en una cadena, que se usa para resolver la promesa.

Al desarrollar sus propias funciones, considere la posibilidad de realizar una acción si la solicitud web no se completa de forma oportuna o procesa por lotes varias solicitudes de 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));
      })
  })
}

Ejemplo de XHR

En el ejemplo de código siguiente, la getStarCount función llama a la API de Github para detectar la cantidad de estrellas que se proporcionan al repositorio de un usuario determinado. Se trata de una función asincrónica que devuelve un javascript Promise. Cuando se obtienen datos de la llamada web, se resuelve la promesa que devuelve los datos a la celda.

/**
 * 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();
  });
}

Crear una función de streaming

Las funciones personalizadas de streaming le permiten enviar datos a celdas que se actualizan continuamente, sin necesidad de que un usuario actualice nada explícitamente. Esto resulta útil para mostrar datos en directo de servicios, como precios de acciones, lecturas de sensores o análisis en tiempo real, como la función del tutorial de funciones personalizadas.

Para declarar una función de streaming, puede usar cualquiera de las dos opciones siguientes.

• Etiqueta @streaming JSDoc.
• Parámetro CustomFunctions.StreamingInvocation de invocación.

Las funciones de streaming difieren de las funciones asincrónicas normales, ya que pueden llamar setResult varias veces para actualizar el valor de celda continuamente, en lugar de devolver un único resultado.

Ejemplo de streaming básico

El siguiente ejemplo es una función personalizada que agrega un número al resultado cada segundo. Tenga en cuenta lo siguiente sobre este código.

• Excel muestra cada valor nuevo automáticamente con el método setResult.
• El segundo parámetro de entrada, invocation, no se muestra a los usuarios finales en Excel cuando seleccionan la función en el menú autocompletar.
• La onCanceled devolución de llamada define la función que se ejecuta cuando se cancela la función.
• El streaming no está necesariamente asociado a la realización de una solicitud web. En este caso, la función no realiza una solicitud web, pero sigue obteniendo datos a intervalos establecidos, por lo que requiere el uso del parámetro de streaming 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);
  };
}

Transmisión de datos desde un servicio web

En el ejemplo siguiente se muestra una función de streaming que captura los precios de las acciones de un servicio web cada 10 segundos.

/**
 * Streams stock price updates.
 * @customfunction
 * @param {string} ticker Stock ticker symbol.
 * @param {CustomFunctions.StreamingInvocation<number>} invocation
 */
function stockPrice(ticker, invocation) {
  const updateInterval = 10000; // Update every 10 seconds.

  const timer = setInterval(() => {
    // Replace with your actual API endpoint.
    fetch(`https://api.example.com/stock/${ticker}`)
      .then(response => response.json())
      .then(data => {
        invocation.setResult(data.price);
      })
      .catch(error => {
        // Return the #N/A error if stock price is unavailable.
        invocation.setResult(
          new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable)
        );
      });
  }, updateInterval);

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

Cancelar una función

Excel cancela automáticamente la ejecución de una función en las situaciones siguientes.

• Cuando el usuario edita o elimina una celda que hace referencia a la función.
• Cuando cambia uno de los argumentos (entradas) para la función. En este caso, se activa una nueva llamada de función después de la cancelación.
• Cuando el usuario desencadena manualmente un nuevo cálculo. En este caso, se activa una nueva llamada de función después de la cancelación.

La limpieza adecuada en la devolución de onCanceled llamada es importante para evitar solicitudes de red innecesarias. Borre siempre los temporizadores, cierre las conexiones y anule las solicitudes pendientes cuando se cancele una función. También puede considerar la posibilidad de configurar un valor de streaming predeterminado para administrar casos en los que se realice una solicitud sin que esté conectado.

Uso de un parámetro de invocación

El parámetro invocation es el último de cualquier función personalizada de forma predeterminada. El invocation parámetro proporciona contexto sobre la celda (como su dirección y contenido) y permite usar el método y onCanceled el setResult evento para definir lo que hace una función cuando transmite (setResult) o se cancela (onCanceled).

El controlador de invocación debe ser de tipo CustomFunctions.StreamingInvocation o CustomFunctions.CancelableInvocation procesar solicitudes web.

Consulte Parámetro Invocation para obtener información sobre otros posibles usos del invocation argumento y cómo se corresponde con el objeto Invocation .

Recibir datos a través de WebSockets

En una función personalizada, puede usar WebSockets para intercambiar datos a través de una conexión persistente con un servidor. Los WebSockets son útiles para los datos en tiempo real que se actualizan con frecuencia, como los tickers financieros, los mensajes de chat o los datos del sensor. Con WebSockets, la función personalizada puede abrir una conexión con un servidor y, a continuación, recibir automáticamente mensajes del servidor cuando se producen determinados eventos, sin tener que sondear explícitamente los datos del servidor.

Ejemplo de streaming de WebSocket

En el ejemplo de código siguiente se muestra una función de streaming que usa WebSockets para recibir actualizaciones en tiempo real.

/**
 * Streams real-time data via WebSocket.
 * @customfunction
 * @param {string} symbol Data symbol to monitor.
 * @param {CustomFunctions.StreamingInvocation<string>} invocation
 */
function streamWebSocket(symbol, invocation) {
  const ws = new WebSocket('wss://example.com/data');

  ws.onopen = () => {
    // Subscribe to updates for the specified symbol.
    ws.send(JSON.stringify({ subscribe: symbol }));
  };

  ws.onmessage = (event) => {
    const data = JSON.parse(event.data);
    invocation.setResult(data.value);
  };

  ws.onerror = (error) => {
    // Return the #N/A error if connection fails.
    invocation.setResult(
      new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable)
    );
  };

  invocation.onCanceled = () => {
    ws.close();
  };
}

Siguientes pasos

• Obtenga información sobre tipos diferentes de parámetros que pueden usar las funciones.
• Descubrir cómo hacer varias llamadas de API por lotes.

Vea también

• Tutorial de las funciones personalizadas de Excel
• Opciones de parámetros de funciones personalizadas
• Llamadas de función personalizada de Batch para un servicio remoto
• Devolver varios resultados de la función personalizada
• Valores volátiles de funciones
• Crear metadatos JSON de funciones personalizadas
• Crear funciones personalizadas en Excel