Exportar datos de Google Sheets a JSON en tiempo real

por | Sep 21, 2021 | Blog, Google Spreadsheets

Exportar Google Sheets a JSON v2 2021

Si eres desarrollador o tienes algo que ver con lenguajes de programación, sabrás que JSON es un estándar muy útil para procesar datos para usar en tus programas. Pero, ¿sabías que puedes usar Google Sheets como una base de datos en línea para programas sencillos? Efectivamente te funcionará como una API de lectura pública gratuita. Si te suena complicado, no temas, pues hoy veremos cómo hacerlo.

Configurar permisos de la hoja de cálculo

Exportar datos de Google Sheets a JSON es fácil, solamente tenemos que saber configurar los permisos de nuestra hoja de cálculo correctamente y modificar un poco la URL para indicarle que queremos el resultado de la hoja en formato JSON.

Lo primero que hay que hacer es ir al menú Archivo – Compartir y asegurarnos de que el permiso de nuestra hoja de cálculo está puesto como Cualquier usuario de Internet puede ver esto

Aquí debemos tener un poco de cuidado si nuestra información es sensible, porque esto hace efectivamente públicos los datos del documento en la web para cualquiera que conozca el identificador del mismo. Los datos de la hoja no se pueden editar con este método, pero si alguien examina la dirección donde los usamos, sí podrían verlos. Si queremos que se mantenga privado, deberemos usar la API de Google Sheets junto con un proyecto separado de Google Cloud Platform para autenticarnos primero. Pero ese no es el método que usaremos ahora, entonces asumamos por ahora que nuestra información es pública y que todo el mundo tiene permiso para verla.

Contruir URL para la salida de JSON de la hoja de cálculo

Ahora viene la parte interesante. Vamos a construir la URL del feed de JSON de nuestro documento de Google Sheets, de la siguiente manera:

https://docs.google.com/spreadsheets/d/ID_DOCUMENTO/gviz/tq?tqx=out:json&gid=ID_HOJA_TRABAJO

Entonces, esto significa que debemos identificar primero dos cosas: el ID de nuestro documento y el ID de la hoja interna de trabajo a la que queremos acceder.

ID_DOCUMENTO es el identificador único de nuestro archivo o documento en Google Drive, por ejemplo, en esta URL es lo que viene después de /d/

https://docs.google.com/spreadsheets/d/1zWX4rL1rVRcdyVVz3V5_nRXIf5ezR8IwzHSOmo/edit#gid=0

ID_HOJA_TRABAJO es el identificador único de la hoja de trabajo interna (es decir, la ‘pestaña’). Es lo que viene después de gid=

https://docs.google.com/spreadsheets/d/1zWX4rL1rVRcdyVVz3V5_nRXIf5ezR8IwzHSOmo/edit#gid=0

En este caso es 0 porque es la primera hoja de cualquier documento, pero para hojas siguientes Google Sheets generará un número único para cada una:

https://docs.google.com/spreadsheets/d/1zWX4rL1rVRcdyVVz3V5_nRXIf5ezR8IwzHSOmo/edit#gid=669149629

Entonces, usando este conocimiento, la URL final para exponer la información de la primera hoja como JSON se verá así:

https://docs.google.com/spreadsheets/d/1zWX4rL1rVRcdyVVz3M4scV5_nRXIf5ezR8IwzHSCOmo/gviz/tq?tqx=out:json&gid=0

Esto nos dará como resultado un archivo json.txt con esta salida:

/O_o/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1814652513","table":{"cols":[{"id":"A","label":"Divisa ","type":"string"},{"id":"B","label":"Valor ","type":"number","pattern":"#,##0.00"}],"rows":[{"c":[{"v":"USD MXN"},{"v":20.1400000000002,"f":"20.14"}]},{"c":[{"v":"BTC MXN"},{"v":814385.390000001,"f":"814,385.39"}]},{"c":[{"v":"ETH MXN"},{"v":55036.81371,"f":"55,036.81"}]},{"c":[null,{"v":44460.777149918976,"f":"44,460.77"}]}],"parsedNumHeaders":2}});

Como podemos ver, ahí está toda la información de la hoja de trabajo de la hoja de cálculo expuesta: encabezados, datos, su formato, etcétera en formato JSON. O casi en formato JSON, para poder usarlo eso es lo siguiente que tenemos que revisar.

Descifrando la salida en JSON de la hoja de cálculo

Lo último será preparar el lado que debe consumir esta información. Puede ser un servidor de node.js, Python o PHP o lo que más nos convenga. Si te fijas bien, la salida tiene algunas cosas que no nos sirven directamente, como el principio del archivo:

