Tutorial de script do Google Apps para dominar macros

Executivos eficazes sabem que o tempo é o fator limitante... Nada mais, talvez, distingue os executivos eficazes tanto quanto seu cuidado carinhoso com o tempo.

Peter Drucker

O tempo é o nosso recurso mais valioso. Queremos gastá-lo nas atividades de maior impacto e agregação de valor que pudermos, não apenas porque geralmente carregam o maior valor monetário, mas também para nos desafiar continuamente e maximizar nossa satisfação no trabalho.

Há muitas maneiras de melhorar sua eficiência e produtividade para fazer melhor uso do seu tempo. Em um artigo anterior sobre o Planilhas Google, expliquei como o poder da colaboração online é uma dessas chaves para aumentar a produtividade.

Em outro artigo, demonstrei como a linguagem de programação Python pode ser uma poderosa ferramenta de análise e automação de tarefas para profissionais de finanças.

Inspirando-se nisso, agora quero apresentar um tutorial do Google Apps Script. O Google Apps Script permite escrever scripts e programas em JavaScript para automatizar, conectar e estender os produtos do G Suite do Google, incluindo Planilhas, Documentos, Apresentações, Gmail, Drive e vários outros. Aprendê-lo requer um investimento de tempo, assim como escrever os roteiros, mas a produtividade aumenta e as oportunidades adicionais que ele abre fazem valer a pena.

Como primeiro passo, vamos começar analisando um conceito familiar: macros.

Gravando e usando macros no Planilhas Google

Se você passou um tempo significativo trabalhando com o Excel, provavelmente terá entrado em contato com a interface de macro VBA (Visual Basic for Applications) do Excel em algum momento. Seja gravando ou escrevendo-os você mesmo ou pegando carona em outros criados por outros.

As macros são uma ótima maneira de automatizar fluxos de trabalho repetitivos e tediosos. O VBA pode não ser uma linguagem que você dedicou muito tempo para aprender, mas sua beleza era que você realmente não precisava para se tornar produtivo e criar suas próprias macros. Você pode simplesmente gravar o fluxo de trabalho que deseja automatizar e, em seguida, entrar no código e fazer as pequenas alterações necessárias para tornar a macro mais geral.

De certa forma, o VBA é uma grande e esquecida lição de como introduzir pessoas não técnicas na codificação . A maneira como você pode gravar ações e, em seguida, ter o código preenchido para revisão posterior é, de fato, uma maneira muito mais pragmática de aprender lendo livros didáticos e assistindo a tutoriais passivamente.

A mesma funcionalidade de gravação do VBA está disponível no Planilhas Google. Aqui está um exemplo simples de como usá-lo:

Vamos começar com alguns dados de exemplo, usando uma consulta IMPORTHTML para importar uma tabela. Neste exemplo, baixei uma lista da Wikipedia dos 15 maiores fundos de hedge do mundo. Escusado será dizer, mas este é um exemplo arbitrário; a intenção é que você se concentre mais na aplicação, sobre o assunto.

O processo de gravação de macros é iniciado através do seguinte caminho de menu: Ferramentas > Macros > Gravar macro.

Em seguida, percorremos as ações (formato PC) que queremos gravar:

1. Selecione a primeira linha
2. Pressione Shift + Ctrl + Seta para baixo para selecionar tudo
3. Ctrl + C para copiar
4. Shift + F11 para criar uma nova planilha
5. Dê um novo nome à planilha
6. Pressione Shift + Control + V para colar valores

Uma vez feito, pressione o botão Salvar na janela de macro na parte inferior, dê um nome e um atalho de teclado opcional.

Para ações mais simples que podem ser replicadas exatamente por meio dessas mesmas etapas, o processo terminaria aqui e você pode começar a usar sua macro imediatamente. Nesse caso, porém, precisamos fazer algumas alterações antes que o código seja utilizável. Por exemplo, a planilha para a qual copiamos precisará ter um nome diferente a cada vez. Vamos ver como fazer isso.

Como escrever o script do Google Apps manualmente

Agora veremos os ossos do Google Apps Script pela primeira vez; a plataforma de programação que roda nos servidores do Google. Isso potencializa nossas macros e permite que você crie fluxos de trabalho muito complexos e até complementos para os próprios aplicativos. Ele pode ser usado para automatizar não apenas o trabalho com planilhas, mas praticamente qualquer coisa interconectada no G Suite do Google.

A linguagem de programação do Apps Script é JavaScript , uma das linguagens de programação mais populares, o que significa que há muitos recursos disponíveis para qualquer pessoa que queira aprender extensivamente. Mas, assim como no VBA, você realmente não precisa: você pode usar a mesma funcionalidade de gravação e simplesmente executar as etapas que deseja repetir automaticamente. A saída da gravação pode parecer grosseira e provavelmente não combinará perfeitamente com o que você deseja fazer, mas fornecerá um ponto de partida sólido o suficiente. Vamos agora fazer isso para o script que acabamos de gravar.

