Tutorial de Google Apps Script para dominar macros

Los ejecutivos efectivos saben que el tiempo es el factor limitante… Nada más, quizás, distingue tanto a los ejecutivos efectivos como su cuidado tierno y amoroso del tiempo.

Peter Drucker

El tiempo es nuestro recurso más valioso. Queremos gastarlo en las actividades de mayor impacto y valor agregado que podamos, no solo porque generalmente tienen el valor monetario más alto, sino también para desafiarnos continuamente y maximizar nuestra satisfacción laboral.

Hay muchas maneras de mejorar su eficiencia y productividad para hacer un mejor uso de su tiempo. En un artículo anterior sobre Hojas de cálculo de Google, expliqué cómo el poder de la colaboración en línea es una de las claves para aumentar la productividad.

En otro artículo, demostré cómo el lenguaje de programación Python puede ser una poderosa herramienta de análisis y automatización de tareas para los profesionales de las finanzas.

Inspirándome en esto, ahora quiero presentar un tutorial de Google Apps Script. Google Apps Script le permite escribir secuencias de comandos y programas en JavaScript para automatizar, conectar y ampliar los productos en G Suite de Google, incluidos Hojas de cálculo, Documentos, Presentaciones, Gmail, Drive y muchos otros. Aprenderlo requiere una inversión de tiempo, al igual que escribir los guiones, pero los aumentos de productividad y las oportunidades adicionales que abre hacen que valga la pena.

Como primer paso, comencemos analizando un concepto familiar: las macros.

Grabación y uso de macros en hojas de cálculo de Google

Si ha pasado mucho tiempo trabajando con Excel, seguramente habrá entrado en contacto con la interfaz de macros VBA (Visual Basic para aplicaciones) de Excel en algún momento. Ya sea grabándolos o escribiéndolos usted mismo o aprovechando los creados por otros.

Las macros son una excelente manera de automatizar flujos de trabajo repetitivos y tediosos. Puede que VBA no sea un lenguaje al que dedicó mucho tiempo para aprender, pero su belleza fue que realmente no lo necesitaba para volverse productivo y crear sus propias macros. Simplemente puede registrar el flujo de trabajo que desea automatizar y luego ingresar al código y realizar los pequeños cambios necesarios para que la macro sea más general.

De alguna manera, VBA es una gran y olvidada lección sobre cómo introducir a las personas no técnicas a la codificación . La forma en que puede registrar acciones y luego completar el código para una revisión posterior es, de hecho, una forma mucho más pragmática de aprender leyendo libros de texto y viendo tutoriales pasivamente.

La misma funcionalidad de grabación de VBA está disponible en Hojas de cálculo de Google. Aquí hay un ejemplo simple de cómo usarlo:

Comencemos con algunos datos de muestra, usando una consulta IMPORTHTML para importar una tabla. En este ejemplo, descargué una lista de Wikipedia de los 15 fondos de cobertura más grandes del mundo. No hace falta decirlo, pero este es un ejemplo arbitrario; la intención es que te enfoques más en la aplicación, sobre el tema.

El proceso de grabación de macros se inicia a través de la siguiente ruta de menú: Herramientas > Macros > Grabar macro.

Luego recorremos las acciones (formato de PC) que queremos registrar:

1. Seleccione la primera fila
2. Presione Shift + Ctrl + Flecha hacia abajo para seleccionar todo
3. Ctrl + C para copiar
4. Shift + F11 para crear una nueva hoja
5. Dale a la hoja un nuevo nombre.
6. Presione Shift + Control + V para pegar valores

Una vez hecho esto, presione el botón Guardar en la ventana de macro en la parte inferior, asígnele un nombre y un atajo de teclado opcional.

Para acciones más simples que se pueden replicar exactamente a través de estos mismos pasos, el proceso terminaría aquí y puede comenzar a usar su macro de inmediato. En este caso, sin embargo, necesitamos hacer algunos cambios antes de que el código sea utilizable. Por ejemplo, la hoja en la que copiamos deberá tener un nombre diferente cada vez. Veamos cómo hacer esto.

Escribir secuencias de comandos de Google Apps manualmente

Ahora veremos los huesos de Google Apps Script por primera vez; la plataforma de programación que se ejecuta en los servidores de Google. Esto potencia nuestras macros y le permite crear flujos de trabajo muy complejos, e incluso complementos, para las propias aplicaciones. Se puede usar para automatizar no solo el trabajo de la hoja de cálculo, sino casi cualquier cosa interconectada dentro de G Suite de Google.

El lenguaje de programación de Apps Script es JavaScript , uno de los lenguajes de programación más populares, lo que significa que hay una gran cantidad de recursos disponibles para cualquiera que desee aprender mucho. Pero, al igual que con VBA, realmente no es necesario: puede usar la misma funcionalidad de registro y simplemente hacer los pasos que desea poder repetir automáticamente. El resultado de la grabación puede parecer tosco y lo más probable es que no coincida perfectamente con lo que desea hacer, pero proporcionará un punto de partida lo suficientemente sólido. Hagámoslo ahora para el guión que acabamos de grabar.

