Receber e tratar dados com funções personalizadas

Uma das maneiras pelas quais as funções personalizadas aprimoram o poder do Excel é receber dados de locais diferentes da pasta de trabalho, como a Web ou um servidor (por meio de WebSockets). Você pode solicitar dados externos por meio de uma API como Fetch ou usando XmlHttpRequest(XHR), uma API Web padrão que emite solicitações HTTP para interagir com servidores.

Principais pontos

• Retornar um JavaScript Promise de funções que buscam dados externos.
• Use funções de streaming para atualizar continuamente os valores das células sem interação do usuário.
• As funções de streaming usam a marca e CustomFunctions.StreamingInvocation o @streaming parâmetro.
• O onCanceled retorno de chamada lida com a limpeza quando uma função é cancelada.
• Os WebSockets permitem atualizações de dados em tempo real de servidores com conexões persistentes.

Funções que retornam os dados de fontes externas

As funções personalizadas que recuperam dados de fontes externas, como APIs REST ou serviços Web, são assíncronas por natureza. O Excel precisa aguardar a chegada dos dados antes de exibir os resultados na célula. Para lidar com isso, sua função deve:

1. Retorne um JavaScript Promise para o Excel.
2. Resolva o Promise com o valor final usando a função de retorno de chamada.

O Excel aguarda automaticamente que a promessa seja resolvida antes de exibir o resultado na célula. Esse padrão funciona para solicitações de dados únicas. Para atualizações contínuas, use funções de streaming.

Exemplo de busca

No exemplo de código a seguir, a webRequest função alcança uma API externa hipotética que rastreia o número de pessoas atualmente na Estação Espacial Internacional. A função retorna um JavaScript Promise e usa fetch para solicitar informações da API hipotética. Os dados resultantes são transformados em JSON e a names propriedade é convertida em uma cadeia de caracteres, que é usada para resolver a promessa.

Ao desenvolver suas próprias funções, considere executar uma ação se a solicitação da Web não for concluída em tempo hábil ou agrupar várias solicitações 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));
      })
  })
}

Exemplo de XHR

No exemplo de código a seguir, a getStarCount função chama a API do GitHub para descobrir a quantidade de estrelas fornecidas ao repositório de um usuário específico. Esta é uma função assíncrona que retorna um JavaScript Promise. Quando os dados são obtidos da chamada na web, é resolvida a promessa que retorna os dados para a célula.

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

Faça uma função de streaming

Funções personalizadas de streaming permitem a saída de dados para células que atualizam repetidamente, sem a necessidade de um usuário explicitamente atualizar coisa alguma. Isso é útil para exibir dados dinâmicos de serviços, como preços de ações, leituras de sensores ou análises em tempo real, como a função no tutorial de funções personalizadas.

Para declarar uma função de streaming, você pode usar uma das duas opções a seguir.

• A @streaming marca JSDoc.
• O CustomFunctions.StreamingInvocation parâmetro de invocação.

As funções de streaming diferem das funções assíncronas regulares, pois podem chamar setResult várias vezes para atualizar o valor da célula continuamente, em vez de retornar um único resultado.

Exemplo básico de streaming

O exemplo a seguir é uma função personalizada que adiciona um número ao resultado a cada segundo. Observe o seguinte sobre este código.

• O Excel exibe cada valor novo automaticamente usando o método setResult.
• O segundo parâmetro de entrada, invocation, não é exibido para os usuários finais no Excel quando eles selecionam a função no menu de preenchimento automático.
• O onCanceled retorno de chamada define a função que é executada quando a função é cancelada.
• O streaming não está necessariamente vinculado a fazer uma solicitação na web. Nesse caso, a função não está fazendo uma solicitação da Web, mas ainda está obtendo dados em intervalos definidos, portanto, requer o uso do 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);
  };
}

Transmitindo dados de um serviço Web

O exemplo a seguir mostra uma função de streaming que busca preços de ações de um serviço Web a 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 uma função

O Excel cancela automaticamente a execução de uma função nas situações a seguir.

• Quando o usuário edita ou exclui uma célula que faz referência à função.
• Quando é alterado um dos argumentos (entradas) para a função. Nesse caso, uma nova chamada de função é acionada, além do cancelamento da antiga.
• Quando o usuário aciona manualmente um recálculo. Nesse caso, uma nova chamada de função é acionada, além do cancelamento da antiga.

A limpeza adequada no retorno de onCanceled chamada é importante para evitar solicitações de rede desnecessárias. Sempre limpe temporizadores, feche conexões e anule solicitações pendentes quando uma função for cancelada. Você também pode considerar a definição de um valor de streaming padrão para lidar com os casos em que uma solicitação for feita, mas você está offline.

Usar um parâmetro de invocação

O parâmetro invocation é o último parâmetro de qualquer função personalizada por padrão. O invocation parâmetro fornece contexto sobre a célula (como seu endereço e conteúdo) e permite que você use o método e onCanceled o setResult evento para definir o que uma função faz quando transmite (setResult) ou é cancelada (onCanceled).

O manipulador de invocação precisa ser do tipo CustomFunctions.StreamingInvocation OR CustomFunctions.CancelableInvocation para processar solicitações da Web.

Consulte o parâmetro Invocation para saber mais sobre outros usos potenciais do invocation argumento e como ele corresponde ao objeto Invocation .

Como receber dados por meio de WebSockets

Em uma função personalizada, é possível usar WebSockets para trocar dados por meio de uma conexão persistente com um servidor. Os WebSockets são úteis para dados em tempo real atualizados com frequência, como tickers financeiros, mensagens de chat ou dados de sensores. Usando WebSockets, sua função personalizada pode abrir uma conexão com um servidor e receber mensagens automaticamente do servidor quando determinados eventos ocorrerem, sem precisar sondar explicitamente o servidor para obter dados.

Exemplo de streaming do WebSocket

O exemplo de código a seguir mostra uma função de streaming que usa WebSockets para receber atualizações em tempo 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();
  };
}

Próximas etapas

• Saiba mais sobre diferentes tipos de parâmetros que as suas funções podem usar.
• Descubra como agrupar várias chamadas de API.

Confira também

• Tutorial de funções personalizadas do Excel
• Opções de parâmetro de funções personalizadas
• Chamadas de função personalizada em lote para um serviço remoto
• Retornar vários resultados da função personalizada
• Valores voláteis nas funções
• Criar metadados JSON para funções personalizadas
• Criar funções personalizadas no Excel