Ao gravar, faz sentido ter cuidado para não gravar acidentalmente nenhuma etapa adicional que você não deseja que seja capturada na gravação final, mas às vezes é difícil evitar: algo tão simples quanto selecionar uma célula diferente antes de pressionar o botão Parar O botão de gravação será capturado e subsequentemente repetido toda vez que você executar o script. A primeira etapa ao editar nosso script seria limpá-lo e remover essas etapas. Vamos mergulhar indo em Ferramentas > Editor de scripts no menu de arquivos.

Se você conhece JavaScript, reconhecerá isso instantaneamente e também poderá se surpreender ao ver a palavra-chave “var” em vez de “let” ou “const”, como veria no JavaScript moderno. Isso reflete o fato de que a versão JavaScript no Apps Script é bastante antiga e não oferece suporte a muitos dos recursos mais recentes da linguagem. No final, apresentarei uma solução alternativa para aqueles que gostariam de usar os recursos de linguagem mais recentes.

Quando você executar o script pela primeira vez, ele solicitará autorização, o que faz sentido, pois os scripts podem modificar (e potencialmente excluir) todos os seus dados. Você provavelmente reconhecerá o processo de autorização de outros produtos do Google.

Agora podemos começar a modificar o código. As alterações que precisamos fazer são pequenas, mas se você fizer isso pela primeira vez, ainda poderá exigir uma pesquisa rápida na documentação do Sheets Apps Script e/ou uma pesquisa rápida de um conceito JavaScript, como trabalhar com datas. Aqui, o fato de JavaScript ser uma linguagem tão difundida vem a calhar: uma solução para qualquer problema ou funcionalidade que você enfrenta geralmente pode ser encontrada rapidamente se você expressar seu termo de pesquisa de maneira direta.

As alterações feitas nesta versão do script da versão original gravada são que, em vez do nome codificado para a nova planilha que criamos, agora a nomeamos com a data de hoje. Além disso, também alteramos o caminho da cópia no final para fazer referência a essa nova planilha. As últimas quatro linhas também demonstram como realizar algumas operações de formatação, como alterar o valor de uma célula, redimensionar colunas e ocultar linhas de grade.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

A execução do script agora mostrará que a nova planilha está de fato nomeada com a data de hoje e contém as informações copiadas como valores (não fórmulas) da planilha principal.

Visualizações de gráficos agora podem ser adicionadas usando o mesmo processo de registro. Eu usei isso para criar três gráficos simples.

A limpeza do código para cada um será algo assim:

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

Novamente, não se preocupe se algumas das opções parecerem confusas: tudo isso é gerado automaticamente, você só precisa entender o suficiente para remover as etapas desnecessárias e talvez fazer pequenos ajustes posteriormente.

Exemplos avançados de script do Google Apps: conectar o Planilhas ao Google Drive e ao Apresentações

Tudo agora está começando a tomar forma, mas e se a saída real que queremos não for uma planilha, mas uma apresentação? Se for esse o caso, a maior parte do trabalho daqui ainda pode ser manual, e não economizamos muito tempo se precisarmos fazer isso de forma recorrente.

Vamos agora explorar como seria automatizar a criação de uma apresentação usando os dados de exemplo de nossa planilha.

Este exercício agora se torna mais avançado por dois motivos:

1. Precisaremos nos familiarizar com o modo de trabalhar com o Apresentações Google (e o Google Drive), além do Planilhas.
2. No Apresentações, ou ao trabalhar entre os aplicativos do G Suite em geral, não há a funcionalidade "Gravar macro" disponível. Isso significa que você precisa saber o suficiente sobre o Apps Script (e se sentir à vontade para navegar pela documentação de cada um dos produtos do G Suite) para escrever scripts do zero.

Este próximo exemplo destina-se a fornecer alguns blocos de construção básicos para você começar e se familiarizar.

Para começar, vamos criar um modelo que mais tarde queremos preencher com conteúdo usando nosso script. Aqui estão dois slides de apresentação simples que eu montei:

Em seguida, você precisará obter o ID deste modelo porque terá que se referir a ele em seu script. Inconscientemente, você já viu esse ID muitas vezes porque é, na verdade, a sequência aleatória de caracteres e números que você vê na URL do seu navegador:

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

Agora temos que adicionar as seguintes linhas ao nosso script original. Isso solicitará novamente a autorização, desta vez para acessar seu Google Drive.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

Você não verá nenhum feedback visual imediato se executar este snippet de código, mas se você olhar na pasta do seu Google Drive onde você armazenou o modelo, verá que uma cópia dele foi realmente criada e tem o valor de hoje data no nome do arquivo. Começamos bem!

