Sheets

Conexión de Google Sheets con Node.js usando una Cuenta de Servicio

1. Configuración en Google Cloud Console

1. Crea un proyecto en Google Cloud Console :

2. Ve a Google Cloud Console.

3. Crea un nuevo proyecto o selecciona uno existente.

4. Activa la API de Google Sheets :

5. Ve a "APIs y Servicios" → "Biblioteca".

6. Busca y habilita la API de Google Sheets .

7. Crea una cuenta de servicio :

8. Ve a "APIs y Servicios" → "Credenciales".

9. Haz clic en "Crear credenciales" → "Cuenta de servicio".

10. Asigna un nombre y selecciona el rol Editor.

11. Descarga la clave JSON :

12. Una vez creada la cuenta de servicio, selecciona la cuenta.

13. Haz clic en "Claves" → "Agregar clave" → "JSON".
14. Descarga el archivo JSON y guárdalo en tu proyecto Node.js (e.g., creds.json).

2. Configura el Acceso a la Hoja de Cálculo

1. Abre tu hoja de cálculo en Google Sheets .

2. Comparte el acceso con la cuenta de servicio :

3. Encuentra el correo electrónico de la cuenta de servicio en el archivo JSON (propiedad client_email).

4. Comparte la hoja de cálculo con este correo (editor o viewer, según sea necesario).

3. Configuración en Node.js

1. Instala las dependencias necesarias : pnpm add googleapis
2. Configura la conexión

import { google } from 'googleapis';
import * as fs from 'fs';

// Tipo para la respuesta de valores de una hoja
interface SheetDataResponse {
  [key: string]: {
    value: string,
    cell: string
  }[]
}

// Cargar las credenciales de la Service Account
const credentials = JSON.parse(fs.readFileSync('creds.json', 'utf8'));

// Configurar el cliente JWT para la autenticación con la cuenta de servicio
const auth = new google.auth.JWT(
  credentials.client_email,
  undefined,
  credentials.private_key,
  ['https://www.googleapis.com/auth/spreadsheets']
);

async function authenticate() {

  await auth.authorize();
}

// Función para obtener los datos de una hoja de cálculo
export async function read_rows(spreadsheetId: string, range: string): Promise<SheetDataResponse | null> {
  await authenticate();
  const sheets = google.sheets({ version: 'v4', auth });

  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: spreadsheetId,
    range,
  });

  // response.data as any || null;

  const rows = response.data.values;
  if (!rows || rows.length === 0) {
    return null;  // Retorna null si no hay datos
  }

  // La primera fila será usada como claves (headers)
  const headers = rows[0];

  // Inicializamos un objeto donde cada clave será un array de objetos con value y cell
  const dataAsObject = {};
  headers.forEach((header, colIndex) => {
    header = header.toLocaleLowerCase();
    dataAsObject[header] = [];
  });

  // Rellenamos los arrays con objetos que contienen el valor y la referencia de la celda (ej. "A1")
  rows.slice(1).forEach((row, rowIndex) => {
    headers.forEach((header: string, colIndex) => {
      header = header.toLocaleLowerCase();
      const cellValue = row[colIndex] || '';  // Valor de la celda o vacío si no hay valor
      const cellReference = `${String.fromCharCode(65 + colIndex)}${rowIndex + 2}`;  // Convertir columna a letra y fila
      dataAsObject[header].push({
        value: cellValue,
        cell: cellReference
      });
    });
  });

  return dataAsObject;
}


// Función para insertar una fila en una hoja de cálculo
export async function add_row(spreadsheetId: string, range: string, values: Record<string, any>): Promise<void> {
  await authenticate();
  const sheets = google.sheets({ version: 'v4', auth });

  await sheets.spreadsheets.values.append({
    spreadsheetId,
    range,
    valueInputOption: 'USER_ENTERED',
    requestBody: {
      values: [Object.values(values)] //values.map(row => Object.values(row)),
    },
  });
}

El código de arriba lo puedes editar a tu gusto, el te muestra como leer y insertar datos una vez obtenidas las credenciales de tu service-account.

Acoplando las funciones a nuestro chatbos podriamos crear 2 comandos a modo de darnos una visualización general

import { Workflow } from "@elimeleth/vct-flow"
import { read_rows, add_row } from "./sheet.ts"

export const read_command = new Workflow("read")
.addAction(async (ctx, {send}) => {
    const rows = await read_rows();

    console.log(rows)
    /* todo! aqui coloca tu propia lógica */
});

export const add_command = new Workflow("add")
.addAction(async (ctx, {send}) => {
    await add_row(procces.env.SPREAD_SHEET_ID, "test", {
                                "nombre": "jhon",
                                "apellido": "doe"
                            });
    /* todo! aqui coloca tu propia lógica */
})