Este artículo te enseñará a utilizar Google Apps Script en Google Sheets. Compartiremos ejemplos de scripts y cómo afectan a las hojas de cálculo. También explicaremos algunos conceptos clave como las funciones personalizadas y los tipos de datos.
¡Vamos a ello!
Principales conclusiones:
- En Google Sheets, Apps Script puede utilizarse para automatizar la introducción de datos, generar informes y limpiar y dar formato a los datos, entre otras cosas.
- Para utilizar Google Apps Script en Google Sheets, ve a Extensión > Apps Script y escribe o pega tu código.
- Los conceptos clave en Apps Script incluyen funciones personalizadas, disparadores, propiedades y métodos, servicios de Apps Script, manejo de errores y bibliotecas.
¿Qué es Google Apps Script?
Google Apps Script es una plataforma basada en la nube que te permite crear tus propios programas para trabajar con tus aplicaciones de Google (como Gmail, Docs y Sheets). Estos programas realizan tareas automáticamente. Pueden enviar correos electrónicos u organizar datos. También pueden añadir nuevas funciones a tus aplicaciones, como menús o botones personalizados.
Google Apps Script utiliza un lenguaje de programación sencillo llamado JavaScript. Sin embargo, no necesitas ser un programador experto para escribirlo.
Así de sencillo funciona Google Apps Scripts:
- Escriba instrucciones sencillas (código) en el editor Apps Script.
- Los servidores de Google leen tus instrucciones y realizan las tareas que deseas.
- Los resultados aparecen en tus aplicaciones de Google. Estos resultados pueden ser nuevos menús, tareas automatizadas o aplicaciones personalizadas.
Ahora, ¿cómo utilizar Apps Script en Google Sheets?
¿Cómo utilizar Google Apps Script en Google Sheets?
- Abre tu hoja de cálculo de Google.
- Haga clic en “Extensiones“ en la barra de menú y seleccione “Script de aplicaciones”. Esto abrirá una nueva pestaña con el editor de Apps Script.
3. Verás Code.gs. Este es el archivo principal donde escribirás tu código JavaScript. Se encuentra en el menú “Editor”.
4. Escriba o pegue su código. Puede que necesites aprender Javascript para saber cómo escribir código, o que puede generar código con Gemini o ChatGPT. Pero aquí está el script que usaremos para este tutorial:
¿Cuál es el escenario detrás de este script? Supongamos que tenemos una lista de contactos en una hoja de Google y queremos utilizarla para una campaña básica de correo electrónico. El script obtendrá todos los datos necesarios, como el correo electrónico y el nombre, para redactar y enviar correos electrónicos.
5. Haga clic en el botón “Guardar”para guardar el script.
6. En la esquina superior izquierda, cambie el nombre del proyecto y haga clic en “Ejecutar.” El script enviará correos electrónicos a todas las direcciones de su hoja.
7. Los scripts de Apps Script requieren autorización para acceder a los datos de Google Sheets. Se te pedirá que concedas estos permisos cuando ejecutes un script.
8. También puede configurar un activador para que el script se ejecute automáticamente según una programación (por ejemplo, diaria o semanal). Para ello, pase el ratón por encima de los iconos de la izquierda y haga clic en “Disparadores.”
9. Haga clic en el botón “+ Añadir activador”.
10. Vaya a la fuente de eventos y seleccione “En función del tiempo.”
11. Seleccione un tipo basado en el tiempo.
- Fecha y hora concretas: Para un disparo único.
- Temporizador de minutos: Ejecutar a un intervalo especificado en minutos (por ejemplo, cada 15 minutos).
- Temporizador horario: Ejecutar a un intervalo especificado en horas (por ejemplo, cada 3 horas).
- Cronómetro de día: Funcionar diariamente a una hora determinada.
- Cronómetro semanal: Correr en días concretos de la semana a una hora concreta.
- Temporizador mensual: Para funcionar en días concretos del mes a una hora determinada.
12. Pulsa “Guardar.” El activador está ahora activo.
Aquí está el correo electrónico que recibí después de ejecutar el script.
Ya está. Ya sabes cómo utilizar Google Apps Script en Google Sheets. Pero vamos a discutir más casos de uso y ver cómo Apps Script mejora las hojas de cálculo.
Ejemplos prácticos de Apps Scripts en acción
1. Automatizar la introducción de datos
Puedes utilizar Google Apps Scripts en Google Sheets para automatizar la introducción de datos.
La introducción manual de datos es propensa a errores tipográficos. Con automatización, De este modo, se reducen las posibilidades de que se produzcan errores humanos. Apps Script puede extraer datos de otras fuentes (como sitios web, formularios o correos electrónicos) e introducirlos automáticamente en la hoja de cálculo.
Digamos que recibes por correo electrónico recibos de gastos de empresa. Apps Script puede escanear esos correos, extraer los números relevantes (fecha, importe, categoría) y organizarlos ordenadamente en tu hoja de cálculo de seguimiento de gastos.
Escribamos y expliquemos un guión:
¿Qué significa este guión?
Suponemos que tiene una etiqueta de Gmail llamada “Recibos” donde almacena los correos electrónicos de gastos. El script recupera las 5 primeras conversaciones no leídas de esa etiqueta. Recorre cada hilo y sus mensajes individuales (correos electrónicos).
A continuación, extrae los detalles del gasto. Tenga en cuenta que hemos simplificado mucho esta parte. El script asume un formato específico para los gastos en el cuerpo del correo electrónico (por ejemplo, “$15.99” y “07/25/2024”).
A continuación, si el script encuentra tanto el importe como la fecha, obtiene la hoja de cálculo activa y añade una nueva fila con los datos extraídos (y el asunto del correo electrónico como referencia).
Por último, tras el procesamiento, el script marca el hilo de correo electrónico como leído para evitar duplicados.
Nota: Nuestro script es simplificado. La exactitud de los datos depende totalmente del formato del correo electrónico y de la capacidad del script para analizarlo correctamente. Además, es posible que tenga columnas adicionales en su rastreador de gastos real (por ejemplo, categoría, método de pago).
2. Generar informes
También puedes utilizar Google Apps Script en Google Sheets para generar informes.
Piensa en esos tediosos informes que tienes que hacer, tal vez resúmenes de ventas, actualizaciones de proyectos o calificaciones de estudiantes. Apps Script puede simplificar este proceso extrayendo los datos de una hoja de Google o de otros lugares.
Digamos que haces un seguimiento de tus ventas semanales en una hoja de Google. Apps Script puede:
- Coge las últimas cifras de ventas de tu hoja.
- Calcule las ventas totales, las ventas por producto e incluso el crecimiento con respecto a la semana pasada.
- Hacer un gráfico circular o gráfico de líneas informe y un resumen.
- Envía el informe por correo electrónico a tu jefe todos los lunes por la mañana.
Lo configuras una vez y Google Apps Script se encarga del resto, semana tras semana.
He aquí un ejemplo de script, que explicaremos a continuación.
function generateSalesReport() {
// Obtener los datos de ventas
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“WeeklySales”);
var data = hoja.getDataRange().getValues();
// Calcular las ventas totales y las ventas por producto
var ventastotal = 0;
var ventasPorProducto = {};
for (var i = 1; i < data.length; i++) { // Saltar fila de cabecera
var product = datos[i][1];
var ventas = datos[i][2];
totalVentas += ventas;
ventasPorProducto[producto] = (ventasPorProducto[producto] || 0) + ventas;
}
// Crear el informe
var informe = “<h2>Informe semanal de ventas</h2>\n\n”;
informe += “<p>Ventas totales: $” + totalSales.toFixed(2) + “</p>\n\n”;
informe += “<h3>Ventas por producto:</h3>\n\n”;
for (var product in ventasPorProducto) {
informe += “<p>” + product + “: $” + salesByProduct[product].toFixed(2) + “</p>\n\n”;
}
// Enviar el correo electrónico
var emailSubject = “Informe semanal de ventas”;
var emailBody = informe;
MailApp.sendEmail(“YOUR_EMAIL_ADDRESS”, emailSubject, “”, {htmlBody: emailBody}); // Incluya su dirección de correo electrónico
}
Lectura recomendada: Las 5 mejores plantillas de ventas de Google Sheets
¿Qué significa este guión?
El script obtiene todos los valores de la hoja “WeeklySales”. Recorre los datos y calcula las ventas totales y las ventas por producto. A continuación, crea un informe HTML con los datos calculados. Por último, el script utiliza MailApp.sendEmail para enviar el informe por correo electrónico.
MailApp.sendEmail
Aquí hay una hoja de Google agrupando todas las ventas:
Este es el aspecto del informe semanal de ventas totales en el correo electrónico:
Nota: Este es un script simplificado. Cambie “WeeklySales” si su hoja tiene un nombre diferente. Sustituya “YOUR_EMAIL_ADDRESS” por la dirección de correo electrónico real del destinatario.
3. Limpiar y formatear los datos
Limpiar y dar formato a los datos es otra forma de utilizar Apps Scripts en Google Sheets.
Imagina que tienes una hoja de cálculo desordenada llena de información sobre clientes. Puede que algunos nombres tengan espacios de más, que las fechas tengan formatos diferentes o que algunas celdas contengan errores tipográficos. Limpiar esto manualmente sería un verdadero dolor de cabeza. Aquí es donde Apps Scripts vienen a salvar el día.
Puedes utilizar las secuencias de comandos de Google Apps para analizar tus datos y encontrar y solucionar problemas específicos, como:
- Espacios extra: Elimina automáticamente los espacios sobrantes antes y después de los nombres.
- Formatos incorrectos: Cambia las fechas de “29/07/2024” a “29 de julio de 2024” para que todo coincida.
- Errores tipográficos: Si el nombre de un producto está mal escrito, el script puede corregirlo.
También puedes utilizar Google Apps Scripts para organizar información y combinar o dividir datos.
Vamos a crear un Javascript para este ejemplo.
Imagine que tiene una hoja de Google con pedidos de clientes. Cada fila contiene información como el ID del pedido, el nombre del cliente, el producto, la cantidad y la fecha del pedido. Desea organizar los pedidos por fecha, con los pedidos más recientes en la parte superior.
Nuestro guión podría ser algo así:
function sortSheetByDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange(“A2:E26”); // Ajuste el rango para cubrir sus datos
// Obtener los datos
var data = range.getValues();
// Comprueba si las fechas están en formato objeto Date y conviértelas en cadenas
data.forEach(function(fila) {
if (Object.prototype.toString.call(row[4]) === ‘[object Date]’) {
fila[4] = Utilities.formatDate(fila[4], SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), ‘dd/MM/aaaa’);
}
});
// Parsear las fechas correctamente y ordenar
data.sort(function(a, b) {
var dateA = new Date(a[4].split(‘/’).reverse().join(‘-‘));
var dateB = new Date(b[4].split(‘/’).reverse().join(‘-‘));
return fechaB - fechaA; // Ordenar de más reciente a más antiguo
});
// Devuelve los datos ordenados a la hoja
range.setValues(datos);
}
¿Qué significa este guión?
El script encuentra el orden de la información que desea clasificar. A continuación, toma cuidadosamente toda la información de cada formulario de pedido - nombre del cliente, fecha del pedido, etc. - y la coloca en celdas separadas. Aquí es donde ocurre la magia. El script mira cada fecha de pedido y encuentra qué pedidos son más recientes y cuáles son más antiguos.
Datos con fechas no ordenadas cronológicamente:
Datos con fechas en orden cronológico después de utilizar el script:
Conceptos esenciales de Apps Script que debes conocer y utilizar en Google Sheets
1. Funciones personalizadas
Las funciones personalizadas permiten crear funciones propias que se pueden utilizar directamente en Google Sheets, al igual que las funciones integradas (SUM, MEDIA, etc.). Estos funciones están escritas en JavaScript dentro de Apps Script y pueden realizar diversas tareas, desde cálculos sencillos hasta complejas manipulaciones de datos.
He aquí un ejemplo de función personalizada:
function DOUBLE(entrada) {
devolver entrada * 2;
}
Esta función personalizada duplica los números de una celda. Después de añadir la función personalizada al Editor de secuencias de comandos, vuelva a la hoja de cálculo.
Digamos que la celda A1 contiene el número 580.
- Escriba =DOUBLE(A1) en la celda B1.
=DOUBLE(A1)
- Google Sheets ejecuta tu función, dándole el valor 1160 en la celda B1.
- La función calcula 580 * 2 = 1160.
- La celda B1 muestra el resultado: 1160.
Diferencia entre una función personalizada y un código general que escribes en Google Apps Script
La principal diferencia entre una función personalizada y cualquier otro código reside en la forma de utilizarlas y llamarlas dentro de Hojas de cálculo de Google.
Un código de función personalizado debe devolver un valor, como:
return entrada * 2;.
Una función no puede modificar ninguna parte de la hoja de cálculo ni desencadenar ninguna otra acción. Sólo puede calcular y devolver un resultado basado en sus entradas.
Además, para utilizar una función personalizada, debe introducir el código =functionName() en una celda en la hoja de cálculo, como:
=DOUBLE(A1)
2. Disparadores
Si quieres utilizar Google Apps Script en Google Sheets, también deberás conocer los activadores.
Imagina que tienes una tarea en Google Sheets que debería realizarse automáticamente, sin que tengas que pulsar ningún botón. Ahí es donde entran en juego los activadores. Un activador es como un pequeño despertador que configuras en el código de tu Apps Script. Indica al script que se despierte y ejecute una parte específica del código cuando se produzca un evento concreto en la hoja de cálculo.
Puede personalizar los desencadenantes para que se ejecuten cuando se abra una hoja, se edite una celda, se envíe un formulario o incluso en una fecha y hora determinadas.
También puedes utilizar disparadores para mantener los datos actualizados. Por ejemplo, un desencadenante podría extraer automáticamente las cotizaciones bursátiles o la información meteorológica más recientes.
3. Propiedades y métodos
Las propiedades y los métodos permiten modificar el funcionamiento de las Hojas de cálculo de Google. Puedes escribir secuencias de comandos que formateen automáticamente las celdas o desplacen los datos. Por ejemplo, en lugar de cambiar manualmente el color de cientos de celdas, tu script puede hacerlo al instante.
Piensa en las propiedades como los rasgos o atributos de un objeto. En Google Sheets, esto podría ser:
- El color de fondo de una celda:
cell.getBackground()
- El valor dentro de una celda:
cell.getValue()
- El tamaño de letra del texto:
cell.getFontSize()
Puede averiguar el valor actual de una propiedad o establecerlo para cambiar su aspecto o comportamiento.
Los métodos, sin embargo, son como los verbos o acciones que un objeto puede realizar. Por ejemplo, una celda en Google Sheets puede:
- Cambia su contenido:
cell.setValue(“¡Hola!”)
- Despejarse completamente:
cell.clearContent()
- Fusionar con celdas vecinas:
cell.mergeAcross()
Se “llama” a un método para que suceda. Es como dar una instrucción.
He aquí un sencillo ejemplo de script:
function formatImportantCells() {
var hoja = SpreadsheetApp.getActiveSheet();
var range = hoja.getRange(“A1:B10”); // Obtener un grupo de celdas
var cells = range.getCells(); // Obtener celdas individuales
for (var i = 0; i < longitud.celdas; i++) {
if (cells[i].getValue() > 100) { // Comprueba el valor
cells[i].setBackground(“yellow”); // Establecer una propiedad (color)
}
}
}
Este script comprueba los valores de las celdas A1 a B10. Si un valor es superior a 100, el script vuelve amarillo el fondo de la celda.
Resumiendo:
- Las propiedades se describen: Te informan sobre el estado actual de las cosas.
- Los métodos sí: Hacen que las cosas sucedan.
- Notación por puntos: Se utiliza un punto para acceder a propiedades y métodos (por ejemplo, cell.getValue()).
cell.getValue()
4. 4. Tipos de datos
Los tipos de datos son otro concepto que debes aprender para utilizar Google Apps Script en Google Sheets.
Los tipos de datos son etiquetas que indican a Apps Script con qué tipo de información está tratando. Del mismo modo que distingues entre manzanas y naranjas, Apps Script necesita saber si está trabajando con números, texto, fechas u otros tipos.
Pero, ¿por qué importan los tipos de datos en las Hojas de cálculo de Google?
Hay al menos tres razones:
- Cálculos correctos: Imagina que estás sumando gastos. Si Apps Script piensa que tus números son en realidad texto, obtendrás resultados extraños. Los tipos de datos garantizan que los cálculos se realicen con precisión.
- Formato adecuado: Quieres que tus fechas parezcan fechas y que tus números de teléfono se comporten como números de teléfono. Los tipos de datos te ayudan a controlar cómo se muestran y utilizan las cosas en tu hoja de cálculo.
- Prevención de errores: Si intentas usar una fecha como si fuera un número, Apps Script podría marcar un error. Conocer los tipos de datos correctos te ayudará a evitar estos contratiempos y a escribir código que funcione sin problemas.
Supongamos que realizas un seguimiento de las ventas y tienes una columna en Google Sheets con el número de artículos vendidos. Cuando se utiliza Apps Script para calcular las ventas totales, tiene que entender que esta columna contiene números, no sólo caracteres aleatorios.
Por ejemplo, el script siguiente menciona el tipo de datos:
if (valueType === “number”) {
He aquí el desglose:
- valorTipo: Esta variable almacena el resultado de typeof value. El operador typeof es una función de JavaScript que indica directamente el tipo de datos de una variable.
- === “número”: Esta comparación comprueba si el valueType es estrictamente igual a la cadena “number”. Si lo es, el script sabe que se trata de un valor numérico y procede a sumarlo al totalVentas.
Acabamos de cubrir algunos conceptos o términos de Apps Scripts. Sin embargo, hay más, como Apps Script Services, Error Handling y Libraries.
Consejos y buenas prácticas para Apps Script
1. Aprender Javascript
Aprender JavaScript te proporciona una base sólida para comprender el funcionamiento de Google Apps Script. Te resultará mucho más fácil descifrar código que encuentres en Internet, solucionar problemas de tus propios proyectos y escribir scripts más avanzados.
Una vez que conozcas los fundamentos de JavaScript, podrás manipular datos de forma realmente inteligente e interactuar con otros servicios de la web.
Además, JavaScript es un lenguaje muy utilizado, por lo que tus nuevos conocimientos no se desperdiciarán. Puedes aplicarlos a otros proyectos más allá de Google Apps Script. Por ejemplo, puedes crear sitios web sencillos o incluso trabajar en proyectos complejos de desarrollo web.
2. Escribir código eficiente y fácil de mantener
Tu código es como una receta, y una buena receta es:
- Eficiente: No desperdicia ingredientes ni tiempo. El código eficiente se ejecuta más rápido y utiliza menos de su cuota de Apps Script (el límite de lo que pueden hacer sus scripts).
- Mantenible: Es fácil de entender y actualizar más adelante. Un código fácil de mantener te ahorrará dolores de cabeza cuando tengas que hacer cambios en el futuro.
Aquí tienes algunos consejos adicionales para Google Apps Script:
- No llames a tus variables “x” o “temp”. Elige nombres que describan lo que almacenan. Por ejemplo, en lugar de “data”, usa “customerNames” o “orderDates”.”
- Divida el código en funciones más pequeñas y reutilizables. Lo ideal es que cada función realice una tarea específica. De este modo, el código estará más organizado y será más fácil de probar.
- Las cosas no siempre salen según lo planeado. Utiliza bloques “try...catch” para capturar errores con elegancia, lo que evita que tu script se bloquee inesperadamente.
- Si te encuentras escribiendo el mismo código varias veces, crea una función para ello. Este principio se denomina “No te repitas” (DRY).
3. Depurar inteligentemente
En Google Apps Script, los bugs son errores o comportamientos inesperados en tu código. La depuración es el proceso de encontrar y corregir estos problemas.
Cuando Apps Script encuentra un problema, normalmente muestra un mensaje de error. Este mensaje es la primera pista. A menudo le indica qué ha fallado. Sabrás si usaste una variable que no existe, dividiste por cero, o algo más. También sabrás dónde ha ido mal, como la línea de código que causa el problema.
Apps Script tiene un depurador incorporado. Es como una versión más potente de “console.log()”. Puedes pausar tu código, recorrerlo línea por línea e inspeccionar variables en tiempo real.
Digamos que tienes este código:
function calcularArea(longitud, anchura) {
var area = largo * ancho;
console.log(“El área es: ” + área);
}
calculateArea(10,); // Observa que falta el argumento para la anchura
Lo ejecutas y se bloquea con un error: “TypeError: No se puede leer la propiedad ‘*’ de undefined”. ¿Qué ocurre?
- Lee el error: Dice que estás intentando utilizar el operador ‘*’ (multiplicación) en algo indefinido.
- Comprueba el código: Te das cuenta de que olvidaste pasar un valor para el argumento “anchura”.
Soluciona el error: Cambia la última línea por:
calcularÁrea(10, 5);
Uso de secuencias de comandos de Google Apps para mejorar Google Sheets
Enhorabuena. Acabas de desbloquear el poder de Apps Script dentro de Google Sheets. En este artículo, hemos visto algunos casos de uso práctico y hemos compartido ejemplos de secuencias de comandos. Ahora estás totalmente equipado para utilizar Google Apps Script en Google Sheets.
Al automatizar tareas, personalizar funciones e incluso crear aplicaciones web interactivas, estás en el buen camino para convertirte en un ninja de las hojas de cálculo. Sigue aprendiendo, sigue creando secuencias de comandos y sigue ampliando las posibilidades de Google Sheets.
PREGUNTAS FRECUENTES
¿Cómo añadir un editor de secuencias de comandos a Google Sheets?
El editor de secuencias de comandos está integrado en Google Sheets. No es necesario añadirlo por separado.
¿Cómo abrir el editor de secuencias de comandos en Google Sheets?
Vaya a Extensiones > Apps Script. Esto abrirá el Editor de Script en una nueva pestaña.
¿Dónde está el editor de secuencias de comandos en Google Sheets?
El editor de secuencias de comandos no se encuentra físicamente dentro de la hoja de cálculo de Google. Se abre en una pestaña o ventana independiente cuando se accede a él a través del menú Extensiones. Ve a Extensiones > Apps Script.
English 
Español 
Português do Brasil 
Français