5 funciones de script de hojas de cálculo de Google que necesita saber


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 las Hojas de cálculo de Google es la función de creación de scripts de Google que la acompaña.

La creación de scripts de Google Apps es una herramienta de creación de scripts en segundo plano que funciona no solo en hojas de cálculo de Google sino también Google Docs, Gmail , Google analitico y casi cualquier otro servicio 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, crear una secuencia de comandos básica en Hojas de cálculo de Google para leer y escribir datos de celdas y las Hojas de cálculo de Google avanzadas más eficaces funciones de script.

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 Herramientasdel menú, luego Editor de scripts.

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.

In_content_1 all: [300x250] / dfp: [640x360]->

Para intentarlo, intente crear una función de script 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 script 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 ese celda en la variable datos. Puede realizar un cálculo en 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 termine de escribir su función, seleccione el icono del disco para guardar.

La primera vez que ejecuta una nueva función de secuencia de comandos de Hojas de cálculo de Google como esta (al seleccionar el icono de ejecución), deberá proporcionar Autorización para que la secuencia de comandos se ejecute en su cuenta de Google.

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

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 llevar el concepto de hacer cálculos de 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 de la hoja.

function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues();

La variable de datos es multidimensional matriz que contiene todos los datos de la hoja. Para realizar un cálculo en los datos, utiliza un bucle para. El contador del bucle for funcionará en cada fila, y la columna permanece constante, en función de la columna en la que 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.

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 completan 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.

Use appendRow To Write Results

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

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

Como ejemplo, cree una función que cuente de 1 a 10 y muestre un contador con múltiplos de 2 en un Contadorcolumna.

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 script anterior de Hojas de cálculo de Google y la URLFetchApppara 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 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 puede ver, Xml.parseextrae cada elemento de la fuente RSS y separa cada línea en el título, enlace, fecha y descripción.

Usando la función appendRow, puede colocar estos elementos en las columnas apropiadas para cada elemento en la fuente RSS.

El resultado en su hoja se verá algo así:

En su lugar de incrustar la URL de la fuente RSS en la secuencia de comandos, podría tener un campo en su hoja con la URL, y luego tener varias hojas, una 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 la fuente RSS del sitio.

Para hacer esto, bajo 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.

Desea 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 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 de “elementos”.

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

Luego, al final de los "elementos" para el bucle (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 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 "cuerpo" contiene la cadena completa del mensaje de correo electrónico. ¡Ahora está listo para enviar el correo electrónico!

Cómo enviar correos electrónicos 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 .

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 los enlaces URL incluidos, hace que sea muy conveniente seguir el contenido más reciente 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.

Curso de Google Drive. 9. Funciones en las hojas de cálculo de Google.

Artículos Relacionados:


16.01.2020