Receber e tratar dados com funções personalizadas
Uma das formas pelas quais as funções personalizadas melhoram o poder do Excel é ao receber dados de localizações diferentes do livro, como a Web ou um servidor (através de WebSockets). Pode pedir dados externos através de uma API como Fetch ou através XmlHttpRequest de (XHR), uma API Web padrão que emite pedidos 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
- revenda perpétua do Office 2016 e posterior
- Office 2021 perpétuo licenciado em volume e posterior
- Office no Mac
As funções personalizadas do Excel não são atualmente suportadas no seguinte:
- Office no iPad
- versões perpétuas licenciadas em volume do Office 2019 ou anterior no Windows
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:
- Devolver um JavaScript
Promiseao Excel. - Resolva o
Promisecom o valor final com a função de chamada de retorno.
Exemplo de busca
No seguinte exemplo de código, a webRequest função alcança uma hipotética API externa que monitoriza o número de pessoas atualmente na Estação Espacial Internacional. A função devolve um JavaScript Promise e utiliza fetch para pedir informações da hipotética API. Os dados resultantes são transformados em JSON e a names propriedade é convertida numa cadeia, que é utilizada para resolver 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 seguinte, a getStarCount função chama a API do Github para descobrir a quantidade de estrelas dadas ao repositório de um utilizador específico. Esta é uma função assíncrona que devolve um JavaScript Promise. Quando os dados são obtidos a partir da chamada Web, a promessa é resolvida, o que devolve os dados à 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 transmissão em fluxo, pode utilizar uma das duas opções seguintes.
- A
@streamingetiqueta JSDoc. - O
CustomFunctions.StreamingInvocationparâ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. - A
onCanceledchamada de retorno define a função que é executada quando a função é cancelada. - A transmissão em fluxo não está necessariamente associada a fazer um pedido Web. Neste caso, a função não está a fazer um pedido Web, mas ainda está a obter dados em intervalos definidos, pelo que requer a utilização do parâmetro de transmissão em fluxo
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 devolver uma matriz de transposição dinâmica de uma função de transmissão em fluxo, veja Devolver múltiplos resultados da sua função personalizada: Exemplos de código.
Cancelar uma função
O Excel cancela a execução de uma função nas seguintes situações.
- 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
Existe também uma categoria de funções denominada funções canceláveis que utilizam a @cancelable etiqueta JSDoc. As funções canceláveis permitem que um pedido Web seja terminado a meio do pedido.
Uma função de transmissão em fluxo não pode utilizar a etiqueta, mas as @cancelable funções de transmissão em fluxo podem incluir uma função onCanceled de chamada de retorno. Apenas as funções personalizadas assíncronas que devolvem um valor podem utilizar a @cancelable etiqueta JSDoc. Veja Autogenerate JSON metadata: @cancelable para saber mais sobre a @cancelable etiqueta.
Utilizar 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 dá contexto sobre a célula (como o respetivo endereço e conteúdo) e permite-lhe utilizar o método e onCanceled o setResult evento para definir o que uma função faz quando transmite (setResult) ou é cancelada (onCanceled).
O processador de invocação tem de ser do tipo CustomFunctions.StreamingInvocation ou CustomFunctions.CancelableInvocation processar pedidos Web.
Veja Parâmetro de invocação para saber mais sobre outras potenciais utilizações do invocation argumento e como 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. Ao utilizar WebSockets, a função personalizada pode abrir uma ligação com um servidor e, em seguida, receber automaticamente mensagens do servidor quando ocorrem determinados eventos, sem ter de consultar explicitamente os dados do servidor.
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
- Saiba mais sobre diferentes tipos de parâmetros que as suas funções podem usar.
- Descubra como agrupar várias chamadas de API.