Como obtener un rango con Google Apps Script


Si estás empezando con Google Apps Script una de las primera cosas que necesitaras es obtener un cierto rango de un spreadsheet mediante Google Apps Script.

Rectifico, si estás empezando tal vez lo primero que necesitas es saber lo que es un rango :). Rápido y fácil: un rango es un array bidimensional de celdas de un spreadsheet. Puede ser una celda sola,  una fila, una columna, o una zona de celdas rectangular ( no puede tener formas "raras" )


Si te preguntas para que sirve obtener un rango la respuesta rápida es por que el rango de celdas contiene ( y permite leer/escribir ) lo que queramos en las celdas: valores, formulas, notas, colores y tipos de fuente, color de fondo, validaciones, etc... Es decir, el rango contiene TODA la información de las celdas que podríamos modificar a mano, extremadamente útil para hacer nuestros scripts.

Ahora bien, como podemos obtener el rango con Google Apps Script? Hay unas cuantas maneras en función de en que situación nos encontremos.

1. Obtener rango de una celda en concreto
function GetCellRange()
{
   var range  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1);
   Logger.log(range.getValue());
}

Con este código obtenemos el rango de la celda que está en la posicion columna=1 y fila=1 (empieza desde 1, no desde 0), es decir, obtendrá el rango de la celda A1. 

Si os resulta más fácil, se podría hacer un poco diferente para que fuera más claro como obtener el rango en lo que se llama "notación A1"

function GetCellRange()
{
   var range  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1");  
   Logger.log(range.getValue());
}

Lo hagas como lo hagas, esta forma de obtener rangos es básico para hacer lecturas y escrituras en un spreadsheet con Google Apps Script


2. Obtener rango de una fila en concreto
function GetRow()
{
   var sheet  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var range  = sheet.getRange(1, 1, 1, sheet.getLastColumn() );
   Logger.log(range.getValues());
}

Todo lo que hacemos es pedir al spreadsheet el rango de alto 1 celda y ancho tanto como datos tenga la hoja de cálculo. 

Usualmente no se usa esta forma para leer filas, sino que se usa lo que explicaré en el punto 5 a fin de después ir leyendo todas las lineas que nos interesan.


3. Obtener rango de una columna en concreto
Obtener el rango de una columna es algo más sencillo de lo que seria obtener una fila, si por ejemplo quisiéramos obtener la fila de datos desde la posición  A1 de tres elementos deberíamos hacer algo de este estilo:
function GetColumnRange()
{
   var range  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 3);
   Logger.log(range.getValues());
}

Si quisiéramos obtener la columna entera solo deberíamos hacer un pequeño cambio en el código:
function GetColumnRange()
{
   var sheet  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var range  = sheet.getRange(1, 1, sheet.getLastRow());
   Logger.log(range.getValues());
}
De esta forma obtendríamos toda la columna hasta donde la hoja de cálculo tuviera valores.


4. Obtener rango de una zona en concreto
function GetData()
{
   var range  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 3,4);
   Logger.log(range.getValues());
}
Con este sencillo código obtendríamos el rango de celdas que estaría comprendido en la celda A1, con un alto de 3 filas y un ancho de 4 columnas.


5. Obtener rango de toda la hoja
function GetAll()
{
   var range  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange();
   Logger.log(range.getValues());
}
Tengo que reconocer que getDataRange es una de mis funciones preferidas, básicamente lo que hace es obtener el rango para todas las celdas que haya sido escritas. desde la posición 1,1 hasta el máximo de columnas y filas que tenga la hoja de cálculo escritas. Muy útil cuando queremos procesar los datos de forma óptima ya se para hacer la lectura o escritura óptima dentro de un spreadsheet.


6. Obtener rango de lo que el usuario ha modificado
function onEdit(e)
{
   var range = e.range;
   Logger.log(range.getValues());
}

Cuando el usuario modifica algo de una celda, se ejecuta el evento onEdit. Como parámetro, recibimos la información de lo que se ha modificado y evidentemente, podemos obtener el rango de dicha información.

