Primeiros passos com o Office Scripts no Excel

Os Office Scripts permitem automatizar tarefas repetitivas no Microsoft Excel, permitindo que você economize tempo, aumente a eficiência e melhore a produtividade geral.

Office Scripts foram projetados para funcionar perfeitamente com aplicativos populares do Office, como o Excel e o Word, tornando-os acessíveis a usuários de diversos departamentos. Com eles, você pode automatizar cálculos complexos, gerar relatórios, formatar documentos e executar outras ações rotineiras com apenas alguns cliques.

Devido ao amplo conjunto de soluções que o Office Scripts oferece, a ferramenta pode ser considerada o novo VBA. Ainda, uma das principais vantagens dessa ferramenta é a conexão com o Power Automate (veja mais detalhes aqui), o que permite que uma planilha de Excel seja integrada com diversos outros serviços da Microsoft e de terceiros. Nesse post, vamos aprender como criar Office Scripts no Excel.

Criando o seu primeiro Office Script

Office Scripts utilizam TypeScript, uma linguagem de programação baseada em JavaScript. Mesmo que a sintaxe do TypeScript seja completamente diferente do VBA, os profissionais que tenham alguma experiência nesta poderão aproveitar o raciocínio lógico necessário para criar soluções com ambas as ferramentas.

Os Office Scripts são escritos dentro do editor de código nativo do Excel Web. Para criar seu primeiro script, siga os passos a seguir.

Primeiro, vá até a aba “Automação”, localizada na barra de ferramentas no topo da tela.

Na aba “Automação”, você poderá ver todos os Scripts disponíveis (em branco nesta imagem para evitar a exposição de dados pessoais 😊). Para criar um novo script, navegue até a seção “Ferramentas de Script” e clique na opção “Novo Script”.

Uma nova barra lateral com o editor de código aparecerá na sua tela à direita, com um espaço para inserir seu código. É aqui que você irá criar seu primeiro Office Script!

O que é a “function main”?

Cada Office Script é executado dentro da função “main”. Uma função em JavaScript (ou em TypeScript) é um bloco de código reutilizável que executa uma tarefa específica. Ela desempenha um papel semelhante às funções/procedimentos no VBA: você pode a cria apenas uma vez para a execução de uma tarefa específica e pode acioná-la diversas vezes ao longo de seu código.

A função é inicializada com a palavra reservada “function” (você pode ver em todas as imagens que essa palavra tem uma cor diferente, pois é reservada), seguida pelo seu nome e, opcionalmente, pelos parâmetros, que são alocados entre parênteses. Em resumo, a sintaxe de uma função será mais ou menos assim:

Por padrão, os parâmetros são opcionais, então você pode manter os parênteses vazios ao criá-los. No entanto, a função “main” no contexto do Office Scripts requer pelo menos um parâmetro: workbook.

O parâmetro “workbook” representa seu arquivo do Excel e será importante para realizar qualquer iteração com em Office Scripts. Inicialmente, para Scripts menos complexos, você pode escrever todos os comandos dentro da função “main”. No entanto, conforme o seu Script ganha complexidade, pode valer a pena criar funções menores e reutilizáveis para uma melhor organização e eficiência.

Exemplo prático: Descrição do caso de uso

Nosso caso de uso fictício utilizará um arquivo do Excel para criar um “formulário” simples. Após a inserção de dados neste formulário, o Script, que será acionado por um botão, preencherá uma tabela com informações de produtos. A tabela é composta por três colunas: nome do produto, estoque atual e preço do produto.

Para configurar esse sistema, vamos utilizar tabela chamada ‘Products’, que ocupa o intervalo das células A1 a C3, juntamente com um formulário compacto nas células G4, G5 e G6, onde os usuários podem inserir os dados.

Primeiros passos com o código: criando variáveis com Office Scripts

Ao iniciar um novo script (seguindo as etapas mencionadas anteriormente), você escreverá seu código dentro da função “main”. Para começar, declare duas variáveis que irão armazenar a planilha e a tabela ‘Produtos’ do seu arquivo do Excel.

