Cómo usar la API de Google Sheets con Nodejs

En primer lugar, una breve descripción de nuestro caso de uso. Digamos que tengo una hoja de cálculo en Hojas de cálculo de Google que no es pública y quiero poder leer/modificar mediante programación a través de algún proceso por lotes que se ejecuta en mi máquina local o en algún servidor. Esto es algo que tuve que hacer recientemente con una aplicación Node.js y encontré la parte de autenticación un poco difícil de entender. Así que pensé en compartir mi solución y espero que ayude a alguien que lo necesite. Puede haber mejores maneras de hacer esto, pero estoy compartiendo lo que funcionó mejor para mí.

Dado que no hay interacción del usuario involucrada en nuestro caso de uso, no queremos usar el proceso OAuth donde el usuario necesita abrir un navegador e iniciar sesión en su cuenta de Google para autorizar la aplicación. Para escenarios como este, Google tiene un concepto de cuenta de servicio . Una cuenta de servicio es un tipo especial de cuenta de Google destinada a representar a un usuario no humano que necesita autenticarse y estar autorizado para acceder a los datos en las API de Google.

Al igual que una cuenta normal, una cuenta de servicio también tiene una dirección de correo electrónico (aunque no tiene un buzón de correo real y no puede enviar correos electrónicos a una cuenta de correo electrónico de servicio). Y al igual que puede compartir una hoja de Google con un usuario usando su dirección de correo electrónico, también puede compartir una hoja de Google con una cuenta de servicio usando su dirección de correo electrónico. Y esto es exactamente lo que vamos a hacer en este tutorial.

Crearemos una hoja de cálculo en Hojas de cálculo de Google con un usuario normal, la compartiremos con una cuenta de servicio (que crearemos) y usaremos las credenciales de la cuenta de servicio en nuestro script de Node.js para leer y modificar esa hoja.

requisitos previos

Este tutorial asume que usted tiene:

• Experiencia trabajando con Node.js
• Una cuenta de Google
• Una configuración de proyecto en la consola de desarrolladores de Google donde tiene privilegios de administrador

Resumen de pasos

Aquí está la lista de pasos que seguiremos a través de este tutorial:

• Crear una hoja de cálculo en las hojas de Google
• Habilite la API de Google Sheets en nuestro proyecto en la consola de desarrolladores de Google
• Crear una cuenta de servicio
• Comparta la hoja de cálculo creada en el paso 1 con la cuenta de servicio creada en el paso 3
• Escriba un servicio de Node.js para acceder a las hojas de Google creadas en el paso 1 usando las credenciales de la cuenta de servicio
• Pruebe nuestro servicio escrito en el paso 5

Ahora que tenemos un esquema de lo que vamos a hacer, comencemos.

Paso 1: crea una hoja de cálculo en Hojas de cálculo de Google

Este realmente no necesita ninguna instrucción. Solo necesita iniciar sesión en su cuenta de Google, abrir Google Drive y crear una nueva hoja de Google. Puedes poner algunos datos aleatorios en él. Una cosa de la que debemos tomar nota es la identificación de la hoja.

Cuando tenga la hoja abierta en su navegador, la URL se verá así: https://docs.google.com/spreadsheets/d/1-XXXXXXXXXXXXXXXXXXXSgGTwY/edit#gid=0 .

Y en esta URL, 1-XXXXXXXXXXXXXXXXXX SgGTwY es la identificación de la hoja de cálculo y será diferente para cada hoja de cálculo. Tome nota porque lo necesitaremos en nuestro script Node.js para acceder a esta hoja de cálculo. Para este tutorial, aquí están los datos que hemos almacenado en nuestra hoja de cálculo:

Paso 2: habilite la API de Google Sheets en nuestro proyecto en la consola de desarrolladores de Google

Necesitamos habilitar la API de Hojas de cálculo de Google para nuestro proyecto para poder usarla. Este tutorial asume que ya tiene un proyecto en la consola de desarrolladores de Google, por lo que si no tiene uno, puede crear uno nuevo muy fácilmente. Una vez que tenga el proyecto en la consola de desarrolladores de Google, abra el panel del proyecto. Allí debería ver un botón Habilitar API y servicios .

Haga clic en él y busque la API de hojas de Google usando la barra de búsqueda. Una vez que lo vea, haga clic en él y luego haga clic en Habilitar