Esta funcionalidad puede ser básica cuando queremos controlar lo que nuestro usuario introduce o cuando queremos montar un sistema de timestamp ( por poner ejemplos)


7. Obtener rango de lo que un usuario ha respondido en un formulario
function onFormSubmit(e)
{
   var range = e.range;
   Logger.log(range.getValues());
}

Si os fijáis es lo mismo que el onEdit pero para el caso de cuando un formulario es rellenado. La gran diferencia es que generalmente el usuario modificará solo una celda a la vez mientras que los formularios lo que se modifica es toda una fila de golpe. En ambos casos el código es el mismo dado que lo que recibimos es un rango en las dos situaciones.


8. Obtener rango de lo que otro programa ha modificado en nuestro spreadsheet.
function onChange(e)
{
   var range = e.range;
   Logger.log(range.getValues());
}

La idea es la misma que con el onEdit o el onFormSubmit pero cuando los cambios que se producen en el spreadsheet no vienen ni del usuario ni de un formulario, generalmente, cuando otro software ( en muchas ocasiones Zapier por poner solo un ejemplo ) introduce información en nuestro spreadsheet.

Se os ocurre alguna otra situación en que sea útil y diferente obtener el rango?

Quedo abierto a vuestras dudas y sugerencias.

Nos vemos

NOTA: Si te interesa el tema de los rangos, tal vez te interese como proteger rangos con Google Apps Script

6 comentarios:

  1. Me gustaría saber si me pueden ayudar, me estoy equivocando en algo y no se que es. Estoy extrayendo datos de múltiples hojas, para agregarlo en una de estadísticas generales, pero tengo el problema que el libro del que extrae la información no es el que abrí con el método SpreadsheetApp.openById(), puedo duplicar una hoja del libro correcto, pero cuando quiero ir a copiar una columna de una hoja a otra, extrae información de otros libros.
    Este es el código...
    var sps = SpreadsheetApp.openById("xyz");

    //Extraigo las hojas antes de crear la hoja estadísitica
    var sheet_var = sps.getSheets();
    sps.setActiveSheet(sps.getActiveSheet());
    sps.duplicateActiveSheet();
    sps.getActiveSheet().setName('Estadistica');
    sps.moveActiveSheet(1);
    sps.getRange('F:F').activate();
    sps.getActiveSheet().insertColumnsAfter(sps.getActiveRange().getLastColumn(), 1);
    //Recorro todas las hojas extrayendo la columna indicada
    for (var i=1;i<sheet_var.length;i++){
    //var destino_0 = sheet_var[i].getRange(11, 3, 5, 1);
    var datos_rango = sps.getSheets()[i].getRange(11, 3, 5, 1);
    datos_rango.copyTo(sps.getActiveSheet().getRange(11, 7),SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
    }

    SpreadsheetApp.flush();

    ResponderEliminar
    Respuestas
    1. No lo acabo de ver. Pasame el link al documento(s) y le hecho un vistazo

      Eliminar
    2. Estimado, después de un día revisando y revisando, me di cuenta que no hay problema en el código, La última hoja de mi libro tenía datos erróneos y eran lo que me estaba leyendo. Tengo muchas hojas en el archivo y no las había visto.
      Muchas gracias de todas maneras por tu respuesta.

      Saludos,
      Rodrigo Mercado.

      Eliminar
    3. Perfecto. Me alegro que lo tengas controlado Rodrigo. Yo me lo estuve revisando y no me cuadraba nada, todo estaba "bien". No dudes en hacerme llegar si tienes mas dudas.
      Nos vemos!

      Eliminar
  2. Hola buen día,

    Cómo podría obtener el rango de celdas que editaron por un ctrl v?

    Me explico, cojí una información y la pegué en mi hoja, pero quisiera saber qué celdas fueron las editadas.

    ResponderEliminar
    Respuestas
    1. Creo que lo mejor te serviría sería este artículo: https://www.tutorialesgoogleappscripts.com/2017/07/como-detectar-cambios-en-un-spreadhseet.html , echale un ojo! Nos vemos

      Eliminar

Tal vez te interese