Para criar essas variáveis, utilize a palavra-chave “const” seguida dos nomes desejados. No nosso caso, vamos usar “ws” (em referência a “Worksheet”) e “productTable” (linhas 2 e 3). Atribua essas variáveis usando os métodos “getWorksheet()” e “getTable()” fornecidos pelo parâmetro workbook. As linhas de código ficarão assim:

function main(workbook: ExcelScript.Workbook) {
    const ws = workbook.getWorksheetByName('NomeDaPlanilha'); // Substitua 'NomeDaPlanilha' pelo nome da sua planilha
    const productTable = ws.getTableByName('Products'); // Substitua ‘Products’ pelo nome de sua tabela
}

No código acima:

const: Esta é a palavra reservada do JavaScript/TypeScript usada para criar uma variável que não pode ser alterada.
workbook: Referência para o arquivo de atual. Este é o elemento usado pelo JavaScript para se conectar ao nosso arquivo do Excel, e podemos acessá-lo para interagir com tabelas, planilhas, intervalos e assim por diante.
getWorksheet(): Método do objeto workbook para acessar uma planilha específica. Você pode passar como argumento o nome da planilha (como fizemos) ou a sua posição (0, 1, 2, e assim por diante).
getTable(): Método do objeto workbook para acessar uma tabela específica dentro do arquivo. A tabela pode estar em qualquer lugar no documento do Excel, e você não precisa ter a planilha ativada. Você pode passar o nome da tabela como argumento entre parênteses.

Especifique o nome da planilha e da tabela entre aspas dentro dos parênteses de cada método. É importante garantir que os nomes que você fornece correspondam exatamente aos elementos existentes; caso contrário, o seu código pode não funcionar como esperado. Como estaremos manipulando esses elementos em nosso script, precisamos recuperá-los do workbook.

Lendo dados com Office Scripts

Para ler os dados dos campos do nosso “formulário”, precisamos acessar células específicas (G4, G5, G6). Podemos fazer isso utilizando a variável “ws”, que representa a nossa planilha, usando o método “getRange()” e especificando a célula desejada. Como resultado, obteremos um objeto “Range”, que contém diversos dados relativos a um intervalo de células. Para ler o conteúdo desse intervalo, utilizamos o método “getValue()”.

Em resumo, (1) acessamos a planilha usando a variável “ws”, (2) selecionamos a célula desejada com o método “getRange()” e (3) recuperamos o valor da célula usando o método “getValue()”.

O código completo para acessar o valor da célula ‘G4’, que receberá a entrada do nome do produto, é ws.getRange(‘G4’).getValue(). Vamos atribuir esse valor a uma variável chamada productName (veja a linha de código 5):

Em seguida, repetiremos o mesmo processo para os outros dois campos do formulário (células G5 e G6), atribuindo-os às variáveis currentStock e productPrice (linhas de código 6 e 7):

Inserindo uma nova linha em uma tabela com Office Scripts

Com os valores do formulário, nossa próxima tarefa é inseri-los na tabela de Produtos. Fazemos isso referenciando a variável productTable, que contém nossa tabela de produtos, e usando o método addRow().

O método addRow() recebe dois parâmetros:

A posição (linha) da tabela onde você deseja inserir o novo registro. No nosso caso, para adicionar o registro ao final da tabela, podemos passar “null” como o valor do parâmetro. Se você deseja inserir o registro em uma linha específica, como a 5ª linha em uma tabela com 10 linhas, você forneceria o valor 5 para este parâmetro.
Os valores a serem inseridos na tabela. Como nossa tabela possui várias colunas, podemos inserir o registro como um array, em que cada elemento representará uma de suas colunas.

Em TypeScript, um array é uma coleção de elementos representados por valores cercados por colchetes, separados por vírgulas. Ao criar um array para inserir dados na tabela de produtos, devemos seguir a mesma ordem das colunas da tabela.

