Una de las cosas más interesantes que tiene Google Apps Script, es la posibilidad de conectar con nuestras hojas de cálculo como si se tratara de una base de datos. Esto es muy útil para poder crear nuestras propias aplicaciones web, por ejemplo si necesitamos hacer una aplicación pequeña, y evitar contratar un hosting.

Para ello vamos a empezar con nuestro proyecto del restaurant. Entramos a Google drive y creamos una hoja de cálculos haciendo click en el botón Nuevo / Hojas de cálculo de Google / Hoja de cálculo en blanco.

image by fernando-gaitan.com.ar

Le ponemos nombre y creamos dos hojas. Una se va a llamar «Menu» y la otra: «Categorias»

image by fernando-gaitan.com.ar

También es importante que copiemos la URL de nuestra hoja de cálculos, que tiene un aspecto como éste:

https://docs.google.com/spreadsheets/d/[ID-DE-TU-HOJA-DE-CALCULOS]/edit#gid=0

Objeto SpreadsheetApp

Ahora tenemos que conectar nuestra aplicación con la hoja de cálculos, para eso vamos a nuestro archivo GAS y hacemos lo siguiente:

const SS = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/[ID-DE-TU-HOJA-DE-CALCULOS]/edit#gid=0");

function doGet() {
  let layout = HtmlService.createTemplateFromFile("layout");
  return layout.evaluate();
}

Vale aclarar que dentro del método openByUrl() tenemos que pasar como parámetro la URL de nuestra hoja de cálculos.

Para continuar vamos a hacer un nuevo deploy yendo a Publicar / Implementar como aplicación web y donde dice «Project version» poner «Nuevo». Hacemos click en «Actualizar».

image by fernando-gaitan.com.ar

Notamos que aparece una ventana avisando que hay que darle permiso a esta acción. Así que pulsamos en el botón: «Revisar permisos».

image by fernando-gaitan.com.ar

Seleccionamos la cuenta con la que queremos dar permisos.

image by fernando-gaitan.com.ar

Seleccionamos «Configuración avanzada»

image by fernando-gaitan.com.ar

Vamos a «nombre de nuestro proyecto (no seguro)»

image by fernando-gaitan.com.ar

Por último pulsamos en el botón: «Permitir»

image by fernando-gaitan.com.ar

Mostrando información de la hoja de cálculos

Entramos en nuestra hoja de cálculos y llenamos las celdas de «Categorías» con algunos ítem. Por ejemplo:

image by fernando-gaitan.com.ar

Método getSheetByName()

Como vimos antes, nos conectábamos a nuestra hoja de cálculos pasándole la URL de la misma, de la siguiente forma:

const SS = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/[ID-DE-TU-HOJA-DE-CALCULOS]/edit#gid=0");

Ese objeto tiene un método llamado getSheetByName(), el cual nos permite seleccionar con qué hoja vamos a trabajar. En este caso: «Categorias»

Volvamos a nuestro archivo .gs y creamos una función que nos va a devolver las categorías que creamos en la hoja con el mismo nombre:

function getCategorias(){

  let ws = SS.getSheetByName("Categorias");

  let data = ws.getRange("A2:A11").getValues();

  Logger.log(data);

  return data;

}

Método getRange()

Primero usamos el método getSheetByName() al cual tenemos que pasar como parámetro el nombre de la página, como ya expliqué.

Luego llamamos al método getRange() que nos permite seleccionar el rango de celdas que queremos recuperar. Aunque esto no es muy útil y tendremos que cambiarlo, ya veremos por qué.

Y finalmente como cualquier función podemos retornar el resultado con la palabra reservada return.

Ejecuciones y pruebas

Logger.log() nos sirve a los desarrolladores para hacer un seguimiento, por ejemplo si quisiéramos saber el contenido de una variable. Tenemos que pasarle como parámetro al método log() la información que queremos seguir.

Para ello vamos al menú: Ejecutar / Ejecutar función y finalmente en la que acabamos de crear: getCategorias()

image by fernando-gaitan.com.ar

Para ver el resultado vamos al menú: Ver / ejecuciones. Nos mostrará por pantalla todas las acciones que se van realizando. Y al haber usado el método log(), podremos ver el resultado:

image by fernando-gaitan.com.ar

image by fernando-gaitan.com.ar

Vemos que el método recupera los registros de la hoja de cálculos en forma de array.

Siempre que queramos verificar que algo funcione correctamente, podemos hacer pruebas como está.

Método getRange()

Para recuperar los datos de las categorías usamos la siguiente línea:

let data = ws.getRange("A2:A11").getValues();

Al método getRange() le pasamos como parámetro el conjunto de celdas que queremos ver. Sin embargo el problema está cuando no sabemos el volumen de la información.

Por ejemplo si agregamos una categoría nueva sería en la celda A12, y no la podríamos ver porque el rango llega hasta el A11.

Para eso vamos a cambiar esa línea por lo siguiente:

let data = ws.getRange(
    2,
    1,
    ws.getLastRow() - 1,
    1
).getValues();

Como vemos, el método getRange() tiene varias formas de pasarle parámetros.

En este caso los dos primeros parámetros van a definir las coordenadas de la celda donde queremos empezar 2, 1 Osea a partir de la celda A2. El motivo por el cual no empezamos por el 1 (1, 1) es porque la primer fila de la hoja de cálculos se usa para poner el nombre de la columna.

El tercer parámetro es el motivo por el cual cambiamos la forma de llamar a ese método. Sucede que este parámetro define la cantidad de filas (de arriba hacia abajo) que queremos recuperar, pero esta hoja no tiene un tamaño definido, puede tener un registro, dos, tres, diez, cien o más.

Por tanto usamos el método getLastRow() que nos devuelve la totalidad de filas. El -1 (menos uno) es para que no cuente una de más, recordemos que la primer fila la omitimos porque como dije antes, se usa para la cabecera.

El cuarto parámetro funciona igual que el anterior, sólo que para las columnas (de izquierda a derecha), una sola. Si en cambio no supiésemos cuánto va a crecer de forma horizontal esta hoja, entonces podríamos usar el método: getLastColumn().

Nuestro código quedará finalmente de este modo:

const SS = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/[ID-DE-TU-HOJA-DE-CALCULOS]/edit#gid=0");

function doGet() {
  let layout = HtmlService.createTemplateFromFile("layout");
  return layout.evaluate();
}

function getCategorias(){

  let ws = SS.getSheetByName("Categorias");

  let data = ws.getRange(
    2,
    1,
    ws.getLastRow() - 1,
    1
  ).getValues();

  Logger.log(data);

  return data;

}