Una guía para principiantes de Google Sheets Script

Cómo

PorRyan Dube

Ultima actualización en6 de agosto de 2020

Si usa Google Sheets, puede interesarle saber que Google Scripts puede mejorar en gran medida sus hojas de cálculo.

Piense en Google Scripts como VBA en Microsoft Excel. Le permite agregar código a su hoja de cálculo para automatizar la entrada de datos. También puede usarlo para traer datos externos a sus hojas. Lo mejor de todo es que puede ejecutar cálculos en una programación periódica, incluso cuando no tiene la hoja de cálculo abierta.

En este artículo, aprenderá cómo habilitar Google Scripts y cómo escribir y ejecutar un script simple.

Cómo utilizar los scripts de Google

Hay algunas cosas en Google Sheets que debe habilitar antes de poder comenzar a escribir Google Scripts allí.

Seleccione el menú Herramientas y seleccione Editor de guiones del menú.

Esto abrirá el editor de Google Scripts en una ventana de proyecto sin título.

Este script está contenido dentro de su cuenta de Google dentro de la herramienta Google Scripts. Este proyecto en particular está conectado a la hoja de cálculo en la que está trabajando actualmente.

Puede escribir una secuencia de comandos, pero no puede ejecutarla hasta que las secuencias de comandos estén realmente habilitadas en la hoja. Deberá hacer esto la primera vez que lo ejecute (como verá a continuación).

Para ver esto en acción, hagamos un guión simple.

Crea tu primer script de Google

En este ejemplo, va a escribir un guión que sume todos los puntajes del examen y del examen final para la columna B (puntajes de Chris).

Esta es la hoja de cálculo de ejemplo.

De vuelta en la ventana de Google Scripts, pegue el siguiente código dentro de las llaves de la función myFunction (), así:


function myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheets () [0] rango var = sheet.getRange (2,2,5);
valores var = range.getValues ​​();
var sum = 0
para (var fila en valores) {
para (var col en valores [fila]) {
suma = valores [fila] [col] + suma;
}
}
sheet.getRange (7,2) .setValue (suma);
}


Este código puede parecer complicado, pero analicemos exactamente lo que está haciendo. Es más simple de lo que parece.

Analicemos este código una línea a la vez.

Definiendo las Variables

1. var ss = SpreadsheetApp.getActiveSpreadsheet ();

Esta línea recupera la hoja de cálculo actual que está activa. Dado que solo está ejecutando esta secuencia de comandos cuando la hoja de cálculo en la que desea ejecutar el cálculo está activa, siempre obtendrá la hoja de cálculo correcta. La hoja se guarda como una variable de "objeto" llamada "ss".

2. var sheet = ss.getSheets () [0]

Esto obtiene la primera hoja y la guarda como una variable de objeto llamada hoja. Existe una función alternativa que puede utilizar para obtener la hoja por el nombre de la hoja.

Eso sería var hoja = ss.getSheetByName ("Hoja1");

Puede utilizar la función que desee.

3. rango var = sheet.getRange (2,2,5);

Esta función obtiene un rango de valores por fila, columna y número de filas. En este caso, extraemos todos los valores comenzando en la segunda fila, segunda columna y obteniendo los valores cinco filas hacia abajo.

La variable "rango" se convierte en lo que se conoce como una "matriz". Una matriz es una variable que contiene varios valores. En Google Script, se refiere a cualquiera de esos números usando un esquema de direccionamiento de elementos especiales con corchetes, como verá a continuación.

4. valores var = range.getValues ​​();

La función getValues ​​() en realidad extrae los valores del objeto de rango que definió anteriormente.

5. var sum = 0

Esta es solo una variable numérica que estamos inicializando con el número 0. Esto se convierte en el comienzo de cómo vamos a resumir todos los números de la columna.

Crear los bucles For

6. para (var fila en valores) {

Esto es lo que se llama un bucle "For". Este tipo de bucle recorre un número específico de elementos en una lista. En este caso, pasará por la matriz de "valores" que discutimos anteriormente. Este primer ciclo recorrerá todas las filas de la matriz. En este caso solo habrá uno.

Nota: las filas de la matriz no son lo mismo que las filas de la hoja de cálculo. Cuando la matriz cargó los valores de la columna en la hoja de cálculo, los colocó en una sola línea (o fila) de datos, así: "89, 92, 78, 98, 89". Entonces, en este caso, la matriz tiene una línea (fila) y cinco números en (columnas).

7. para (var col en valores [fila]) {

Este segundo bucle for pasará por todas las "filas" de la matriz. En este caso, recorrerá los 5 valores.

8. suma = valores [fila] [col] + suma;

Cada vez que se ejecuta el ciclo, esta declaración seguirá sumando la suma del nuevo valor a sí misma. Para cuando ocurran los 5 bucles, los 5 números deben totalizarse en la variable "suma".

9 sheet.getRange (7,2) .setValue (suma);

Esta es una declaración de dos funciones. La función getRange apunta a la celda en la séptima fila y la segunda columna. La función setValue luego coloca el valor de la variable "suma" en esa celda.

Ahora que comprende cómo funciona cada línea del código, es hora de verlo todo en acción.

Guardar su script de Google

Una vez que haya terminado de escribir su secuencia de comandos de Google, es hora de guardarla y recorrer el código. Primero, escriba "Proyecto sin título" para darle un nombre a su proyecto. Luego, seleccione el ícono del disquete en el menú para guardar el script.

Es posible que vea un error cuando intente guardar. Que no cunda el pánico. En realidad, esto es parte del proceso de depuración. Preste mucha atención al número de línea donde dice que tiene un error. Mire de cerca su código y resuelva cualquier error tipográfico o de sintaxis que pueda tener.

En este ejemplo, a mi bucle "for" en la línea 8 le faltaba el símbolo de apertura "(". Una vez arreglado, intente guardar de nuevo. Una vez que pueda guardar su código, Google considera que la sintaxis de su código está bien.

Esto no significa que su código se ejecutará según lo previsto, solo significa que se puede "compilar" bien, sin problemas de sintaxis.

Ejecución de su secuencia de comandos de Google

Ahora seleccione el icono de reproducción en la parte superior para recorrer su código. Dado que esta es la primera vez que ejecuta su nueva secuencia de comandos, debe proporcionar a la aplicación Google Script permiso para acceder a su hoja de cálculo (datos) en su cuenta de Google. Seleccione Revisar permisos en la ventana emergente.

Deberá iniciar sesión en su cuenta de Google. Y como se trata de una aplicación personalizada y no registrada en Google, verá una advertencia de que la aplicación no está verificada. Selecciona el Avanzado enlace y luego seleccione Ir al ejemplo de secuencia de comandos de Google (no seguro) continuar.

En la siguiente pantalla, deberá seleccionar Permitir para dar acceso a Google Script a su cuenta de Google. Una vez que lo haga, se ejecutará el script.

Si no ve ningún error, vuelva a la hoja de cálculo y verá que los resultados de su secuencia de comandos han aparecido en la parte inferior de la primera columna.

Escribir scripts avanzados de Google

Este era un script de Google muy básico que simplemente sumaba las celdas en una sola columna. Si desea agregar variables a los dos bucles For en el código, también puede recorrer todas las columnas y proporcionar un resumen en la fila 7 para todas ellas.

Google Scripts ofrece todo tipo de posibilidades, como enviar correo electrónico desde una hoja de cálculo. Incluso puedes aprovechar API especiales de Google que le permiten integrar otras aplicaciones de Google como Gmail, Calendar e incluso Google Analytics. Una vez que comience a aprender a escribir scripts de Google, las posibilidades son infinitas.