Paso 3: crea una cuenta de servicio

Una vez que habilite la API de Hojas de cálculo de Google en su proyecto, verá la página donde puede configurar los ajustes para esta API. Haga clic en la pestaña Credenciales en la barra lateral izquierda. Aquí verá una lista de ID de clientes y cuentas de servicio de OAuth. Por defecto no debería haber ninguno.

Haga clic en el botón Crear credenciales en la parte superior y seleccione la opción Cuenta de servicio

Ingrese el nombre y la descripción de la cuenta de servicio y haga clic en el botón Crear .

Haga clic en Continuar en el siguiente cuadro de diálogo

En el siguiente cuadro de diálogo, obtiene una opción para crear una clave. Este es un paso importante. Haga clic en el botón Crear clave y elija JSON como formato. Esto le pedirá que descargue el archivo JSON en su máquina local.

Para este tutorial, cambié el nombre del archivo y lo guardé como service_account_credentials.json en mi máquina local.

Guárdelo en un lugar seguro. Este archivo clave contiene las credenciales de la cuenta de servicio que necesitamos en nuestro script de Node.js para acceder a nuestra hoja de cálculo desde Hojas de cálculo de Google.

Una vez que haya seguido todos estos pasos, debería ver la cuenta de servicio recién creada en la página de credenciales.

Tome nota de la dirección de correo electrónico de la cuenta de servicio. Tendremos que compartir nuestra hoja de cálculo con esta cuenta.

Paso 4: Comparta la hoja de cálculo creada en el paso 1 con la cuenta de servicio creada en el paso 3

Ahora que tenemos una cuenta de servicio, debemos compartir nuestra hoja de cálculo con ella. Es como compartir una hoja de cálculo con cualquier cuenta de usuario normal. Abra la hoja de cálculo en su navegador y haga clic en el botón Compartir en la esquina superior derecha. Eso abrirá un modal donde deberá ingresar la dirección de correo electrónico de la cuenta de servicio. Desmarque la casilla de verificación para Notificar a las personas, ya que esto enviará un correo electrónico y dado que la cuenta de servicio no tiene ningún buzón de correo, le dará una notificación de falla en la entrega del correo.

Haga clic en el botón _Aceptar_ para compartir la hoja de cálculo con la cuenta de servicio.

Esto completa todos los pasos de configuración. Ahora podemos llegar a la parte divertida :-)

Paso 5: escriba un servicio de Node.js para acceder a la hoja de Google usando las credenciales de la cuenta de servicio

Crearemos nuestro script como un servicio que se puede usar como parte de un proyecto más grande. Lo llamaremos googleSheetsService.js . Expondrá las siguientes API:

• getAuthToken
• obtener hoja de cálculo
• obtener valores de hoja de cálculo

La función getAuthToken es donde manejaremos la autenticación y devolverá un token. Luego usaremos ese token y lo pasaremos a otros métodos.

No cubriremos la escritura de datos en la hoja de cálculo, pero una vez que tenga la idea básica de cómo usar la API, será fácil ampliar el servicio para agregar más y más funciones compatibles con la API de Hojas de cálculo de Google.

Usaremos el módulo googleapis npm. Entonces, comencemos creando un directorio para este proyecto de demostración. Llamémoslo google-sheets-demo .

 cd $HOME mkdir google-sheets-demo cd google-sheets-demo

Copie el archivo service_account_credentials.json que creamos en el paso 3 a este directorio ( google-sheets-demo ). Y crea nuestro nuevo archivo googleSheetsService.js .

Pegue las siguientes líneas en el archivo:

 // googleSheetsService.js const { google } = require ( 'googleapis' ) const SCOPES = [ 'https://www.googleapis.com/auth/spreadsheets' ] async function getAuthToken ( ) { const auth = new google.auth.GoogleAuth({ scopes : SCOPES }); const authToken = await auth.getClient(); return authToken; } module .exports = { getAuthToken, }

Por ahora, nuestro servicio solo tiene una función que devuelve el token de autenticación. Agregaremos otra función getSpreadSheet pronto. Primero veamos qué hace nuestra función.

Primero, requerimos el módulo googleapis npm. Luego definimos SCOPES . Cuando creamos un token de autenticación usando las API de Google, existe un concepto de alcance que determina el nivel de acceso que tiene nuestro cliente.

Para leer y editar hojas de cálculo, necesitamos acceso al alcance https://www.googleapis.com/auth/spreadsheets .