Al grabar, tiene sentido tener cuidado de no grabar ningún paso adicional por accidente que no desee capturar en la grabación final, pero a veces es difícil de evitar: algo tan simple como seleccionar una celda diferente antes de presionar el botón Detener El botón de grabación se capturará y posteriormente se repetirá cada vez que ejecute el script. El primer paso al editar nuestro script sería limpiarlo y eliminar dichos pasos. Profundicemos yendo a Herramientas > Editor de secuencias de comandos en el menú Archivo.

Si conoce JavaScript, lo reconocerá al instante y también se sorprenderá al ver la palabra clave "var" en lugar de "let" o "const" como vería en JavaScript moderno. Esto refleja el hecho de que la versión de JavaScript en Apps Script es bastante antigua y no es compatible con muchas de las funciones más recientes del lenguaje. Sin embargo, hacia el final, presentaré una solución alternativa para aquellos que deseen utilizar las funciones de idioma más recientes.

Cuando ejecute el script por primera vez, le pedirá autorización, lo cual tiene sentido, ya que los scripts pueden modificar (y potencialmente eliminar) todos sus datos. Lo más probable es que reconozca el proceso de autorización de otros productos de Google.

Ahora podemos comenzar a modificar el código. Los cambios que debemos realizar son menores, pero si lo hace por primera vez, aún puede requerir una búsqueda rápida a través de la documentación de Sheets Apps Script y/o una búsqueda rápida de un concepto de JavaScript, como trabajar con fechas. Aquí, el hecho de que JavaScript sea un lenguaje tan extendido resulta útil: por lo general, se puede encontrar rápidamente una solución para cualquier problema al que te enfrentes o funcionalidad que se te ocurra si expresas tu término de búsqueda de una manera sencilla.

Los cambios realizados en esta versión de la secuencia de comandos de la versión original grabada son que, en lugar del nombre codificado para la nueva hoja que creamos, ahora la llamamos con la fecha de hoy. Además, también cambiamos la ruta de copia al final para hacer referencia a esta nueva hoja. Las últimas cuatro filas también demuestran cómo realizar algunas operaciones de formato, como cambiar el valor de una celda, cambiar el tamaño de las columnas y ocultar las líneas de cuadrícula.

 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); };

Ejecutar el script ahora mostrará que la nueva hoja tiene el nombre de la fecha de hoy y contiene la información copiada como valores (no fórmulas) de la hoja principal.

Ahora se pueden agregar visualizaciones de gráficos mediante el mismo proceso de registro. Usé esto para crear tres gráficos simples.

El código de limpieza para cada uno se verá así:

 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); };

Nuevamente, no se preocupe si algunas de las opciones parecen confusas: todo esto se genera automáticamente, solo necesita comprender lo suficiente para eliminar los pasos innecesarios y quizás hacer pequeños ajustes más adelante.

Ejemplos avanzados de secuencias de comandos de Google Apps: conexión de hojas a Google Drive y diapositivas

Ahora todo está empezando a tomar forma, pero ¿qué pasa si el resultado real que queremos no es una hoja de cálculo sino una presentación? Si ese es el caso, entonces la mayor parte del trabajo desde aquí aún podría ser manual, y no hemos ahorrado mucho tiempo si necesitamos hacer esto de forma recurrente.

Ahora exploremos cómo sería automatizar la creación de una presentación utilizando los datos de ejemplo de nuestra hoja de cálculo.

Este ejercicio ahora se vuelve más avanzado por dos razones:

1. Tendremos que familiarizarnos con cómo trabajar con Presentaciones de Google (y Google Drive) además de Hojas de cálculo.
2. En Presentaciones, o cuando se trabaja entre aplicaciones de G Suite en general, no hay disponible la función "Grabar macro". Esto significa que debe saber lo suficiente sobre Apps Script (y sentirse cómodo navegando por la documentación de cada uno de los productos de G Suite) para escribir scripts desde cero.

El siguiente ejemplo está destinado a proporcionar algunos componentes básicos para que pueda comenzar y familiarizarse.

Para empezar, vamos a crear una plantilla que luego queremos llenar con contenido usando nuestro script. Aquí hay dos diapositivas de presentación simples que armé:

A continuación, deberá obtener la ID de esta plantilla porque deberá hacer referencia a ella en su secuencia de comandos. Subconscientemente, habrá visto esta identificación muchas veces porque, de hecho, es la secuencia aleatoria de caracteres y números que ve en la URL de su navegador:

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

Ahora tenemos que agregar las siguientes líneas a nuestro script original. Esto volverá a solicitar autorización, esta vez para acceder a su Google Drive.

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

No verá ningún comentario visual inmediato si ejecuta este fragmento de código, pero si busca en la carpeta de su Google Drive donde almacenó la plantilla, verá que se ha creado una copia y tiene la fecha de hoy. fecha en el nombre del archivo. ¡Hemos tenido un buen comienzo!

Ahora usemos más bloques de construcción para comenzar a llenarlo con contenido, programáticamente en lugar de a mano. Agregue las siguientes filas a la misma función:

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

