Google Sheets es una poderosa herramienta de hoja de cálculo basada en la nube que le permite hacer casi todo lo que podría hacer en Microsoft Excel. Pero el verdadero poder de Google Sheets es la función Google Scripting que viene con él.
Las secuencias de comandos de Google Apps son una herramienta de secuencias de comandos en segundo plano que funciona no solo en Google Sheets, sino también en Google Docs, Gmail, Google Analytics y casi todos los demás servicios en la nube de Google. Le permite automatizar esas aplicaciones individuales e integrar cada una de esas aplicaciones entre sí.
En este artículo, aprenderá cómo comenzar con las secuencias de comandos de Google Apps, cómo crear una secuencia de comandos básica en Hojas de cálculo de Google para leer y escribir datos de celdas y las funciones de secuencias de comandos avanzadas más efectivas de Hojas de cálculo de Google.
¿Cómo crear un script de aplicaciones de Google?
Puede comenzar ahora mismo a crear su primer script de Google Apps desde hojas de cálculo de Google.
Para hacer esto, seleccione Herramientas en el menú, luego Editor de secuencias de comandos.
Esto abre la ventana del editor de secuencias de comandos y se establece de manera predeterminada en una función llamada myfunction(). Aquí es donde puede crear y probar su Google Script.
Para intentarlo, intente crear una función de secuencia de comandos de Hojas de cálculo de Google que lea los datos de una celda, realice un cálculo en ella y envíe la cantidad de datos a otra celda.
La función para obtener datos de una celda son las funciones getRange() y getValue(). Puede identificar la celda por fila y columna. Entonces, si tiene un valor en la fila 2 y la columna 1 (la columna A), la primera parte de su secuencia de comandos se verá así:
Function myFunction() { var hoja = SpreadsheetApp.getActiveSheet(); var fila = 2; var columna = 1; var datos = hoja.getRange(fila, columna).getValue();}
Esto almacena el valor de esa celda en la variable de datos. Puede realizar un cálculo en los datos y luego escribir esos datos en otra celda. Así que la última parte de esta función será:
Var resultados = datos * 100; hoja.getRange(fila, col+1).setValue(resultados);}
Cuando haya terminado de escribir su función, seleccione el icono del disco para guardar.
La primera vez que ejecute una nueva función de secuencia de comandos de Hojas de cálculo de Google como esta (seleccionando el icono de ejecución), deberá proporcionar autorización para que la secuencia de comandos se ejecute en su cuenta de Google.
Permita que los permisos continúen. Una vez que se ejecute su secuencia de comandos, verá que la secuencia de comandos escribió los resultados del cálculo en la celda de destino.
Ahora que sabe cómo escribir una función de secuencia de comandos básica de Google Apps, echemos un vistazo a algunas funciones más avanzadas.
Use getValues para cargar arreglos
Puede llevar el concepto de hacer cálculos sobre datos en su hoja de cálculo con secuencias de comandos a un nuevo nivel mediante el uso de matrices. Si carga una variable en la secuencia de comandos de Google Apps mediante getValues, la variable será una matriz que puede cargar varios valores de la hoja.
Function myFunction() { var hoja = SpreadsheetApp.getActiveSheet(); var datos = hoja.getDataRange().getValues();
La variable de datos es una matriz multidimensional que contiene todos los datos de la hoja. Para realizar un cálculo en los datos, utiliza un bucle for. El contador del bucle for funcionará en cada fila y la columna permanecerá constante, según la columna de la que desee extraer los datos.
En nuestra hoja de cálculo de ejemplo, puede realizar cálculos en las tres filas de datos de la siguiente manera.
For (var i = 1; i < datos.longitud; i++) { var resultado = datos[i][0] * 100; hoja.getRange(i+1, 2).setValue(resultado); }}
Guarde y ejecute este script tal como lo hizo anteriormente. Verá que todos los resultados se completan en la columna 2 de su hoja de cálculo.
Notará que hacer referencia a una celda y una fila en una variable de matriz es diferente que con una función getRange.
Data[i][0] se refiere a las dimensiones de la matriz donde la primera dimensión es la fila y la segunda es la columna. Ambos comienzan en cero.
GetRange(i+1, 2) se refiere a la segunda fila cuando i=1 (dado que la fila 1 es el encabezado), y 2 es la segunda columna donde se almacenan los resultados.
Use appendRow para escribir resultados
¿Qué sucede si tiene una hoja de cálculo en la que desea escribir datos en una nueva fila en lugar de una nueva columna?
Esto es fácil de hacer con la función appendRow. Esta función no molestará ningún dato existente en la hoja. Simplemente agregará una nueva fila a la hoja existente.
Como ejemplo, haga una función que cuente del 1 al 10 y muestre un contador con múltiplos de 2 en una columna Contador.
Esta función se vería así:
Function myFunction() { var hoja = SpreadsheetApp.getActiveSheet(); for (var i = 1; i<11; i++) { var resultado = i * 2; hoja.appendRow([i,resultado]); }}
Estos son los resultados cuando ejecuta esta función.
Procesar fuentes RSS con URLFetchApp
Puede combinar la función de secuencia de comandos anterior de Hojas de cálculo de Google y URLFetchApp para extraer la fuente RSS de cualquier sitio web y escribir una fila en una hoja de cálculo para cada artículo publicado recientemente en ese sitio web.
¡Este es básicamente un método de bricolaje para crear su propia hoja de cálculo del lector de fuentes RSS!
El script para hacer esto tampoco es demasiado complicado.
Function myFunction() { var hoja = SpreadsheetApp.getActiveSheet(); var elemento, fecha, título, enlace, desc; var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText(); var doc = Xml.parse(txt, falso); título = doc.getElement().getElement("canal").getElement("título").getText(); var items = doc.getElement().getElement("canal").getElements("item"); // Análisis de elementos individuales en RSS Feedfor (var i in items) { item = items[i]; título = artículo.getElement("título").getText(); enlace = item.getElement("enlace").getText(); fecha = item.getElement("pubDate").getText(); desc = item.getElement("descripción").getText(); hoja.appendRow([título,enlace,fecha,desc]);}}
Como puede ver, Xml.parse extrae cada elemento de la fuente RSS y separa cada línea en título, enlace, fecha y descripción.
Usando la función appendRow, puede colocar estos elementos en columnas apropiadas para cada uno de los elementos de la fuente RSS.
La salida en su hoja se verá así:
En lugar de incrustar la URL de la fuente RSS en el script, podría tener un campo en su hoja con la URL y luego tener varias hojas para cada sitio web que desee monitorear.
Concatenar cadenas y agregar un retorno de carro
Puede llevar la hoja de cálculo RSS un paso más allá agregando algunas funciones de manipulación de texto y luego usar las funciones de correo electrónico para enviarse un correo electrónico con un resumen de todas las publicaciones nuevas en el canal RSS del sitio.
Para hacer esto, en la secuencia de comandos que creó en la sección anterior, querrá agregar algunas secuencias de comandos que extraerán toda la información en la hoja de cálculo.
Querrá crear la línea de asunto y el cuerpo del texto del correo electrónico analizando toda la información de la misma matriz de elementos que usó para escribir los datos RSS en la hoja de cálculo.
Para ello, inicialice el asunto y el mensaje colocando las siguientes líneas antes de los elementos del bucle For.
Var subject = Últimos 10 artículos publicados en mysite.com
Var mensaje =
Luego, al final de los elementos del ciclo (justo después de la función appendRow), agregue la siguiente línea.
Mensaje = mensaje + título + 'n' + enlace + 'n' + fecha + 'n' + desc + 'n' + 'nn';
El símbolo + concatenará los cuatro elementos seguidos de n para un retorno de carro después de cada línea. Al final de cada bloque de datos de título, querrá dos retornos de carro para un cuerpo de correo electrónico con un formato agradable.
Una vez que se procesan todas las filas, la variable del cuerpo contiene la cadena completa del mensaje de correo electrónico. ¡Ahora estás listo para enviar el correo electrónico!
¿Cómo enviar correo electrónico en Google Apps Script?
La siguiente sección de su Google Script será enviar el asunto y el cuerpo por correo electrónico. Hacer esto en Google Script es muy fácil.
Var dirección de correo electrónico = [email protected];
MailApp.sendEmail(dirección de correo electrónico, asunto, mensaje);
MailApp es una clase muy conveniente dentro de los scripts de Google Apps que le brinda acceso al servicio de correo electrónico de su cuenta de Google para enviar o recibir correos electrónicos. Gracias a esto, la línea única con la función sendEmail le permite enviar cualquier correo electrónico con solo la dirección de correo electrónico, la línea de asunto y el cuerpo del texto.
Así es como se verá el correo electrónico resultante:
La combinación de la capacidad de extraer un feed RSS de un sitio web, almacenarlo en una hoja de cálculo de Google y enviárselo a usted mismo con enlaces URL incluidos, hace que sea muy conveniente seguir el contenido más reciente de cualquier sitio web.
Este es solo un ejemplo del poder que está disponible en las secuencias de comandos de Google Apps para automatizar acciones e integrar múltiples servicios en la nube.