De manera similar, si solo tuviéramos que dar acceso de solo lectura a las hojas de cálculo, habríamos usado el alcance https://www.googleapis.com/auth/spreadsheets.readonly .

Dentro de la función getAuthToken , estamos llamando al constructor new google.auth.GoogleAuth pasando los ámbitos en el objeto de argumentos.

Esta función espera que estén disponibles dos variables de entorno, GCLOUD_PROJECT , que es el ID del proyecto de la consola de desarrollo de Google, y GOOGLE_APPLICATION_CREDENTIALS, que indica la ruta del archivo que contiene las credenciales de la cuenta de servicio.

Tendremos que configurar estas variables de entorno desde la línea de comandos. Para obtener el ID del proyecto, puede obtenerlo de la URL del proyecto cuando lo abre en su navegador web. Debe tener un aspecto como este

https://console.cloud.google.com/home/dashboard?project={ID del proyecto}

Y GOOGLE_APPLICATION_CREDENTIALS debe contener la ruta del archivo service_account_credentials.json . Entonces, vaya a la terminal y desde el directorio google-sheets-demo , ejecute los siguientes comandos para configurar estas variables de entorno:

 export GCLOUD_PROJECT={project ID of your google project} export GOOGLE_APPLICATION_CREDENTIALS =./service_account_credentials.json

Debe asegurarse de tener el archivo de credenciales copiado en el directorio actual.

Ahora añadiremos dos funciones más a nuestro servicio:

• obtener hoja de cálculo
• obtener valores de hoja de cálculo

El primero devolverá metadatos sobre la hoja de cálculo, mientras que el segundo devolverá los datos dentro de la hoja de cálculo. Nuestro archivo googleSheetsService.js modificado debería verse así:

 // googleSheetsService.js const { google } = require ( 'googleapis' ); const sheets = google.sheets( 'v4' ); const SCOPES = [ 'https://www.googleapis.com/auth/spreadsheets' ]; async function getAuthToken ( ) { const auth = new google.auth.GoogleAuth({ scopes : SCOPES }); const authToken = await auth.getClient(); return authToken; } async function getSpreadSheet ( {spreadsheetId, auth} ) { const res = await sheets.spreadsheets.get({ spreadsheetId, auth, }); return res; } async function getSpreadSheetValues ( {spreadsheetId, auth, sheetName} ) { const res = await sheets.spreadsheets.values.get({ spreadsheetId, auth, range : sheetName }); return res; } module .exports = { getAuthToken, getSpreadSheet, getSpreadSheetValues }

En la parte superior hemos añadido una línea

 const sheets = google.sheets( 'v4' );

Esto es para usar la API de hojas. Luego hemos agregado las dos nuevas funciones getSpreadSheet y getSpreadSheetValues . Para ver todos los puntos de conexión de la API compatibles con la API de Hojas de cálculo de Google, consulte este enlace

https://developers.google.com/sheets/api/reference/rest

Para nuestra demostración, solo estamos usando dos de esos. La función getSpreadSheet espera el token de autenticación y el ID de hoja de cálculo como sus parámetros. Y getSpreadSheetValues espera un parámetro adicional que es el nombre de hoja desde el que obtener los datos.

De forma predeterminada, una hoja de cálculo solo contiene una sola hoja y se denomina Hoja1 . Finalmente, exportamos las funciones recién agregadas a través de module.exports .

Esto completa nuestro googleSheetsService . Si necesita admitir más funciones API, puede verificar la referencia usando el enlace anterior, agregar las funciones de contenedor correspondientes en este servicio y exportarlo usando module.exports .

Para cualquier consumidor de este servicio, primero deberá llamar a la función getAuthToken() para obtener el token de autenticación y luego pasar ese token a las funciones posteriores como getSpreadSheet() , getSpreadSheetValues() , etc.

Ahora que tenemos nuestro servicio listo, solo necesitamos probarlo para asegurarnos de que funciona bien.

Paso 6: prueba nuestro servicio

Así que tenemos nuestro servicio listo. ¿Pero funciona? Comprobemos eso.