Vamos agora usar mais blocos de construção para começar a preenchê-lo com conteúdo, programaticamente em vez de manualmente. Adicione as seguintes linhas à mesma função:

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Agora as coisas estão ficando um pouco mais interessantes, pois alteramos a primeira página para incluir a data de hoje. No Apresentações, como no Planilhas, você trabalha com objetos (representados por classes), cada um com propriedades e métodos (ou seja, funcionalidade anexada). Eles são organizados em uma hierarquia, com SpreadsheetsApp, DriveApp ou SlidesApp sendo o objeto de nível superior. No trecho de código acima, precisamos percorrer essa hierarquia passo a passo para chegar ao elemento que queremos editar, neste caso: O texto em uma caixa de texto. Praticamente falando, isso significa passar pelos objetos Presentation, Slide, PageElement e Shape, até finalmente chegarmos ao objeto TextRange que queremos editar.

Manter o controle do tipo de objeto com o qual você está lidando pode ser confuso e os bugs resultantes da tentativa de aplicar uma operação ao objeto errado podem ser difíceis de resolver. Infelizmente, a funcionalidade de ajuda e as mensagens de erro no próprio Editor de Scripts nem sempre fornecem muita orientação aqui, o lado bom é que essa atenção pelo menos melhorará suas práticas de controle de qualidade.

Depois de criar a apresentação e atualizar o título, agora é hora de inserir um de nossos novos gráficos nela. Mantendo a hierarquia de objetos em mente, o código a seguir agora deve fazer sentido:

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

Se você executar o script completo, a apresentação de saída deve se parecer com isto:

Espero que este exemplo ilustre os princípios e forneça inspiração para você começar com sua própria experimentação. Se você pensar bem, tenho certeza de que pode encontrar pelo menos alguns exemplos de trabalho manual feito em sua empresa hoje que realmente deveria ser automatizado dessa maneira. Servindo para liberar tempo para pensar, analisar e aplicar julgamento, em vez de embaralhar mecanicamente os dados de um formato e/ou lugar para outro. Melhorando a experiência de desenvolvimento Conforme mencionado anteriormente, a versão JavaScript compatível com o Google Apps Script é antiga e a funcionalidade do Script Editor on-line é muito limitada. Se você estiver apenas gravando uma macro ou escrevendo algumas dúzias de linhas, você realmente não notará. Se, no entanto, você tiver planos ambiciosos para automatizar todos os aspectos de seus relatórios semanais ou mensais, ou quiser criar plugins, ficará feliz em saber que existe uma ferramenta de linha de comando que permite desenvolver usando seu ambiente de desenvolvimento favorito .

Se você estiver nesses níveis de proficiência, provavelmente também desejará aproveitar os recursos mais recentes que o JavaScript tem a oferecer, e potencialmente ainda mais, pois com a ferramenta de linha de comando você também pode desenvolver em TypeScript.

Usando Python para programação do Planilhas Google

Se você achar que trabalhar com o Apps Script não é sua praia, existem outras opções, dependendo do caso de uso. Se você deseja fazer cálculos numéricos mais avançados, conectar-se a APIs ou bancos de dados ou simplesmente preferir a linguagem de programação Python em vez de JavaScript, o Colaboratory do Google é um produto inestimável. Ele oferece um notebook Jupyter executado nos servidores do Google que permite escrever scripts Python que se integram perfeitamente aos seus arquivos do Google Drive e, por meio da biblioteca 'gspread', facilita o trabalho com os dados da planilha.

Descrevi muitos dos benefícios do Python em um artigo sobre como usá-lo para funções financeiras, que também serve como uma introdução suave ao trabalho com notebooks Python e Jupyter em um contexto comercial e financeiro. Um benefício muito importante para mim é que, diferentemente do Apps Script, o notebook Python no Colaboratory é interativo, então você vê os resultados (ou mensagem de erro) depois de executar cada linha ou pequeno bloco de código.

A automação é viciante

Este tutorial do Google Apps Script mostrou um vislumbre do que é possível através da linguagem de codificação do Google. As possibilidades são virtualmente infinitas. No entanto, se você não tiver conhecimento técnico, os exemplos de código podem parecer assustadores e você pode estar pensando que os ganhos de produtividade obtidos com o aprendizado do Google Apps Script podem não ser suficientes para compensar o investimento significativo em termos de tempo necessário para aprendê-lo.

Isso, é claro, depende de muitos fatores, incluindo o tipo de função que você tem ou espera ter no futuro. Mas mesmo que você não espere fazer nada semelhante aos exemplos mostrados aqui, ter uma compreensão do que é possível e aproximadamente quanto trabalho seria necessário para implementar pode desencadear pensamentos e ideias sobre como melhorar a produtividade em sua empresa, por exemplo seus clientes, ou você mesmo pessoalmente.

Pessoalmente, posso atestar a satisfação de sentar e apertar um botão que completa uma hora de trabalho manual tedioso em menos de um minuto. Depois de fazer isso pela 50ª vez, você ficará grato pelas duas horas gastas juntando tudo em primeiro lugar, o que serviu para liberar seu tempo para atividades de maior valor agregado. Depois de um tempo, esses benefícios de escalabilidade se tornam viciantes.