A função personalizada do Batch chama 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
  • revenda perpétua do Office 2016 e posterior
  • Office 2021 perpétuas licenciadas em volume e posteriores
• 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

Observação

O manifesto unificado do Microsoft 365 não suporta atualmente projetos de funções personalizadas. Tem de utilizar o manifesto apenas de suplemento para projetos de funções personalizadas. Para obter mais informações, veja Manifesto de Suplementos do Office.

Ver o exemplo concluído

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

Em alternativa, transfira ou veja o projeto de exemplo completo em Padrão de criação de batches de funções personalizadas. 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 a sua função personalizada.
2. Uma função para fazer o pedido remoto quando o lote estiver pronto.
3. Código do servidor para responder ao pedido em lote, calcular todos os resultados da operação e devolver os valores.

Nas secções seguintes, irá aprender a construir o código um exemplo de cada vez. Recomenda-se que crie um novo projeto de funções personalizadas com o gerador Yeoman para o gerador de Suplementos do Office . Para criar um novo projeto, consulte Começar a desenvolver funções personalizadas do Excel. Pode utilizar 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 seguinte código ao ficheiro functions.js ou functions.ts (consoante tenha utilizado 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 controlar pedidos em lote

Em seguida, adicione duas variáveis globais ao ficheiro functions.js ou functions.ts . _isBatchedRequestScheduled é importante mais tarde para a temporização de chamadas em lote para o serviço remoto.

let _batch = [];
let _isBatchedRequestScheduled = false;

Adicionar a _pushOperation função

Quando o Excel chama a sua função personalizada, tem de emitir a operação para a matriz de lote. O seguinte código de função _pushOperation mostra como adicionar uma nova operação a partir 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 carateres da operação a executar. 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 transmitidos para a sua função personalizada a partir do Excel. Por fim, resolve ou reject os métodos armazenam uma promessa que contém as informações que o serviço remoto devolve.

Adicione o seguinte código ao ficheiro functions.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 seguinte código ao ficheiro functions.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. No entanto, quando quiser utilizar este código para um serviço remoto real, deve fazer as seguintes alterações.

• 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. Pode adicionar este código ao seu ficheiro defunctions.js ou functions.ts para que possa estudar e executar todo o código neste artigo sem ter de configurar um serviço remoto real.

Adicione o seguinte código ao ficheiro functions.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 executar no seu serviço remoto em direto, 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