Si bien normalmente usaríamos un marco de prueba para ejecutar pruebas unitarias, para simplificar este tutorial, escribiremos un script simple de Node.js. Desde el directorio de nuestro proyecto, cree un nuevo archivo llamado test.js y copie y pegue los siguientes contenidos:

 const { getAuthToken, getSpreadSheet, getSpreadSheetValues } = require ( './googleSheetsService.js' ); const spreadsheetId = process.argv[ 2 ]; const sheetName = process.argv[ 3 ]; async function testGetSpreadSheet ( ) { try { const auth = await getAuthToken(); const response = await getSpreadSheet({ spreadsheetId, auth }) console .log( 'output for getSpreadSheet' , JSON .stringify(response.data, null , 2 )); } catch (error) { console .log(error.message, error.stack); } } async function testGetSpreadSheetValues ( ) { try { const auth = await getAuthToken(); const response = await getSpreadSheetValues({ spreadsheetId, sheetName, auth }) console .log( 'output for getSpreadSheetValues' , JSON .stringify(response.data, null , 2 )); } catch (error) { console .log(error.message, error.stack); } } function main ( ) { testGetSpreadSheet(); testGetSpreadSheetValues(); } main()

Este archivo contiene dos funciones de prueba y una función principal que llama a esas funciones de prueba. En la parte inferior del archivo, estamos ejecutando la función principal . Este script espera dos argumentos de línea de comando:

• spreadsheetId (este es el ID que obtuvimos del paso 1)
• sheetName (este es el nombre de la hoja de cálculo para la que desea ver los valores. Cuando crea una nueva hoja de cálculo, es Sheet1 )

Además, asegúrese de que las variables de entorno GCLOUD_PROJECT y GOOGLE_APPLICATION_CREDENTIALS estén configuradas correctamente.

Ahora, desde la terminal, ejecute este script

 node test .js <your google sheet's spreadsheet id> <sheet name of the worksheet>

Si ha seguido todos los pasos correctamente, debería ver un resultado como este:

 output for getSpreadSheet { "spreadsheetId" : "1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY" , "properties" : { "title" : "test-sheet" , "locale" : "en_US" , "autoRecalc" : "ON_CHANGE" , "timeZone" : "Asia/Calcutta" , "defaultFormat" : { "backgroundColor" : { "red" : 1 , "green" : 1 , "blue" : 1 }, "padding" : { "top" : 2 , "right" : 3 , "bottom" : 2 , "left" : 3 }, "verticalAlignment" : "BOTTOM" , "wrapStrategy" : "OVERFLOW_CELL" , "textFormat" : { "foregroundColor" : {}, "fontFamily" : "arial,sans,sans-serif" , "fontSize" : 10 , "bold" : false , "italic" : false , "strikethrough" : false , "underline" : false } } }, "sheets" : [ { "properties" : { "sheetId" : 0 , "title" : "Sheet1" , "index" : 0 , "sheetType" : "GRID" , "gridProperties" : { "rowCount" : 1000 , "columnCount" : 26 } } } ], "spreadsheetUrl" : "https://docs.google.com/spreadsheets/d/1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY/edit" } output for getSpreadSheetValues { "range" : "Sheet1!A1:Z1000" , "majorDimension" : "ROWS" , "values" : [ [ "Name" , "Country" , "Age" ], [ "John" , "England" , "30" ], [ "Jane" , "Scotland" , "23" ], [ "Bob" , "USA" , "45" ], [ "Alice" , "India" , "33" ] ] }

Si obtiene un error, significa que no ha seguido todos los pasos correctamente. Para este tutorial, la versión del módulo npm de googleapis era 43.0.0 . Es posible que tenga problemas si está utilizando una versión anterior del módulo. Asegúrese de que el ID de la hoja de cálculo y el nombre de la hoja sean correctos y que las variables de entorno estén configuradas correctamente. Si aún recibe el error, debe verificar el mensaje de error y el código para ver qué podría estar causando el problema.

Referencias

• Documentación para el cliente Google API Node.js. https://github.com/googleapis/google-api-nodejs-cliente
• Referencia oficial de la API de Hojas de cálculo de Google. https://developers.google.com/sheets/api/reference/rest

Definitivamente recomendaría consultar estas referencias (especialmente la referencia oficial de la API de Google Sheets) para obtener una comprensión más profunda de la API de hojas y cómo usar el cliente Node.js.

Espero que hayas encontrado útil este tutorial. Gracias y feliz codificación :-)

Publicado anteriormente en http://codingfundas.com/how-to-read-edit-google-sheets-using-node-js/index.html