Cloud Function para automatizar la importación de datos CSV a Hojas de cálculo de Google

1. Introducción

El objetivo de este codelab es que comprendas cómo escribir una Cloud Function para reaccionar a la carga de un archivo CSV en Cloud Storage, leer su contenido y usarlo para actualizar una hoja de cálculo de Google con la API de Sheets.

Esto se puede considerar como la automatización de un paso manual de "importar como CSV". Esto garantizaría que puedas analizar los datos (quizás producidos por otro equipo) en una hoja de cálculo tan pronto como estén disponibles.

Así se ve la implementación :

2. Configuración y requisitos

Configuración del entorno de autoaprendizaje

1. Accede a la consola de Cloud y crea un proyecto nuevo o reutiliza uno existente. (Si todavía no tienes una cuenta de Gmail o de G Suite, debes crear una).

Nota: Para acceder a la consola de Cloud con facilidad, memoriza su URL, que es console.cloud.google.com.

Recuerde el ID de proyecto, un nombre único en todos los proyectos de Google Cloud (el nombre anterior ya se encuentra en uso y no lo podrá usar). Se mencionará más adelante en este codelab como PROJECT_ID.

Nota: Si usas una cuenta de Gmail, puedes configurar la ubicación predeterminada como Sin organización. Si usas una cuenta de G Suite, elige una ubicación que sea adecuada para tu organización.

2. A continuación, deberás habilitar la facturación en la consola de Cloud para usar los recursos de Google Cloud recursos.

Ejecutar este codelab no debería costar mucho, tal vez nada. Asegúrate de seguir las instrucciones de la sección “Realiza una limpieza”, en la que se aconseja cómo cerrar recursos para que no se te facture más allá de este instructivo. Los usuarios nuevos de Google Cloud son aptos para participar en el programa Prueba gratuita de $300.

3. Crea y configura una hoja de cálculo de Google y habilita la API

Primero, creemos un nuevo documento de Hojas de cálculo (esta hoja puede pertenecer a cualquier usuario). Una vez creado, recuerda su identificador, ya que se usará como una variable de entorno para la función que escribiremos :

En la consola de GCP, habilita la API de Google Sheets en tu proyecto recién creado. Para ello, navega a la sección "APIs y servicios" y, luego, a "Biblioteca de APIs":

En la sección "IAM y administración", navega a "Cuentas de servicio" y anota el correo electrónico de la cuenta de servicio predeterminada de App Engine. Debe tener el formato [e-mail]. Por supuesto, también puedes crear tu propia cuenta de servicio dedicada a esta acción.

Por último, simplemente otorga privilegios de edición a esta cuenta de servicio en tu hoja de cálculo con el botón "Compartir":

Con esta configuración, ahora podemos escribir nuestra Cloud Function y configurarla para que use esta cuenta de servicio. Podrá escribir en el documento de hoja de cálculo que acabamos de crear.

4. Crea un bucket de almacenamiento

Creemos el bucket que supervisará nuestra Cloud Function en busca de archivos CSV nuevos.

En la consola, usa el menú de la izquierda para navegar a "Storage"… :

… y crea un bucket nuevo llamado csv2sheet-POSTFIX (reemplaza POSTFIX por algo único) con todos los demás parámetros de configuración establecidos en sus valores predeterminados :

Nota: Puedes elegir tu propia ubicación, tipo de ubicación y clase de almacenamiento. Esto no debería afectar el resto del codelab. En caso de duda, mantén los valores predeterminados.

5. Crea la Cloud Function

Ahora podemos crear una función de Cloud Functions llamada csv2sheet que se active cuando se suban archivos a un bucket de Cloud Storage específico. El código se escribirá en Node.js 8 con funciones asíncronas usando el editor directo en Cloud Console :

Asegúrate de configurar el activador en "Cloud Storage" y de ajustar el nombre del bucket al que creaste en el paso anterior.

También actualiza el punto de entrada de la función que estamos a punto de escribir a csv2sheet :

Ahora, cambia el cuerpo de la función a lo siguiente :

1. usar las APIs de Cloud Storage y Sheets
2. Marca la función csv2sheet como async.
3. obtener el fileName de los metadatos del evento de Cloud Storage y derivar un nombre para la nueva hoja que crearemos :

const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

El uso de async aquí es obligatorio para usar await, como veremos en un momento.

Algunas opciones importantes al crear esta función incluyen las siguientes (haz clic en el vínculo "Más" en la parte inferior de la pantalla) :

• Usa el menú desplegable para seleccionar la cuenta de servicio que se mencionó anteriormente.
• Define una variable de entorno llamada SPREADSHEET_ID que debe coincidir con el documento de hoja de cálculo que creaste anteriormente :

Como paso final de configuración, aquí se muestra el contenido de package.json con las APIs de Cloud Storage y de Hojas de cálculo de Google como las dos dependencias que usaremos (usa la pestaña PACKAGE.JSON del editor intercalado de la consola) :

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