/O_o/ google.visualization.Query.setResponse(

Esto es así porque en realidad estamos utilizando una parte de la Google Charts API con un mecanismo de seguridad aplicado llamado JSONP y esta es la respuesta predefinida por la misma. Pero eso no significa que no podamos aprovechar la parte que sí tiene JSON bien formateado dentro.

Pongamos un ejemplo. Si fuéramos a consumir esto desde PHP, lo único que tendríamos que hacer es quitar los primeros 47 caracteres al inicio y 2 caracteres del final:

$json = substr($content, 47,-2);

Eso nos permitirá tener la salida en JSON bien formateado, ahora sí.

{"version":"0.6","reqId":"0","status":"ok","sig":"1814652513",
	"table":
	{"cols":
		[
			{"id":"A","label":"Divisa ","type":"string"},
			{"id":"B","label":"Valor ","type":"number","pattern":"#,##0.00"}
		],
	"rows":
		[
			{"c":[{"v":"USD MXN"},{"v":20.1400000000002,"f":"20.14"}]},
			{"c":[{"v":"BTC MXN"},{"v":814385.390000001,"f":"814,385.39"}]},
			{"c":[{"v":"ETH MXN"},{"v":55036.81371,"f":"55,036.81"}]},
			{"c":[null,{"v":44460.777149918976,"f":"44,460.77"}]}
		],
	"parsedNumHeaders":2}}

De aquí en adelante, lo que resta es simplemente usar la información como mejor nos parezca. La forma en que están estructurados los datos es a modo de tablas, entonces si tienes pedazos de información en tu hoja, el JSON que se generará, estará en pequeños grupos, cada uno en una tabla diferente.

{
    "version": "0.6",
    "reqId": "0",
    "status": "ok",
    "sig": "1814652513",
    "table":
    {
        "cols":
        [
            {
                "id": "A",
                "label": "Divisa ",
                "type": "string"
            },
            {
                "id": "B",
                "label": "Valor ",
                "type": "number",
                "pattern": "#,##0.00"
            }
        ],
        "rows":
        [
            {
                "c":
                [
                    {
                        "v": "USD MXN"
                    },
                    {
                        "v": 20.1400000000002,
                        "f": "20.14"
                    }
                ]
            },
            {
                "c":
                [
                    {
                        "v": "BTC MXN"
                    },
                    {
                        "v": 814385.390000001,
                        "f": "814,385.39"
                    }
                ]
            },
            {
                "c":
                [
                    {
                        "v": "ETH MXN"
                    },
                    {
                        "v": 55036.81371,
                        "f": "55,036.81"
                    }
                ]
            },
            {
                "c":
                [
                    null,
                    {
                        "v": 44460.777149918976,
                        "f": "44,460.77"
                    }
                ]
            }
        ],
        "parsedNumHeaders": 2
    }
}

Exportar Google Sheets a CSV o a HTML en tiempo real

Antes de irnos, te comparto un pequeño bonus que permite este mismo método. Si en vez de json cambiamos el parámetro de salida out: a csv o html, obtendremos los datos de nuestra hoja de cálculo en cualquiera de esos dos formatos.

Convertir a formato CSV:

https://docs.google.com/spreadsheets/d/ID_DOCUMENTO/gviz/tq?tqx=out:csv&gid=0

Convertir a HTML:

https://docs.google.com/spreadsheets/d/ID_DOCUMENTO/gviz/tq?tqx=out:html&gid=0

Puedes revisar más información sobre la API que controla estos métodos en la documentación oficial de Google Charts API.

Nota API anterior

El método anterior para extraer la información en JSON ya no es válido, pues la API asociada ha sido dada debaja definitivamente.

Es decir, el método que utilizaba este formato ya no funcionará:

https://spreadsheets.google.com/feeds/TIPO_DE_FEED/SPREADSHEET_ID/WORKSHEET_ID/public/values?alt=json

¡Y listo! Ahora ya sabes cómo exportar una hoja de cálculo de Google Sheets a JSON en tiempo real. Esos datos los puedes usar en tu app, SDK o código fácilmente. Así, puedes mantener una base de datos que sea fácil de editar directamente en el documento y ver tus cambios inmediatamente en otra aplicación. Si necesitas algo más robusto, como hacer consultas estilo SQL, deberías ya pensar en usar una base de datos tradicional.

Puedes encontrar más información sobre este tema en la documentación oficial de Google Charts

Guía en video

Si tienes dudas puedes ver esta misma guía explicada en video:

¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.

Aprende Sheets: Curso práctico de Hojas de cálculo de Google
Aprende Sheets: Curso práctico de Hojas de cálculo de Google

2 Comentarios

  1. 360tours
    360tours el febrero 20, 2021 a las 2:09 am

    ¿Sigue siendo vigente esta entrada?

    Responder
  2. Gustavo Delucchi
    Gustavo Delucchi el agosto 2, 2024 a las 2:57 pm

    Cómo podría bajar sólo una fila según el valor de la columna A en csv?, desde ya muchas gracias. Excelente tu trabajo.Saludos

    Responder

Deja un comentario