Share via

Receber e tratar dados com funções personalizadas

  • Artigo

Uma das maneiras pelas quais as funções personalizadas aprimoram o poder do Excel é recebendo 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.

Importante

Observe que as funções personalizadas do Excel estão disponíveis nas plataformas a seguir.

  • Office na Web
  • Office no Windows
    • Assinatura do Microsoft 365
    • varejo perpétuo Office 2016 e posterior
    • Office 2021 perpétuo licenciado por volume e posterior
  • Office no Mac

No momento, as funções personalizadas do Excel não têm suporte no seguinte:

  • Office no iPad
  • versões perpétuas licenciadas por volume do Office 2019 ou anteriores no Windows

GIF de uma função personalizada que transmite o tempo de uma API.

Funções que retornam os dados de fontes externas

Se uma função personalizada recupera dados de uma fonte externa como na web, ela deve:

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

Exemplo de busca

No exemplo de código a seguir, a webRequest função alcança uma hipotética API externa 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 resolve a promessa.

Ao desenvolver suas próprias funções, talvez você queira executar uma ação caso a solicitação da Web não tenha sido concluída de maneira oportuna ou considere o envio de várias solicitaçõesda 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));
      })
  })
}

Observação

Usar fetch evita retornos de chamada aninhados e pode ser preferível do XHR em alguns casos.

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 determinado usuário. Essa é uma função assíncrona que retorna um JavaScript Promise. Quando os dados são obtidos da chamada Web, a promessa é resolvida, 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 pode ser útil para verificar dados ativos de um serviço online, 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.

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 à realização de uma solicitação da Web. Nesse caso, a função não está fazendo uma solicitação da Web, mas ainda está recebendo 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);
  };
}

Observação

Para obter um exemplo de como retornar uma matriz de derramamento dinâmica de uma função de streaming, confira Retornar vários resultados de sua função personalizada: exemplos de código.

Cancelar uma função

O Excel cancela 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 é disparada, seguindo o cancelamento.
  • Quando o usuário aciona manualmente um recálculo. Nesse caso, uma nova chamada de função é disparada, seguindo o cancelamento.

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.

Observação

Há também uma categoria de funções chamadas funções canceláveis que usam a @cancelable marca JSDoc. Funções canceláveis permitem que uma solicitação Da Web seja encerrada no meio da solicitação.

Uma função de streaming não pode usar a marca, mas as @cancelable funções de streaming podem incluir uma função de onCanceled retorno de chamada. Somente funções personalizadas assíncronas que retornam um valor podem usar a @cancelable marca JSDoc. Confira Autogenerate JSON metadata: @cancelable para saber mais sobre a @cancelable marca.

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 ela transmite (setResult) ou é cancelada (onCanceled).

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

Consulte Parâmetro invocação para saber mais sobre outros usos potenciais do invocation argumento e como ele corresponde ao objeto Invocação .

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. Usando WebSockets, sua função personalizada pode abrir uma conexão com um servidor e, em seguida, receber mensagens automaticamente do servidor quando determinados eventos ocorrem, sem precisar sondar explicitamente o servidor em busca de dados.

Exemplo de WebSockets

O código de exemplo a seguir estabelece uma conexão WebSocket e registra cada mensagem de entrada do servidor.

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

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

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

Próximas etapas

Confira também