A função personalizada do lote exige um serviço remoto

Se as suas funções personalizadas chamarem um serviço remoto, você poderá usar um padrão de envio em lotes para reduzir o número de chamadas de rede para o serviço remoto. Para reduzir a idas e voltas na rede, você reúne todas as chamadas em uma única chamada para o serviço da Web. Isso é ideal quando a planilha é recalculada.

Por exemplo, se alguém usou sua função personalizada em 100 células em uma planilha e depois recalculou a planilha, sua função personalizada seria executada 100 vezes e faria 100 chamadas de rede. Usando um padrão de envio em lotes, as chamadas podem ser combinadas para fazer todos os 100 cálculos em uma única chamada de rede.

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

Ver o exemplo concluído

Para exibir o exemplo concluído, siga este artigo e cole os exemplos de código em seu próprio projeto. Por exemplo, para criar um novo projeto de função personalizada para TypeScript, use o gerador Yeoman para Suplementos do Office e adicione todo o código deste artigo ao projeto. Execute o código e experimente-o.

Como alternativa, baixe ou exiba o projeto de exemplo completo no padrão de lote de função personalizada. Se você quiser ver o código inteiro antes de ler mais, dê uma olhada no arquivo de script.

Crie o padrão de envio em lotes deste artigo

Para configurar o envio em lotes para suas funções personalizadas, você precisará escrever três seções principais de código.

1. Uma operação push para adicionar uma nova operação ao lote de chamadas sempre que o Excel chamar sua função personalizada.
2. Uma função para fazer a solicitação remota quando o lote estiver pronto.
3. Código do servidor para responder à solicitação em lote, calcular todos os resultados da operação e retornar os valores.

Nas seções a seguir, você aprenderá a construir o código um exemplo de cada vez. É recomendável criar um novo projeto de funções personalizadas usando o gerador Yeoman para gerador de suplementos do Office . Para criar um novo projeto, consulte Introdução ao desenvolvimento de funções personalizadas do Excel. Você pode usar TypeScript ou JavaScript.

Agrupe cada chamada à sua função personalizada

Suas funções personalizadas funcionam chamando um serviço remoto para executar a operação e calcular o resultado de que precisam. Isso fornece uma maneira de armazenar cada operação solicitada em um lote. Mais tarde, você verá como criar uma função _pushOperation para agrupar as operações. Primeiro, dê uma olhada no exemplo de código a seguir para ver como chamar _pushOperation de sua função personalizada.

No código a seguir, a função personalizada executa a divisão, mas depende de um serviço remoto para fazer o cálculo real. Ela chama _pushOperation para reunir em lote a operação a outras operações para o serviço remoto. Nomeia a operação div2. Você pode usar qualquer esquema de nomenclatura desejado para operações, desde que o serviço remoto também esteja usando o mesmo esquema (mais informações sobre o serviço remoto posteriormente). Além disso, os argumentos que o serviço remoto precisará para executar a operação são passados.

Adicionar a função personalizada div2

Adicione o código a seguir ao arquivofunctions.js ou functions.ts (dependendo se você usou JavaScript ou TypeScript).

/**
 * Divides two numbers using batching
 * @CustomFunction
 * @param dividend The number being divided
 * @param divisor The number the dividend is divided by
 * @returns The result of dividing the two numbers
 */
function div2(dividend, divisor) {
  return _pushOperation("div2", [dividend, divisor]);
}

Adicionar variáveis globais para acompanhar solicitações em lote

Em seguida, adicione duas variáveis globais ao arquivofunctions.js ou functions.ts . _isBatchedRequestScheduled é importante posteriormente para o tempo de chamadas em lote para o serviço remoto.

let _batch = [];
let _isBatchedRequestScheduled = false;

Adicionar a _pushOperation função

Quando o Excel chama sua função personalizada, você precisa empurrar a operação para a matriz do lote. O código de função _pushOperation a seguir mostra como adicionar uma nova operação de uma função personalizada. Ele cria uma nova entrada de lote, cria uma nova promessa para resolver ou rejeitar a operação e envia a entrada para a matriz de lotes.