Ahora las cosas se están poniendo un poco más interesantes, ya que hemos cambiado la primera página para incluir la fecha de hoy. En Diapositivas, como en Hojas, trabaja con objetos (representados por clases) que tienen propiedades y métodos (es decir, funcionalidad adjunta). Estos están organizados en una jerarquía, con SpreadsheetsApp, DriveApp o SlidesApp como el objeto de nivel superior. En el fragmento de código anterior, necesitamos movernos a través de esta jerarquía paso a paso para llegar al elemento que queremos editar, en este caso: El texto en un cuadro de texto. En términos prácticos, esto significa pasar por los objetos Presentation, Slide, PageElement y Shape, hasta que finalmente lleguemos al objeto TextRange que queremos editar.

Hacer un seguimiento del tipo de objeto con el que está tratando puede ser confuso y los errores que resultan de tratar de aplicar una operación al objeto incorrecto pueden ser difíciles de resolver. Desafortunadamente, la funcionalidad de ayuda y los mensajes de error en el Editor de secuencias de comandos no siempre brindan mucha orientación aquí, el lado positivo es que tal atención al menos mejorará sus prácticas de control de calidad.

Habiendo creado la presentación y actualizado el título, ahora es el momento de insertar uno de nuestros nuevos gráficos en ella. Teniendo en cuenta la jerarquía de objetos, el siguiente código ahora debería tener 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);

Si ejecuta el script completo, la presentación de salida debería verse así:

Con suerte, este ejemplo ilustra los principios y brinda inspiración para comenzar con su propia experimentación. Si lo piensa, estoy seguro de que puede encontrar al menos algunos ejemplos de trabajos manuales que se realizan hoy en su empresa y que realmente deberían automatizarse de esta manera. Sirviendo para liberar tiempo para pensar, analizar y aplicar el juicio, en lugar de barajar mecánicamente los datos de un formato y/o lugar a otro. Mejora de la experiencia de desarrollo Como se mencionó anteriormente, la versión de JavaScript compatible con Google Apps Script es antigua y la funcionalidad del editor de scripts en línea es muy limitada. Si solo está grabando una macro o escribiendo unas pocas docenas de líneas, realmente no lo notará. Sin embargo, si tiene planes ambiciosos para automatizar todos los aspectos de sus informes semanales o mensuales, o desea crear complementos, le alegrará saber que existe una herramienta de línea de comandos que le permite desarrollar utilizando su entorno de desarrollo favorito. .

Si se encuentra en tales niveles de competencia, probablemente también desee aprovechar las funciones más recientes que JavaScript tiene para ofrecer, y potencialmente incluso más, ya que con la herramienta de línea de comandos también puede desarrollar en TypeScript.

Uso de Python para la programación de hojas de cálculo de Google

Si encuentra que trabajar con Apps Script no es su taza de té, entonces hay otras opciones, según el caso de uso. Si desea realizar cálculos numéricos más avanzados, conectarse con API o bases de datos, o simplemente prefiere el lenguaje de programación Python en lugar de JavaScript, Google Colaboratory es un producto invaluable. Le brinda un cuaderno Jupyter que se ejecuta en los servidores de Google que le permite escribir secuencias de comandos de Python que se integran perfectamente con sus archivos de Google Drive y, a través de la biblioteca 'gspread', facilita el trabajo con los datos de su hoja de cálculo.

Describí muchos de los beneficios de Python en un artículo sobre cómo usarlo para funciones financieras, que también sirve como una introducción sencilla para trabajar con cuadernos de Python y Jupyter en un contexto comercial y financiero. Un beneficio muy importante para mí es que, a diferencia de Apps Script, el cuaderno de Python en Colaboratory es interactivo, por lo que ve los resultados (o mensajes de error) después de ejecutar cada línea o pequeño bloque de código.

La automatización es adictiva

Este tutorial de Google Apps Script mostró un vistazo de lo que es posible a través del lenguaje de codificación de Google. Las posibilidades son prácticamente infinitas. Sin embargo, si no tiene experiencia técnica, los ejemplos de código pueden parecer abrumadores y es posible que esté pensando que las ganancias de productividad obtenidas al aprender Google Apps Script podrían no ser suficientes para compensar la inversión significativa en términos de tiempo necesario. para aprenderlo

Esto, por supuesto, depende de muchos factores, incluido el tipo de función que tiene o espera tener en el futuro. Pero incluso si no espera hacer nada similar a los ejemplos que se muestran aquí, comprender lo que es posible y aproximadamente cuánto trabajo se necesitaría para implementarlo puede desencadenar pensamientos e ideas sobre cómo mejorar la productividad en su empresa, por ejemplo. sus clientes, o usted mismo personalmente.

Personalmente, puedo dar fe de la satisfacción de sentarme y presionar un botón que completa una hora de tedioso trabajo manual en menos de un minuto. Después de hacer esto por quincuagésima vez, estará agradecido por el par de horas dedicadas a improvisar todo en primer lugar, lo que finalmente ha servido para liberar su tiempo para actividades de mayor valor agregado. Después de un tiempo, estos beneficios de escalabilidad se vuelven adictivos.