Una vez que hayas configurado todo como se describió, haz clic en "Crear". Después de un minuto, se debería crear e implementar tu función.

Nota: Todo el código adicional que se analice en este lab deberá agregarse a esta única función de Cloud que acabamos de crear.

6. Configura la autenticación y la API de Hojas de cálculo

Antes de escribir más código en nuestra función de Cloud con el editor integrado, debemos bloquear la creación de una API de cliente de Google con los permisos de Storage y Sheet adecuados (recuerda que esto forma parte de una función async).

En el editor de funciones de la consola, haz clic en "EDIT" y agrega el siguiente código al cuerpo de la función csv2sheet :

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

Desde allí, podemos crear un cliente de la API de Sheets :

const sheetsAPI = google.sheets({version: 'v4', auth});

7. Usa la API de Sheets para crear una hoja vacía

Con un cliente de la API de Sheets, podemos crear una nueva hoja simple en nuestro documento, pero, antes de continuar, aquí tienes una nota rápida sobre el vocabulario:

• Una hoja de cálculo es el documento real y se hace referencia a ella por su identificador (que se analizó anteriormente y que se puede ver en la URL del documento).
• Una hoja es una de las pestañas del documento y se puede hacer referencia a ella por su nombre (el nombre de la pestaña) o por un identificador generado cuando se crea la hoja.

Teniendo esto en cuenta, aquí tienes una función que usa el cliente de la API de Sheets para crear una hoja vacía en la posición 2 (por lo general, después de la "Hoja1" predeterminada), con 26 columnas y 2,000 filas, y con la primera fila inmovilizada (agrégala a tu función con el editor intercalado) :

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

Observa que, en lugar de codificar la referencia a la hoja de cálculo, usamos la variable de entorno SPREADSHEET_ID creada anteriormente.

Debemos recordar el sheetId para las solicitudes posteriores que se realicen a esta hoja en particular. Además, el nombre de la hoja debe ser único, y la creación fallará si ya existe una hoja llamada sheetName.

La función batchUpdate en la API de Sheets es una forma común de interactuar con documentos y se describe aquí.

8. Lee datos de un archivo CSV de almacenamiento

Ahora que tenemos un lugar donde volcar nuestros datos, desarrollemos aún más nuestra función de Cloud en el editor intercalado y usemos la API de Cloud Storage para ir a tomar los datos reales del archivo que acabamos de subir y almacenarlos en una cadena:

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. Propaga la hoja recién creada

Ahora es el momento de completar la hoja que creamos con la misma API del cliente de Hojas de cálculo y los datos que acabamos de recopilar. Aprovecharemos esta oportunidad para agregar también algo de estilo a las columnas de la hoja (cambiar el tamaño de fuente de la primera fila y ponerla en negrita) :

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

Este código se debe agregar a nuestra Cloud Function, que ahora está completa en un 99%.

Observa cómo los datos y el diseño se combinan como varios requests en una sola llamada a la API de Sheets batchUpdate. Esto permite una actualización más eficiente y atómica.

Ten en cuenta también que definimos un rango de edición que coincide con el tamaño de la hoja que creamos. Esto significa que el contenido que supere las 26 columnas (el valor columnCount que se usa cuando se crea la hoja) fallará con este código en particular.

Si todo sale bien, en este punto puedes hacer lo siguiente:

1. guardar la función actualizada
2. soltar un archivo CSV en el bucket
3. verás los datos correspondientes aparecer en tu hoja de cálculo.

10. Cómo unir todo y probar el flujo

Las llamadas a las funciones que acabamos de analizar se pueden realizar como llamadas de bloqueo sucesivas en la función csv2sheet original:

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

Si necesitas el código fuente completo de la función, está disponible aquí (probablemente sea más fácil obtenerlo todo en un solo conjunto).

Una vez que todo esté en su lugar, simplemente sube un archivo CSV al bucket correcto y observa cómo se actualiza tu hoja de cálculo con una nueva hoja que contiene el contenido del archivo. Aquí tienes un archivo CSV de ejemplo si no tienes uno a mano.

Intenta subir varios archivos al bucket para ver qué sucede.

11. Eso es todo. Es hora de desmantelar la infraestructura

Es broma, no hay infraestructura que desmantelar, todo se hizo sin servidores.

Si lo deseas, puedes borrar la Cloud Function y el bucket que creaste, o incluso todo el proyecto.

12. Próximos pasos

Con esto, se completa este codelab en el que se te guían por los pasos para escuchar las cargas en un bucket de Cloud Storage en una Cloud Function para actualizar una Hoja de cálculo de Google con la API adecuada.

Aquí tiene algunos pasos para continuar:

• Consulta las guías prácticas de Cloud Functions (incluyen algunas prácticas recomendadas).
• Analiza uno de los instructivos de Cloud Functions.
• Explora más la API de Google Sheets

Si tuviste problemas con este codelab, no dudes en informar cualquier inconveniente a través del vínculo que se encuentra en la esquina inferior izquierda.

Agradecemos mucho tus comentarios.