5 funciones de guión de Google Sheets que necesitas saber

Google Sheets es una poderosa herramienta de hoja de cálculo basada en la nube que le permite hacer casi todo lo que puede hacer en Microsoft Excel. Pero el verdadero poder de Google Sheets es la función de secuencias de comandos de Google que viene con ella.

Google Apps Scripting es una herramienta de secuencia de comandos que funciona no solo en Google Sheets sino también en Google Docs, Gmail, Google Analytics y casi todos los demás servicios de Google Cloud. Le permite automatizar esas aplicaciones individuales e integrar cada una de esas aplicaciones entre sí.

Esto abre la ventana del editor de script y el valor predeterminado a una función llamada myFunction (). Aquí es donde puedes crear y probar tu script de Google.

Para darle una toma, intente crear una función de script de hojas de Google que leerá datos de una celda, realizará un cálculo y emitirá la cantidad de datos a otra celda.

La función para obtener datos de una celda es el getRange () y GetValue () funciones. 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 script se verá así:

function myFunction () var lámina = stillsheetApp.getActivesheet (); var fila = 2; var col = 1; VAR data = hoja.GetRange (fila, col).getValue (); 

Esto almacena el valor de esa celda en el datos variable. Puede realizar un cálculo de los datos y luego escribir esos datos a otra celda. Entonces, la última parte de esta función será:

 resultados var = datos * 100; hoja.GetRange (fila, col+1).setValue (resultados); 

Cuando haya terminado de escribir su función, seleccione el icono de disco para guardar. 

La primera vez que ejecuta una nueva función de script de Google Sheets como esta (seleccionando el icono Ejecutar), deberá proporcionar autorización para que el script ejecute en su cuenta de Google.

Permitir que continúen los permisos. Una vez que se ejecute su script, verá que el script escribió los resultados del cálculo en la celda de destino.

Ahora que sabe cómo escribir una función básica de script de Google Apps, echemos un vistazo a algunas funciones más avanzadas.

Use GetValues ​​para cargar matrices

Puede tomar el concepto de hacer cálculos sobre los datos en su hoja de cálculo con secuencias de comandos a un nuevo nivel utilizando matrices. Si carga una variable en el script de Google Apps usando getValues, la variable será una matriz que puede cargar múltiples valores desde la hoja.

function myFunction () var lámina = stillsheetApp.getActivesheet (); VAR data = 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 para bucle. El contador del bucle for funcionará a través de cada fila, y la columna permanece constante, según la columna donde desea 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.

para (var i = 1; i < data.length; i++)  var result = data[i][0] * 100; sheet.getRange(i+1, 2).setValue(result);   

Guardar y ejecutar este script tal como lo hizo arriba. Verá que todos los resultados se llenan en la columna 2 en su hoja de cálculo.

Notará que hacer referencia a una celda y una fila en una variable de matriz es diferente a una función GetRange. 

datos [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 (ya que la fila 1 es el encabezado), y 2 es la segunda columna donde se almacenan los resultados.

Use Appendrow para escribir resultados

¿Qué pasa si tiene una hoja de cálculo donde desea escribir datos en una nueva fila en lugar de una nueva columna??

Esto es fácil de hacer con el anexo función. Esta función no molestará a los datos existentes en la hoja. Solo agregará una nueva fila a la hoja existente.

Como ejemplo, realizar una función que contará de 1 a 10 y muestre un mostrador con múltiplos de 2 en un Encimera columna.

Esta función se vería así:

function myFunction () var lámina = stillsheetApp.getActivesheet (); para (var i = 1; i<11; i++)  var result = i * 2; sheet.appendRow([i,result]);  

Aquí están los resultados cuando ejecuta esta función.

Procese RSS Feeds con URLFetchApp

Podrías combinar la función anterior de script de Google Sheets y la Urlfetchapp Para extraer el feed 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 alimentación RSS!

El guión para hacer esto tampoco es demasiado complicado.

function myFunction () var lámina = stillsheetApp.getActivesheet (); VAR item, fecha, título, enlace, DESC; var txt = urlfetchapp.buscar ("https: // www.Topsecretwriters.com/rss ").getContentText (); var doc = xml.analizar (txt, falso); título = doc.GetElement ().getElement ("canal").GetElement ("Título").getText (); VAR items = doc.GetElement ().getElement ("canal").getElements ("elemento"); // analizar elementos individuales en la alimentación RSS para (var i en elementos) elementos = elementos [i]; Título = Artículo.GetElement ("Título").getText (); enlace = Artículo.getElement ("enlace").getText (); Fecha = Artículo.GetElement ("Pubdate").getText (); DEC = Artículo.GetElement ("Descripción").getText (); hoja.appendrow ([título, enlace, fecha, descarga]); 

Como se puede ver, Xml.analizar gramaticalmente extrae cada elemento de la alimentación RSS y separa cada línea en el título, enlace, fecha y descripción. 

Utilizando el anexo Función, puede poner estos elementos en columnas apropiadas para cada elemento en el alimento RSS.

La salida en su hoja se verá así:

En lugar de incrustar la URL de alimentación RSS en el script, podría tener un campo en su hoja con la URL y luego tener múltiples hojas, una para cada sitio web que desee monitorear.

Concatenate Strings y agregue un retorno de carro

Puede llevar la hoja de cálculo de RSS un paso más allá agregando algunas funciones de manipulación de texto y luego usar funciones de correo electrónico para enviarse un correo electrónico con un resumen de todas las publicaciones nuevas en el feed RSS del sitio.

Para hacer esto, en el script que creó en la sección anterior, querrá agregar algunos script que extrae toda la información en la hoja de cálculo. 

Desea construir la línea de asunto y el cuerpo de texto de correo electrónico analizando toda la información de la misma matriz de "elementos" que utilizó para escribir los datos RSS en la hoja de cálculo. 

Para hacer esto, inicialice el sujeto y el mensaje colocando las siguientes líneas antes de los "elementos" para bucle.

var sujeto = 'Últimos 10 artículos publicados en MySite.com '
Var Message = "

Luego, al final de los "elementos" para bucle (justo después de la función de apéndice), agregue la siguiente línea.

Mensaje = Mensaje + Título + '\ n' + Link + '\ n' + Date + '\ n' + Desc + '\ n' + '\ n \ n';

El símbolo "+" concatinará los cuatro elementos juntos seguido 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 devoluciones de carro para un cuerpo de correo electrónico bien formateado.

Una vez que se procesan todas las filas, la variable "cuerpo" contiene toda la cadena de mensajes de correo electrónico. Ahora estás listo para enviar el correo electrónico!

Cómo enviar correos electrónicos en el script de Google Apps

La siguiente sección de su script de Google será enviar el "asunto" y el "cuerpo" por correo electrónico. Hacer esto en Google Script es muy fácil.

VAR CORREO ELIMINACIÓN.com;
Mailapp.sendEmail (correo electrónico, asunto, mensaje);

El 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 texto del cuerpo.

Así es como se verá el correo electrónico resultante. 

Combinando la capacidad de extraer el feed RSS de un sitio web, guárdelo en una hoja de Google y envíelo a usted mismo con enlaces de URL incluidos, hace que sea muy conveniente seguir el último contenido para cualquier sitio web.

Este es solo un ejemplo del poder que está disponible en los scripts de Google Apps para automatizar acciones e integrar múltiples servicios en la nube.