Esse código também verifica se um lote está programado. Neste exemplo, cada lote está programado para ser executado a cada 100 ms. Você pode ajustar esse valor conforme necessário. Valores mais altos resultam em lotes maiores sendo enviados ao serviço remoto e um tempo de espera maior para o usuário ver os resultados. Valores mais baixos tendem a enviar mais lotes para o serviço remoto, mas com um tempo de resposta rápido para os usuários.

A função cria um objeto invocationEntry que contém o nome da cadeia de caracteres da qual a operação será executada. Por exemplo, se você tivesse duas funções personalizadas nomeadas multiply e divide, você poderia reutilizá-las como nomes de operações em suas entradas de lote. args contém os argumentos que foram passados para sua função personalizada do Excel. E, por fim, resolve ou reject os métodos armazenam uma promessa que contém as informações que o serviço remoto retorna.

Adicione o código a seguir ao arquivofunctions.js ou functions.ts .

// This function encloses your custom functions as individual entries,
// which have some additional properties so you can keep track of whether or not
// a request has been resolved or rejected.
function _pushOperation(op, args) {
  // Create an entry for your custom function.
  console.log("pushOperation");
  const invocationEntry = {
    operation: op, // e.g., sum
    args: args,
    resolve: undefined,
    reject: undefined,
  };

  // Create a unique promise for this invocation,
  // and save its resolve and reject functions into the invocation entry.
  const promise = new Promise((resolve, reject) => {
    invocationEntry.resolve = resolve;
    invocationEntry.reject = reject;
  });

  // Push the invocation entry into the next batch.
  _batch.push(invocationEntry);

  // If a remote request hasn't been scheduled yet,
  // schedule it after a certain timeout, e.g., 100 ms.
  if (!_isBatchedRequestScheduled) {
    console.log("schedule remote request");
    _isBatchedRequestScheduled = true;
    setTimeout(_makeRemoteRequest, 100);
  }

  // Return the promise for this invocation.
  return promise;
}

Faça o pedido remoto

O objetivo da função _makeRemoteRequest é passar o lote de operações para o serviço remoto e, em seguida, retornar os resultados para cada função personalizada. Primeiro, ela cria uma cópia da matriz de lotes. Isso permite que chamadas de função personalizadas simultâneas do Excel iniciem imediatamente o envio em lote em uma nova matriz. A cópia é então transformada em uma matriz mais simples que não contém as informações de promessa. Não faria sentido passar as promessas para um serviço remoto, uma vez que não funcionariam. _makeRemoteRequest irá rejeitar ou resolver cada promessa com base no que o serviço remoto retornar.

Adicione o código a seguir ao arquivofunctions.js ou functions.ts .

// This is a private helper function, used only within your custom function add-in.
// You wouldn't call _makeRemoteRequest in Excel, for example.
// This function makes a request for remote processing of the whole batch,
// and matches the response batch to the request batch.
function _makeRemoteRequest() {
  // Copy the shared batch and allow the building of a new batch while you are waiting for a response.
  // Note the use of "splice" rather than "slice", which will modify the original _batch array
  // to empty it out.
  try{
  console.log("makeRemoteRequest");
  const batchCopy = _batch.splice(0, _batch.length);
  _isBatchedRequestScheduled = false;

  // Build a simpler request batch that only contains the arguments for each invocation.
  const requestBatch = batchCopy.map((item) => {
    return { operation: item.operation, args: item.args };
  });
  console.log("makeRemoteRequest2");
  // Make the remote request.
  _fetchFromRemoteService(requestBatch)
    .then((responseBatch) => {
      console.log("responseBatch in fetchFromRemoteService");
      // Match each value from the response batch to its corresponding invocation entry from the request batch,
      // and resolve the invocation promise with its corresponding response value.
      responseBatch.forEach((response, index) => {
        if (response.error) {
          batchCopy[index].reject(new Error(response.error));
          console.log("rejecting promise");
        } else {
          console.log("fulfilling promise");
          console.log(response);

          batchCopy[index].resolve(response.result);
        }
      });
    });
    console.log("makeRemoteRequest3");
  } catch (error) {
    console.log("error name:" + error.name);
    console.log("error message:" + error.message);
    console.log(error);
  }
}

