Tutorial: Criar funções personalizadas no Excel

Crie um suplemento do Excel que forneça funções personalizadas de JavaScript juntamente com funções internas, como SUM. Você criará funções que executam um cálculo, recuperam dados da Web e transmitem atualizações em tempo real para uma planilha.

Neste tutorial, você:

Pré-requisitos

• Node.js (a versão LTS ativa mais recente). Visite o site doNode.js para baixar e instalar a versão correta para o seu sistema operacional.

• A versão mais recente do Yeoman e do Yeoman gerador de Suplementos do Office. Para instalar essas ferramentas globalmente, execute o seguinte comando por meio do prompt de comando.

  npm install -g yo generator-office
  

  Observação

  Mesmo se você já instalou o gerador Yeoman, recomendamos atualizar seu pacote para a versão mais recente do npm.

• Office conectado a uma assinatura Microsoft 365 (incluindo o Office na web).

  Observação

  Se você ainda não tem o Office, você pode se qualificar para uma assinatura de desenvolvedor do Microsoft 365 E5 por meio do Programa de Desenvolvedor do Microsoft 365. Para obter detalhes, consulte as Perguntas frequentes. Como alternativa, você pode se inscrever para uma avaliação gratuita de 1 mês ou adquirir um plano do Microsoft 365.

Criar um projeto com funções personalizadas

Crie o projeto de código para seu suplemento de função personalizado. O gerador Yeoman para Suplementos do Office configura o projeto com funções personalizadas predefinidas para experimentar. Se você já gerou um projeto no início rápido de funções personalizadas, use esse projeto e continue em Criar uma função personalizada que solicita dados da Web.

1. Execute o comando a seguir para criar um projeto de suplemento usando o gerador Yeoman. Uma pasta que contém o projeto será adicionada ao diretório atual.

   yo office
   

   Observação

   Ao executar o comando yo office, você receberá informações sobre as políticas de coleta de dados de Yeoman e as ferramentas da CLI do suplemento do Office. Use as informações fornecidas para responder às solicitações como achar melhor.

   Quando solicitado, forneça as informações a seguir para criar seu projeto de suplemento.

   • Escolha um tipo de projeto:Excel Custom Functions using a Shared Runtime
   • Escolha um tipo de script:JavaScript
   • Como você deseja nomear seu suplemento?My custom functions add-in

   

   O gerador Yeoman cria os arquivos de projeto e instala os componentes de suporte do Node.

2. Vá para a pasta raiz do projeto.

   cd "My custom functions add-in"
   

3. Compile o projeto.

   npm run build
   

   Observação

   Os Suplementos do Office devem usar HTTPS, não HTTP, mesmo durante o desenvolvimento. Se for solicitado que você instale um certificado após a execução npm run build, aceite o prompt para instalar o certificado fornecido pelo gerador Yeoman.

4. Inicie o servidor local da web, que é executado no Node.js. Você pode experimentar o suplemento de função personalizada no Excel.

npm run start -- web --document {url}

Experimentar uma função personalizada predefinida

O projeto contém funções personalizadas pré-criadas em ./src/functions/functions.js. O arquivo ./manifest.xml os atribui ao CONTOSO namespace, que você usa para acessar as funções no Excel.

Em seguida, experimente a ADD função personalizada concluindo as etapas a seguir.

1. No Excel, vá para qualquer célula e digite =CONTOSO. Observe que o menu de preenchimento automático mostra a lista de todas as funções na CONTOSO namespace.

2. Insira =CONTOSO.ADD(10,200) na célula e selecione Enter.

A ADD função personalizada retorna 210.

Se o namespace CONTOSO não estiver disponível no menu de preenchimento automático, siga as etapas a seguir para registrar o suplemento no Excel.

Criar uma função personalizada que solicita dados da web

A integração de dados da Web é uma ótima maneira de estender o Excel por meio de funções personalizadas. Crie uma getStarCount função personalizada que recupere o número de estrelas para um repositório GitHub.

1. No projeto do suplemento Minhas funções personalizadas , abra ./src/functions/functions.js no editor de códigos.

2. Em functions.js, adicione o seguinte código.

   /**
    * Gets the star count for a GitHub repository.
    * @customfunction
    * @param {string} userName GitHub user or organization name.
    * @param {string} repoName GitHub repository name.
    * @returns {number} Number of stars given to the GitHub repository.
    */
   async function getStarCount(userName, repoName) {
     try {
       const url = `https://api.github.com/repos/${userName}/${repoName}`;
       const response = await fetch(url);
   
       if (!response.ok) {
         throw new Error(response.statusText);
       }
   
       const jsonResponse = await response.json();
       return jsonResponse.stargazers_count;
     } catch (error) {
       throw new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable, String(error));
     }
   }
   

3. Execute o seguinte comando para recriar o projeto.

   npm run build
   

4. Execute as etapas a seguir (para o Excel na Web, Windows ou Mac) para registrá-lo novamente no Excel. Você deve concluir essas etapas antes que a nova função esteja disponível.

Criar uma função personalizada assíncrona de streaming

A getStarCount função retorna o número de estrelas em um momento específico. Uma função de streaming, por outro lado, pode atualizar uma célula repetidamente. Ele inclui um invocation parâmetro que representa a célula que chamou a função.

O exemplo a seguir contém duas funções. currentTime Retorna a hora atual como uma cadeia de caracteres. A função de streaming clock é chamada invocation.setResult para atualizar a célula a cada segundo e é usada invocation.onCanceled para interromper o temporizador quando o Excel cancela a função.

O projeto do suplemento Minhas funções personalizadas já contém essas funções em ./src/functions/functions.js.

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}

/**
 * Displays the current time once a second.
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Para experimentar a função de streaming, insira =CONTOSO.CLOCK() na célula C1 e selecione Enter. A célula exibe a hora atual e as atualiza a cada segundo. Você pode usar o mesmo padrão de temporizador com funções que solicitam dados em tempo real da Web.

Solução de problemas

Você poderá encontrar problemas se executar o tutorial várias vezes. Se o Office cache já tiver uma instância de uma função com o mesmo nome, o seu complemento obtém um erro quando ele é sideload.

Para evitar esse conflito, limpe o cache do Office antes de executar npm run start. Se o processo npm já estiver em execução, insira npm run stop, limpe o cache do Office e reinicie o npm.

Próximas etapas

Você criou um novo projeto de funções personalizadas, experimentou uma função predefinida, criou uma função personalizada que solicita dados da Web e criou uma função personalizada que transmite dados. Em seguida, saiba como Compartilhar dados de função personalizada com o painel de tarefas.