5 funciones de secuencia de comandos de Google Sheets que necesitas saber

Hola geek ! Aquí Lautaro con un nuevo post para ustedes !
Espero que les sirva y nos vemos en el próximo tutorial

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.

La secuencia de comandos de Google Apps es una herramienta de secuencia 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 empezar a utilizar secuencias de comandos de Google Apps, cómo crear una secuencia de comandos básica en Google Sheets para leer y escribir datos de celda y las funciones de secuencia de comandos de Google Sheets avanzadas más eficaces.

Cómo crear un script de Google Apps

Puede comenzar ahora mismo a crear su primer script de Google Apps desde dentro de Google Sheets.

Para hacer esto, seleccione Herramientas en el menú, luego Editor de guiones.

Esto abre la ventana del editor de scripts y por defecto una función llamada myfunction (). Aquí es donde puede crear y probar su Google Script.

Para darle una oportunidad, intente crear una función de secuencia de comandos de Google Sheets que leerá los datos de una celda, realizará un cálculo en ella y enviará la cantidad de datos a otra celda.

La función para obtener datos de una celda es la 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 secuencia de comandos se verá así:

function myFunction() 
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();

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

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

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 Google Sheets como esta (al seleccionar el icono de ejecución), deberá proporcionar la 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 básica de secuencia de comandos de Google Apps, echemos un vistazo a algunas funciones más avanzadas.

Utilice getValues ​​para cargar matrices

Puede llevar el concepto de realizar cálculos sobre los datos de 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 sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

La variable de datos es una matriz multidimensional que contiene todos los datos de la hoja. Para realizar un cálculo sobre los datos, utilice un para lazo. El contador del bucle for funcionará en cada fila y la columna permanece 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 < data.length; i++) 
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   

Guarde y ejecute este script tal como lo hizo anteriormente. Verá que todos los resultados se rellenan en la columna 2 de su hoja de cálculo.

Notará que hacer referencia a una celda y fila en una variable de matriz es diferente que con 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.

Utilice 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 en una nueva columna?

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

Como ejemplo, crea una función que cuente del 1 al 10 y muestre un contador con múltiplos de 2 en un Mostrador columna.

Esta función se vería así:

function myFunction() 
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) 
      var result = i * 2;
     sheet.appendRow([i,result]);
   

Estos son los resultados cuando ejecuta esta función.

Procesar fuentes RSS con URLFetchApp

Puede combinar la función de secuencia de comandos de Google Sheets anterior y la 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 de lector de feeds RSS!

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

function myFunction() 
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) 
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);

Como puedes ver, Xml.parse saca cada elemento de la fuente RSS y separa cada línea en el título, enlace, fecha y descripción.

Utilizando el appendRow función, puede colocar estos elementos en las columnas adecuadas para cada elemento en 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, una para cada sitio web que desea 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 la fuente RSS del sitio.

Para hacer esto, debajo de 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á construir la línea de asunto y el cuerpo del texto del correo electrónico analizando juntos 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 asunto y el mensaje colocando las siguientes líneas antes del bucle For “items”.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Luego, al final de los «elementos» para el ciclo (justo después de la función appendRow), agregue la siguiente línea.

message = message + title + 'n' + link + 'n' + date + 'n' + desc + 'n' + 'n n';

El símbolo «+» concatenará 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 retornos de carro para un cuerpo de correo electrónico con un formato agradable.

Una vez que se procesan todas las filas, la variable «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 emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

MailApp es una clase muy conveniente dentro de los scripts de Google Apps que le da 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.

La combinación de la capacidad de extraer el feed RSS de un sitio web, almacenarlo en una hoja 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 los scripts de Google Apps para automatizar acciones e integrar múltiples servicios en la nube.

Deja un comentario