Modifique _makeRemoteRequest para sua própria solução

A função _makeRemoteRequest chama _fetchFromRemoteService, que, como você verá mais adiante, é apenas uma simulação representando o serviço remoto. Isso facilita estudar e executar o código neste artigo. Mas quando você deseja usar esse código para um serviço remoto real, você deve fazer as alterações a seguir.

• Decida como serializar as operações em lote pela rede. Por exemplo, você pode querer colocar a matriz em um corpo JSON.
• Em vez de chamar _fetchFromRemoteService, você precisa fazer a chamada de rede real para o serviço remoto passando o lote de operações.

Processar a chamada em lote no serviço remoto

A última etapa é manipular a chamada em lote no serviço remoto. O exemplo de código a seguir mostra a função _fetchFromRemoteService. Essa função descompacta cada operação, executa a operação especificada e retorna os resultados. Para fins de aprendizado neste artigo, a função _fetchFromRemoteService foi projetada para ser executada em seu suplemento da Web e simular um serviço remoto. Você pode adicionar esse código ao seu arquivofunctions.js ou functions.ts para que possa estudar e executar todo o código neste artigo sem precisar configurar um serviço remoto real.

Adicione o código a seguir ao arquivofunctions.js ou functions.ts .

// This function simulates the work of a remote service. Because each service
// differs, you will need to modify this function appropriately to work with the service you are using. 
// This function takes a batch of argument sets and returns a promise that may contain a batch of values.
// NOTE: When implementing this function on a server, also apply an appropriate authentication mechanism
//       to ensure only the correct callers can access it.
async function _fetchFromRemoteService(requestBatch) {
  // Simulate a slow network request to the server.
  console.log("_fetchFromRemoteService");
  await pause(1000);
  console.log("postpause");
  return requestBatch.map((request) => {
    console.log("requestBatch server side");
    const { operation, args } = request;

    try {
      if (operation === "div2") {
        // Divide the first argument by the second argument.
        return {
          result: args[0] / args[1]
        };
      } else if (operation === "mul2") {
        // Multiply the arguments for the given entry.
        const myResult = args[0] * args[1];
        console.log(myResult);
        return {
          result: myResult
        };
      } else {
        return {
          error: `Operation not supported: ${operation}`
        };
      }
    } catch (error) {
      return {
        error: `Operation failed: ${operation}`
      };
    }
  });
}

function pause(ms) {
  console.log("pause");
  return new Promise((resolve) => setTimeout(resolve, ms));
}

Modifique _fetchFromRemoteService para o seu serviço remoto ao vivo

Para modificar a _fetchFromRemoteService função a ser executada no serviço remoto ao vivo, faça as seguintes alterações.

• Dependendo da plataforma do servidor (Node.js ou outros), mapeie a chamada de rede do cliente para essa função.
• Remova a função pause que simula a latência da rede como parte da simulação.
• Modifique a declaração da função para trabalhar com o parâmetro transmitido se o parâmetro for alterado para fins de rede. Por exemplo, em vez de uma matriz, pode ser um corpo JSON de operações em lote a serem processadas.
• Modifique a função para executar as operações (ou chame as funções que executam as operações).
• Aplique um mecanismo de autenticação apropriado. Certifique-se de que apenas os autores de chamada corretos possam acessar a função.
• Coloque o código no serviço remoto.

Próximas etapas

Saiba mais sobre os vários parâmetros que você pode usar nas suas funções personalizadas. Ou, reveja as noções básicas sobre como fazer uma chamada na Web através de um função personalizada.

Confira também

• Valores voláteis nas funções
• Criar funções personalizadas no Excel
• Tutorial de funções personalizadas do Excel