Una de las mayores razones para optar por buscar ejemplos de scripts en Google Sheets es evitar hacer la misma cosa dos veces, pues los scripts existen para automatizar los procesos que de otro modo se vuelven mecánicos. Tareas simples como ordenar o mover filas de una hoja de cálculo a otra son cosas que toman minutos, con el tiempo se acumulan en horas que se pueden eliminar con las herramientas adecuadas.
La semana pasada hablamos de Google Sheets y su editor de scripts integrado, por lo que ahora toca ver tres scripts que nos serán esenciales si planeamos usar esta herramienta de Google a largo plazo. Para añadir un script nos iremos a pestaña «Herramientas» y seleccionaremos «Editor de secuencias de comandos» para abrir la ventana con el editor de scripts.
Ahora daremos en «Archivo», «Nuevo» y «Script File» para poder empezar a escribir nuestro script.
Añadiendo activadores de proyectos
Para que los scripts funcionen necesitaremos añadir un activador. Los activadores permitirán que los scripts se ejecuten sobre ciertos eventos como lo puede ser abrir la hoja de cálculo, editarla, entre otras cosas.
Para añadir el activador de proyecto, daremos click en el ícono con el reloj, lo cual abrirá una ventana. Desde aquí podemos añadir un nuevo activador y seleccionar cuál función deberá ejecutarse y qué evento causará que la función se ejecute.
Adicional, puedes agregar una imagen o forma a tu hoja de Google Sheets para usarla como activador. Desde el menú Insertar escoge la opción «Dibujo» y crea tu forma.
Una vez creada, presiona los dos botones que aparecen en la forma del lado superior derecho y escoge la opción «Secuencia de comandos», esto permitirá agregar alguna de las secuencias de comandos que veremos a continuación.
1. Auto-ordenar por fecha o prioridad
Este script nos servirá para organizar automáticamente las filas ya sea por prioridad o por fecha de vencimiento, asegurándonos así que las cosas que necesiten mayor prioridad aparezcan siempre al inicio de la hoja de cálculo.
Lo que hace esta función es auto ordenar el rango dado (en este caso ese rango es A2:H30) dentro de la hoja con el nombre especificado (en este caso, el nombre de la hoja es «Hoja de Revisiones») por la columna asignada (siendo 5 en este caso ya que es la columna en dónde están las fechas de vencimiento):
function myFunction(event){
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() == ‘Hoja de Revisiones‘) {
var editedCell = sheet.getActiveCell();
var columnToSortBy = 5;
var tableRange = “A2:H11“; // Rango a acomodar
// columna A = 1, B = 2, etc…
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: true } );
}
}
}
Mover filas a una tabla diferente tras completarse
Nuestro segundo script lo podremos para mover de manera automática filas de una hoja en progreso a una hoja completada.
Respecto a la síntaxis que se muestra a continuación, la función busca a determinada columna en determinada hoja por su nombre (en este caso siendo la columna 7 en la hoja «Hoja de Revisiones») por el valor «Completado» y luego mueve la fila a la hoja «Completadas» cuando ese valor aparezca.
function myFunction() {
// mover una fila a otro libro cuando el status completado aparezca
// puedes cambiar las variables para cambiar acomodarla a tu libro
var sheetNameToWatch = “Hoja de Revisiones“;
var columnNumberToWatch = 7;
// columna A = 1, B = 2, etc…
var valueToWatch = “Completado“;
var sheetNameToMoveTheRowTo = “Completadas“;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();
if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(range.getRow(), 1, 1,sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(range.getRow());
}
}
3. Actualizaciones de email automáticas para nuevas filas
Imaginate un trabajo en equipo, un miembro añade una nueva fila dentro de tu seguimiento en la hoja de cálculo y lo marca como «listo para revisar».
Lo que buscamos aquí es hacer llegar un email generado de forma automática que contendrá: nombre, nombre del cliente, el objeto que se estará revisando, el link a dicho objeto, la fecha límite, notas y el estado.
Esto significa que la función del script buscara por el valor «listo para revisar». Cuando ese valor sea encontrado, añadirá el valor «enviado» a la columna asignada (en este caso la 8) y enviará un email con la información de la fila especificada (la cual se encuentra en la sección que establece la plantilla HTML para la información del mensaje).
function sendEmail() {
//configurando funcion
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (ActiveSheet.getName() == ‘Review Tracker‘) {
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() – StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,7);
var AllValues = WholeRange.getValues();
var message = “”;
//loop sobre los valores
for (i in AllValues) {
//establece fila actual
var CurrentRow = AllValues[i]; if (CurrentRow[6] == “Ready for Review” && CurrentRow[7] != “sent“) {
//revisa si la columna ya fue enviada
// columna A = 1, B = 2, etc…
var EmailSent = CurrentRow[8];
//Si la fila ya ya fue enviada, continua con la siguente
if (EmailSent == “sent“) {
continue;}
//template HTML para envio
message +=
“<p><b>Name: </b>” + CurrentRow[0] + “</p>” +
“<p><b>Client: </b>” + CurrentRow[1] + “</p>” +
“<p><b>Item to Review: </b>” + CurrentRow[2] + “</p>” +
“<p><b>Link to Item: </b>” + CurrentRow[3] + “</p>” +
“<p><b>Deadline for Review: </b>” + CurrentRow[4] + “</p>” +
“<p><b>Notes: </b>” + CurrentRow[5] + “</p>” +
“<p><b>Status: </b>” + CurrentRow[6] + “</p>” +
“</p><br><br>”;
//Fila a buscar
var setRow = parseInt(i) + StartRow;
//Marcar como enviado
// columna A = 1, B = 2, etc…
ActiveSheet.getRange(setRow, 8).setValue(“sent“);
}//si la revision esta lista
}//For loop finalizado
//define a quien enviar el correo
var SendTo = “example@email.com“;
//define el titulo del correo
var Subject = “New Deliverable to Review“;
//envia el mail siempre y cuando no este vacio
if (message) {
MailApp.sendEmail({ to: SendTo, subject: Subject, htmlBody: message, });
}//if message
}//if sheetName Review
}//End FuncUsando varios scripts a la vez
Si nos encontramos trabajando con varios scripts a la vez vamos a necesitar ajustarlos para que todos tengan nombres únicos. En este ejemplo, la función «onEdit» llama a cada función, títuladas «myFunction1» y «myFunction2», por lo cual si tenemos varias funciones con el mismo nombre, Google solo va a tomar en cuenta la última función, a menos que las diferenciemos.
Para verlo en acción, este ejemplo combina las dos primeras funciones que vimos anteriormente en un solo script:
function onEdit(event) {
myFunction1(event);
myFunction2();
}
function myFunction1(event){
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() == ‘Review Tracker’) {
var editedCell = sheet.getActiveCell();
var columnToSortBy = 5;
var tableRange = “A2:H30”; // What to sort
// column A = 1, B = 2, etc…
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: true } );
}
}
}
function myFunction2() {
// moves a row from a sheet to another when a magic value is entered in a column
// adjust the following variables to fit your needs
// see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
var sheetNameToWatch = “Review Tracker”;
var columnNumberToWatch = 7;
// column A = 1, B = 2, etc…
var valueToWatch = “Complete”;
var sheetNameToMoveTheRowTo = “Completed Reviews”;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();
if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(range.getRow());
}
}Automatizar actividades en linea es una de las opciones mas productivas que encontraras en estos tiempos de distancia, por lo que te invito a descubrir mas ejemplos de scripts en Google Sheets y compartir los tuyos con todos nosotros.
Nos leemos abajo.