Para adicionar uma nova linha à tabela, utilizamos a seguinte linha de código: productTable.addRow(null, [productName, currentStock, productPrice]. Vamos entender cada componente:

productTable: Variável que armazena a tabela de produtos do seu arquivo.
addRow: Método chamado na variável productTable para adicionar uma nova linha à tabela de produtos.
null: Utilizado como o primeiro argumento do método addRow, ele indica que o novo registro deve ser inserido no final da tabela.
[productName, currentStock, productPrice]: Array contendo as variáveis extraídas do formulário, dispostas na mesma ordem das colunas na tabela de produtos.

Este é o resultado:

Como etapa adicional, desejamos informar ao usuário que o registro foi inserido com sucesso na tabela de produtos. Essa notificação aparecerá na célula “F8” com a mensagem “Product added to the table!”. Podemos fazer isso usando a seguinte linha de código: ws.getRange(‘F8′).setValue(Product added to the table!’). Vamos entender os componentes do código:

ws: Referência à variável que criamos para armazenar a planilha ‘Sheet1’.
getRange: Este método é chamado na planilha para acessar um intervalo específico. Fornecemos o intervalo desejado como argumento dentro dos parênteses. No nosso caso, estamos acessando o intervalo ‘F8’.
setValue: Método para escrever um valor no intervalo. Anteriormente, usamos o método getValue() para ler os valores das células, e agora estamos usando setValue para escrever. O valor a ser inserido na célula é passado como argumento para o método dentro dos parênteses.

Executando o Office Script

Com o Office Script preparado, é hora de testá-lo. Clique no símbolo de três pontos acima do editor de código e escolha “Executar” nas opções.

Ao executar o script, ele realiza as seguintes tarefas:

Lê os valores inseridos no formulário, que estão localizados no intervalo G4:G6.
Adiciona cada valor como uma nova linha ao final da tabela Products.
Exibe uma mensagem de sucesso na célula F8.

Limpar células com Office Scripts

Para promover uma melhor experiência do usuário, podemos implementar um recurso adicional no script: limpar os valores no ‘New Product Form’ após sua execução, garantindo que ele esteja pronto para uso futuro.

Para limpar as células, utilizamos o método clear() em conjunto com getRange(). Por padrão, o método clear() remove tanto os valores quanto a formatação do intervalo. No entanto, em nosso cenário, queremos apenas limpar os valores. Para isso, especificamos passamos ExcelScript.ClearApplyTo.contents (sem aspas) como argumento para o método clear(). Este é um elemento nativo fornecido pelo Office Scripts que permite customizar o que deve ser afetado pelo método clear(). O código completo para essa tarefa é (ver linha de código 12): ws.getRange(‘G4:G6’).clear(ExcelScript.ClearApplyTo.contents).

E este é o resultado quando adicionamos um produto “Pinapple”, com 8 unidades em estoque e um preço de $4: o produto é inserido na tabela e o formulário é limpo para um novo uso.

Vinculando o Office Scripts a um botão

Para tornar seu Office Script ainda mais amigável para o usuário, podemos criar um botão dentro de sua planilha, permitindo a execução do script sem acessar o editor de código. Essa funcionalidade é costumeiramente é feito no VBA. Para alcançar isso, você precisa clicar nos três pontos acima do editor de código, rolar pelas opções e encontrar “Adicionar botão”.

Com o botão na tela, os usuários podem facilmente executar o script, sem a necessidade de navegar pelo editor de código. Isso torna o processo mais acessível e conveniente.

O texto do botão pode ser editado ao clicar com o botão direito do mouse e selecionar “Editar texto”.

Código completo

Este é o código completo:

function main(workbook: ExcelScript.Workbook) {
  const ws = workbook.getWorksheet('Sheet1')
  const productTable = workbook.getTable('Products')

  const productName = ws.getRange('G4').getValue()
  const currentStock = ws.getRange('G5').getValue()
  const productPrice = ws.getRange('G6').getValue()

  productTable.addRow(null, [productName, currentStock, productPrice])

  ws.getRange('F8').setValue('Product added to the table!')
  ws.getRange('G4:G6').clear(ExcelScript.ClearApplyTo.contents)

}

Neste post, abordamos os passos fundamentais para criar o seu primeiro script usando o Office Scripts. Ao utilizar essa ferramenta, você pode melhorar significativamente sua experiência com o Excel e otimizar seu fluxo de trabalho. Os Office Scripts permitem automatizar tarefas repetitivas, validar dados e gerar relatórios sem esforço.

Agora que já sabe como criar e executar um script, você tem a oportunidade de otimizar seus processos no Excel e aumentar a eficiência em suas tarefas diárias!