Como manipular o Excel a partir do Power Automate - uma introdução aos Office Scripts
O VBA (Visual Basic for Applications) foi criado em 1993 pela Microsoft e integrado na versão 5 do Excel (quem se lembra!?). Desde então, milhões de utilizadores em todo o mundo procuram automatizar tarefas repetitivas no Excel, transformando, muitas vezes, o Excel em aplicações que servem os mais diversos fins (alguns, bastante engraçados!). 30 anos depois, o VBA para Excel continua vivo e com legiões de entusiásticos seguidores e alguns processos críticos em algumas empresas continuam a depender do mesmo.
Talvez por isso, o Excel Desktop mantém a disponibilidade do VBA, mas sem grande desenvolvimento. É como se a Microsoft não tivesse realmente coragem de o retirar com medo de dececionar os utilizadores e provocar todo o tipo de incidentes com diferentes tipos de consequências. Veja-se o que está disponível na minha versão do Excel atualizada a novembro de 2023:
Não parece muito atual e a razão é que o desenvolvimento tem sido orientado para a cloud. O Excel e a plataforma Power funcionam hoje integralmente na cloud e assim, o Office Scripts está aos poucos a substituir o VBA para Excel.
O que é o Office Scripts?
O Office Scripts é uma ferramenta de automação que permite criar e executar scripts em aplicações do Office. Os scripts são blocos de código que podem ser usados para automatizar tarefas, como formatar folhas de cálculo, criar relatórios ou enviar e-mails.
Principais características dos Office Scripts:
- São escritos em JavaScript, uma linguagem de programação relativamente fácil de aprender e usar
- Podem ser criados usando o Gravador de Ações, que regista as ações que o utilizador executa numa aplicação do Office ou usando o Editor de Código, que permite criar scripts personalizados
- Podem ser usados em qualquer aplicação do Office, incluindo Word, Excel, PowerPoint e Outlook
- Podem ser usados para automatizar tarefas simples, como introduzir fórmulas ou gráficos em folhas de cálculo, ou para desempenhar tarefas mais complexas, como criar aplicações ou relatórios personalizados.
Os scripts do Office são uma ferramenta poderosa que pode ser usada para aumentar a produtividade e a flexibilidade do Office. Não parece, no entanto, que disponham de toda a flexibilidade e alcance do VBA, por enquanto, mas é este o caminho a seguir neste momento.
Como executar Office Scripts no Excel?
É possível executar um script do Office no Excel a partir do menu “Automate” do friso superior (terá de o ativar primeiro), escolhendo o script pretendido.
Uma característica interessante dos scripts é que podem ser executados em qualquer ficheiro Excel do domínio a que pertençam. Ou seja, o script é um ficheiro que fica armazenado numa determinada cloud dentro de um domínio Office 365 e que pode correr “em cima” de um qualquer ficheiro Excel dentro desse domínio.
Obviamente, há que ter em conta que a lógica presente no código deve ser compatível com o ficheiro em causa.
Outra forma de executar scripts do Office é a partir do Power Automate, o que permite a integração com a plataforma Power. A partir daqui, é possível automatizar praticamente tudo:
- Criar ficheiros Excel
- Enviar emails com ficheiros Excel em anexo
- Manipular ficheiros Excel existentes
- Obter dados de ficheiros Excel e enviá-los para outra aplicação ou guardá-los num ficheiro
- Entre muitas outras soluções
Ler e escrever em células do Excel a partir do Power Automate
Neste post, vamos ler e escrever em células do Excel a partir do Power Automate. Penso que é óbvio que este tipo de automação ajudará a descobrir muitas soluções:
- Recolher dados do Excel permite utilizar os ficheiros existentes e fazer algo com eles. Por exemplo, um endereço de email guardado numa determinada célula permitirá ao Power Automate enviar uma mensagem a um destinatário. Obter o valor de unidades em stock de um determinado artigo, poderá servir para servir para informar a equipa de vendas ou da logística que o mesmo está acima ou abaixo do valor de segurança.
- Escrever dados no Excel poderá servir para automatizar relatórios ou cálculos dinâmicos. Por exemplo, inserir um determinado valor numa célula irá atualizar todas as fórmulas e gráficos que dela dependem.
Comecemos com um exemplo simples: o nosso mapa de stocks atual é o seguinte:
Pretendemos recolher o valor do stock da cidade do Porto e enviar o respetivo valor (10.000 unidades) para o respetivo gestor, o Manuel.
Como requisito, já sabemos, o ficheiro Excel terá de ser armazenado na cloud, dentro do nosso domínio para que seja acessível pelo Power Automate na mesma cloud. O script poderá ficar armazenado no mesmo local e poderá ser criado no Excel Desktop:
Criação do Office Script no Excel
O desenvolvimento do script será realizado no editor que se abre no painel do lado direito do Excel. O código de demonstração é carregado imediatamente e serve para nos ajudar a arrancar:
Vamos substituir este código por este:
function main(workbook: ExcelScript.Workbook, cidade: string) {
let ws = workbook.getActiveWorksheet();
let rng = ws.getRange("B3:E5");
let rgnArr = rng.getValues();
for (let i = 0; i <= rgnArr.length - 1; i++) {
if (cidade === rgnArr[i][0]) {
return rgnArr[i][3];
}
};
}
Basicamente, este script recebe como argumentos:
- o workbook em que irá ser executado, ou seja, o ficheiro Excel acima representado
- a cidade para a qual pretendemos obter os dados
E a função devolve o email do gestor.
Vamos guardar o script no SharePoint e voltar ao Power Automate para criar um novo fluxo com um trigger manual que requer o input “Cidade”:
A este trigger, vamos acrescentar uma ação de execução de um Office Script:
O terceiro passo, servirá para visualizarmos o resultado do script mais facilmente. Acrescentemos uma ação “Compose” com o conteúdo dinâmico “result” proveniente do passo anterior:
O flow estará neste momento assim:
Para validarmos que o fluxo e o script funcionam, vamos fazer um teste. Vamos executar o fluxo com o input “Coimbra” e analisar o resultado:
Aparentemente, o fluxo correu como previsto. O script considerou o parâmetro “Cidade” e pesquisou-o na tabela do Excel. Uma vez encontrado, devolveu o email do respetivo gestor, que é
Supondo que temos interesse não só no email do gestor, mas também no valor do stock, no nome do gestor e do seu email, poderíamos alterar o script acima para o seguinte:
function main(workbook: ExcelScript.Workbook, cidade: string) {
let ws = workbook.getActiveWorksheet();
let rng = ws.getRange("B3:E5");
let rgnArr = rng.getTexts();
let stock: string | number | boolean;
let nome: string | number | boolean;
let email: string | number | boolean;
for (let i = 0; i <= rgnArr.length - 1; i++) {
if (cidade === rgnArr[i][0]) {
stock = rgnArr[i][1];
nome = rgnArr[i][2];
email = rgnArr[i][3];
return stock + '|' + nome + '|' + email
}
};
}
Como se pode ver neste código, o valor que o script devolve é agora não um valor único, mas sim um array que contém os valores das três colunas. A este array atribuímos o nome rngArr e optámos por devolver os resultados com a concatenação dos três textos com um separador “pipe” (|).
Do lado do Power Automate, este flow poderia ser complementado logo após o step “Compose”, que obtém o resultado do script com a inicialização de um array cuja expressão no campo “Value” seria:
split(outputs('Compose_Resultado'), '|')
Então, o que podemos fazer de seguida seria inicializar três variáveis distintas:
O código da variável ValorStock é o seguinte:
trim(variables('ResultadoArray')?[0])
Basicamente, esta expressão devolve o primeiro elemento do array (índice 0) e remove os espaços desnecessários. O código para as variáveis NomeGestor e EmailGestor é idêntico, alterando-se apenas o índice que seria, respetivamente, 1 e 2.
Finalmente, para enviar o resultado por email ao respetivo gestor, acrescentaríamos o seguinte passo:
Com as variáveis devidamente definidas é bastante fácil trabalhar com o conteúdo dinâmico do Power Automate! O resultado final seria então um email idêntico ao seguinte:
Identificando-se as regras de negócio e os processos organizacionais, a possibilidade de automação de notificações via email ganha novos contornos. É possível fazer chegar a informação de gestão aos decisores, no momento certo.
