Te doy la bienvenida a un nuevo episodio de mi serie "Uso y abuso de herramientas que están pensadas para otra cosa" 😜
Hoy te voy a contar cómo puedes montar en un minuto un "backend" de pruebas para cualquier desarrollo utilizando tan solo una hoja de cálculo de Google Drive (Google Sheets).
Imagina que tienes que crear un prototipo para un cliente y que no tienes un backend público montado contra el que trabajar para obtener datos. O quizá quieres mostrar una serie de datos en tu web que pueden cambiar con cierta frecuencia y no se justifica montar una base de datos para mantenerlos... Puede haber muchos casos por el estilo.
Existen diversas maneras de crear un backend de prueba: desde herramientas online específicas con capas gratuitas, proyectos open source (pero que luego tienes que poner a andar en algún lado), etc... Pero pocas cosas hay tan sencillas para guardar datos como una simple hoja de cálculo ¿verdad?
Google Sheets es el software de hoja de cálculo de Google que viene incluido con Google Drive, incluso en su versión gratuita con cualquier cuenta de GMail. Es súper sencilla de usar y cualquier cambio que hagamos se ve reflejado instantáneamente. Podemos colaborar con ellas y exportarlas a diferentes formatos.
Sin embargo, una cosa que no es muy común es utilizarla como una especie de base de datos sencilla para leer datos en aplicaciones, para cosas como las que comentaba antes.
Vamos a ver cómo lograrlo de manera sencilla. Pero antes de nada...
Crea la hoja de cálculo
Pues eso: vete a Google Drive y crea una nueva hoja:
Rellénala con los datos que necesites, por ejemplo, esta es la mía en la que he metido información sobre algunos cursos de campusMVP, para probar:
Fíjate en varias cosas importantes:
- Cada fila será un registro
- Para usarlos como JSON y poder darle nombre a las propiedades, en la primera fila debe llevar los nombres que quieras darle a los campos del objeto JSON que se va a devolver. Por lo tanto procura que sean nombres válidos para JavaScript (funcionarán igual en otro caso, pero no podrías acceder a ellos en el formato
objeto.nombrePropiedad, sino en el formato objeto["nombrePropiedad"], que no es tan cómodo).
- Puedes tener varias hojas con datos dentro del archivo (varias pestañas en la parte de abajo). Luego veremos cómo acceder a cada una de ellas.
Método 1: Usar la versión 3 de la API de Google Sheets
IMPORTANTE: a partir del 3 de marzo de 2020 este método deja de funcionar porque Google abandona la versión 3 de la API. Lee el método 2. Lo dejo por completitud, ya que estaba en el original.
La versión 3 de la API de Google Sheets está marcada como obsoleta, pero sigue funcionando perfectamente. Lo malo es que quizá en el futuro dejen de soportarla y no puedas utilizarla más, pero hoy por hoy no hay problema.
¿Por qué te digo entonces de utilizarla? Pues porque no necesitas hacer nada especial para poder acceder a los datos: ni autenticación, ni claves de API, ni nada. Es el método más sencillo.
Como veremos tiene alguna pega más, pero antes vamos a ver cómo hacerlo...
1.1.- Publicar la hoja en la Web
Este es un paso indispensable. Si los datos van a ser consumidos a través de Internet tienen que estar disponibles de manera pública.
Muy importante: para publicarla de la manera adecuada debes ir al menú Archivo y elegir la opción de Publicar en la Web. Es decir, no es el menú convencional de compartir que tienen todos los archivos de Google Drive, sino que es el menú especial de GSheets para publicar en la web, que es completamente diferente.
Como se ve en esta animación (tengo la interfaz en inglés, pero en español será idéntica), puedes publicar solo una hoja concreta o todo el libro, y es muy importante que te asegures de que está marcada la opción de republicar automáticamente cuando se hagan cambios:
Al darle al botón de Publicar obtendremos una página en la que podremos copiar la dirección pública de la hoja de calculo publicada:
Por ejemplo, esta es la mía.
Si pulsas en el enlace anterior verás que es una versión estática de solo lectura de los contenidos de la hoja de cálculo.
El caso es que así no nos sirve todavía para nuestro propósito, ya que está en un formato no utilizable.
1.2.- Construir la URL "mágica"
Para acceder a los datos en formato JSON sólo necesitarás una dirección especialmente construida de la siguiente forma:
https://spreadsheets.google.com/feeds/cells/ID-HOJA-CALCULO/1/public/full?alt=json
siendo ID-HOJA-CALCULO el identificador de tu hoja de cálculo en la barra de direcciones.
OJO: no es el identificador que te sale al compartirla en la Web, de la figura anterior, sino el identificador de la barra de direcciones cuando la estás editando, este:
En el caso de mi hoja de cálculo de ejemplo es, por tanto, esta dirección:
https://spreadsheets.google.com/feeds/cells/1qi9Y2APUjP95DeUu9gWrdYE8Gfk6XGBz-3Ul4wezOaU/1/public/full?alt=json
Importante: esto devolverá tan sólo la primera de las hojas del archivo. Si tienes más de una puedes acceder a cualquiera de ellas individualmente cambiando en la dirección anterior el /1/ que va después del ID de tu hoja de cálculo por un /2/ y así sucesivamente.
Y este es el aspecto del JSON devuelto en Firefox, que tiene un visor de JSON integrado:
Como puedes observar, el JSOn generado es un poco "especial". En lugar de generar una matriz de objetos con los valores de las filas en forma de propiedades o de matrices anidadas, genera un "churro" con cantidad de propiedades y nombres raros. De todos modos si lo observas con cuidado verás que es relativamente fácil de procesar y que, con un poco de maña podemos generar una matriz de objetos a partir de los datos recibidos.
En concreto existe una matriz de elementos llamada entry (sí, así en singular, aunque es una matriz) con muchas propiedades para cada elemento. Una de ellas, gs$cell, tiene los datos de la celda en cuestión. Los que nos interesan aquí son row, col y $t, que nos permiten saber la fila, la columna y el valor que hay en cada una de las celdas de la hoja:
Con este conocimiento y un poco de lógica en un bucle podemos recorrerlas para sacar, en primer lugar, los nombres de los campos, y en segundo, crear tantos objetos como filas, con propiedades llamadas como esos campos.
Un código rápido como prueba de concepto para hacerlo con fetch te lo dejo en un enlace al final de este artículo. En el ejemplo que te dejo para descargar se genera una matriz de objetos y se muestra por consola con console.table, mostrando esto:
Como ves los muestra en formato de tabla y también ves la matriz JSON generada, con un objeto por elemento y cada uno de estos objetos con una propiedad que se corresponde con los nombres establecidos en la primera fila de la hoja de cálculo. Muy fácil de utilizar.
En una aplicación real usarías los datos para algo, claro, pero los tienes ya en una matriz de objetos, listos para utilizar con facilidad.
Las mayores pegas de este método, por tanto son:
- El JSON es muy lioso y hay que escribir código para manipularlo. Te dejo un ejemplo sencillo para descarga al final, que te lo da hecho.
- Tiene un pequeño problema de privacidad, pues en el medio de todo ese desbarajuste de datos que devuelve va el nombre y el email de la persona que ha creado la hoja de cálculo.
- Como la API está marcada como obsoleta, puede que deje de funcionar en el futuro, pero como no necesitas autenticarte ni usar claves de API, es muy cómodo de usar.
Método 2: Usar la versión 4 (y actual) de la API de Google Sheets
Vale, este sería el método recomendado. Solo tiene una pega: necesitas una clave de la API de Google para poder utilizarlo. Es gratuita y sencilla de obtener, pero ya tienes que llevar a cabo un paso más, por eso te doy también la idea de usar el método anterior.
Paso 1.- Crear un enlace público para la hoja de cálculo
Si vas a usar este método tienes que compartir la hoja de cálculo de la forma habitual. Es decir, no es necesario que la publiques en la web como antes, sino que debes compartirla de la manera habitual usando el botón de Compartir de la hoja de cálculo y eligiendo el modo Link sharing - On o Public, así:
Una vez hecho esto ya puedes proceder al siguiente paso.
Paso 2.- Habilitar el acceso desde la API de Google Sheets
Para poder usar la API de Google Sheets tienes que acceder a la consola de desarrolladores de Google. Una vez allí crea un nuevo proyecto pulsando sobre el desplegable al lado del logo de Google APIs:
Esto abrirá un diálogo de selección de proyectos. Pulsa en el botón arriba a la derecha para crear un nuevo proyecto:
Dale un nombre lo suficientemente claro para saber para qué lo vas a usar y pulsa en Crear:
Al cabo de un rato verás una notificación diciéndote que ya ha sido creado. Pulsa sobre ella:
Eso te lleva a la página principal (dashboard) del proyecto. En la parte central tienes un recuadro para las APIs. Deberemos activar el uso de la API de Google Sheets, así que pulsa sobre Ir al resumen de las APIs o algo similar que pondrá en español:
En la siguiente pantalla verás que no hay nada, porque todavía no tienes ninguna API activada. Dale al botón Habilitar APIs y servicios de la parte superior:
que te llevará al buscador de APIs. Sólo tienes que escribir Sheets en el recuadro de búsqueda para que te aparezca la API que nos interesa:
Al pulsar sobre ese resultado te lleva a la página de información de la API de Google Sheets:
Lo único que debes hacer es pulsar sobre el botón Habilitar. Esto habilitará la API y te dejará en el "dashboard" de gestión de esta API, pero ya no tenemos que hacer nada más aquí.
Paso 3.- Crear unas credenciales para acceso a la API (API Key)
Ya casi estamos. No desesperes. Ahora debes acceder al menú de gestión de credenciales de API, que está un poco escondido en un submenú del menú del lateral: Menú haburguesa > APIs y Servicios > Credenciales:
Al entrar en la gestión de credenciales y no tener ninguna te muestra un diálogo desde el que puedes crear diferentes tipos de credenciales para ofrecer acceso a las APIs que estamos gestionando. En este caso necesitamos la opción Clave de API o API key que es la primera:
Al darle a la opción te genera una directamente:
Podríamos utilizarla ya así, pero lo mejor es que la protejamos un poco restringiéndola solo al caso de uso que nos interese, tal y como sugiere el diálogo anterior. Pulsa en Restringir clave, lo cual te llevará a un nuevo diálogo de gestión de la clave:
Aprovecha para ponerle un nombre decente a la clave y luego utiliza los controles de abajo para restringir el acceso. Puedes restringir el acceso tan solo a unos dominios (de modo que solo se pueda acceder a esos datos desde tu página web, por ejemplo) y también que sólo tenga acceso a la API de Google Sheets y no a todas las APIs que puedas añadir más tarde para hacer otras cosas. Es muy sencillo, pero te lo muestro en una animación:
¡Listo! Con esto tenemos la clave creada y suficientemente restringida.
Paso 3.- Crear la URL "mágica"
Al igual que en la versión 3 de la API exuste una YRl que te permite leer directamente el contenido de la hoja de cálculo como JSON. En este caso el JSON está mucho mejor construido y no tienes los problemas del método anterior. Pero para llegar aquí ya ves que hemos tenido que dar algunas vueltas más.
La URL que debes utilizar es esta:
https://sheets.googleapis.com/v4/spreadsheets/ID-HOJA-CALCULO/values/RANGO?key=CLAVE-API
siendo:
ID-HOJA-CALCULO: el identificador de la hoja de cálculo, igual que en el paso anterior, sacado de la propia dirección de la misma.RANGO: el rango de datos que queremos sacar. En este documento tienes información sobre rangos, pero básicamente un rango se define de la siguiente manera: NombreHoja|Celda1:Celda2. Es decir, en nuestro ejemplo, si queremos tener los datos de la primera hoja (que he llamado Cursos, entre las celdas A1 y E6), sería: Cursos!A1:E6. Del mismo modo, si queremos todos los datos que haya en las columnas de la A a la E, podemos poner: Cursos!A:E, y así si en el futuro añades más filas aparecerán automáticamente en el resultado.CLAVE-API: es la clave de la API que acabamos de generar. No te preocupes porque sea pública: sólo tendrá acceso de lectura a las hojas que hayas puesto públicas y además tendrían que saberse el identificador, así que no hay problema.
Así, por ejemplo, en nuestra hoja de datos de cursos la dirección sería:
https://sheets.googleapis.com/v4/spreadsheets/1qi9Y2APUjP95DeUu9gWrdYE8Gfk6XGBz-3Ul4wezOaU/values/Cursos!A:E?key=AIzaSyAGO61yfSo9U4Pfh9g3F3s-RIVku06yWR4
En este caso el JSON que se devuelve es mucho más compacto y fácil de usar, con una simple matriz de matrices:
Nota: si al acceder obtienes este error:
es que no has publicado correctamente la hoja de cálculo en el paso 1.1 de este método.
Así que ahora el bucle será parecido al anterior pero más directo al ser una simple matriz cuyos elementos son a su vez otras matrices. En este caso pasamos la matriz obtenida a una matriz de objetos JSON listos para usar por nombre:
El resultado es igual al de antes, pero con este método obtenemos varias ventajas:
- No corremos el riesgo de que nos deje tirados en el medio plazo, porque es la API oficial actual de Google para hacer estas cosas.
- El procesamiento de los datos es mucho más sencillo y directo.
- Compartir la hoja es más sencillo, pues sólo tenemos que usar el método habitual de compartir, y los datos se refrescan en tiempo real (con el método 1, cualquier cambio puede llegar a tardar unos 5 minutos en verse en el JSON, debido a la caché que hace la versión 3 de la API).
Como puntos negativos, solo uno:
- Hay que crear un proyecto y una clave de la API en la consola de desarrolladores de Google.
En resumen
Aunque desde luego no es lo que recomendaría para crear una API real, sí que es cierto que con las técnicas aquí explicadas es muy sencillo crear un "backend" de manera muy simple que sirve JSON a una o varias aplicaciones Web que nos interesen olvidándonos de servidores y con datos tabulares sencillos. Puede ser más que suficiente para pruebas de concepto y prototipos, datos sencillos para graficar o utilizar en una Web, e incluso para dar soporte a ciertas cosas en un sitio web estático creado con Jekyll u otro sistema similar (por ejemplo comentarios si no recibes muchos: recibes los comentarios por email desde un formulario y los que quieras publicar los añades a mano a una hoja de cálculo, atándola a través de un campo con la URL del post).
Te dejo aquí la prueba de concepto con los dos métodos (ZIP, 2.19KB).
¡Espero que te resulte útil!
💪🏻 ¿Este post te ha ayudado?, ¿has aprendido algo nuevo?
Pues NO te pido que me invites a un café... Te pido algo más fácil y mucho mejor
