Google Sheets Integration

Overview

Quality Hub GINEZ uses Google Sheets as the data source for its product catalog. This approach provides:

✅ Real-time updates without code deployment
✅ Familiar spreadsheet interface for non-technical users
✅ Easy collaboration on product data
✅ Version history and audit trail
✅ No database schema changes needed

The system consumes two separate sheets:

Materia Prima (MP) - Raw materials catalog
Producto Terminado (PT) - Finished products catalog

Architecture

Data is fetched on-demand when users access the catalog. Consider implementing Next.js revalidation for production deployments.

Setup Instructions

Step 1: Create Google Sheets

Create two separate Google Sheets for your product data:

Create Raw Materials Sheet

Go to Google Sheets
Create new spreadsheet: “Quality Hub - Materia Prima”
Add columns (see schema below)

Create Finished Products Sheet

Create another spreadsheet: “Quality Hub - Producto Terminado”
Add columns (see schema below)

Populate Data

Fill in your product information following the schema requirements

Step 2: Sheet Schema

Both sheets should follow this structure:

Codigo,Nombre,Familia,Categoria,Tipo,FichaTecnica,HojaSeguridad

Column Definitions

Codigo

string

required

Product Code - Unique identifier (e.g., “LIMLIM”, “TRALIM”)

Must be unique within the sheet
Used for matching with production records
Should match codes in production-constants.ts

Nombre

string

required

Product Name - Full descriptive nameExample: “Limpiador Multiusos Limón 5L”

Familia

string

required

Product Family - High-level categorizationCommon values:

Cuidado del Hogar
Cuidado Personal
Lavandería
Línea Automotriz
Línea Antibacterial
Productos Intermedios

Categoria

string

required

Product Category - Specific product typeExamples:

Limpiadores Multiusos
Detergentes para Trastes
Suavizantes de Telas
Jabones Líquidos
Shampoos

Tipo

string

required

Type - Material classificationValues:

Materia Prima - Raw materials
Producto Terminado - Finished products
Producto Intermedio - Intermediate products

FichaTecnica

url

Technical Data Sheet URL - Link to product specification PDF

Should be a Google Drive public link or direct download URL
Example: https://drive.google.com/file/d/ABC123/view?usp=sharing
Downloads are tracked in admin audit log

HojaSeguridad

url

Safety Data Sheet (SDS) URL - Link to safety documentation

Should be a Google Drive public link or direct download URL
Critical for chemical products
Downloads are tracked in admin audit log

Step 3: Publish to Web

Each sheet must be published as CSV to generate the URL needed for the application.

Open Publish Dialog

In your Google Sheet:

Click File → Share → Publish to web

Configure Publication

In the publish dialog:

Link tab: Select the specific sheet/tab (e.g., “Sheet1”)
Format: Choose Comma-separated values (.csv)
✅ Check “Automatically republish when changes are made”
Click Publish

Copy URL

Copy the generated URL. It should look like:

https://docs.google.com/spreadsheets/d/e/2PACX-1vQxyz.../pub?gid=0&single=true&output=csv

Add to Environment

Add the URLs to your .env.local:

SHEET_MP_CSV_URL="https://docs.google.com/spreadsheets/d/e/.../pub?gid=123&single=true&output=csv"
SHEET_PT_CSV_URL="https://docs.google.com/spreadsheets/d/e/.../pub?gid=456&single=true&output=csv"

Make sure to select CSV format, not HTML. The URL must end with output=csv.

Document Storage (Google Drive)

Setting Up Document Links

The FichaTecnica and HojaSeguridad columns should link to PDF documents stored in Google Drive.

Upload Documents

Create a Google Drive folder: “Quality Hub - Documentos”
Upload all technical data sheets and safety sheets as PDFs
Organize in subfolders if needed (e.g., by product family)

Set Permissions

For each document:

Right-click → Share
Click Change to anyone with the link
Set permission to Viewer
Click Copy link

Add Links to Sheet

Paste the Google Drive links into the respective columns:

https://drive.google.com/file/d/1ABC123xyz/view?usp=sharing

Use descriptive filenames for PDFs (e.g., FT-LIMLIM-2024.pdf) to make management easier.

Data Validation

Implement these validation rules in your Google Sheets:

Required Field Validation

=AND(LEN(A2)>0, COUNTIF($A:$A, A2)=1)

Apply these using Data → Data validation in Google Sheets.

Syncing and Caching

Current Behavior

// Data fetched on page load (client-side)
const response = await fetch(process.env.SHEET_MP_CSV_URL)
const csvText = await response.text()
const products = parseCSV(csvText)

Current implementation fetches fresh data on every page load. This ensures data is always current but may impact performance.

Recommended Production Setup

For better performance, implement server-side caching:

import { NextResponse } from 'next/server'

export const revalidate = 300 // Revalidate every 5 minutes

export async function GET() {
  try {
    const [mpResponse, ptResponse] = await Promise.all([
      fetch(process.env.SHEET_MP_CSV_URL!, { next: { revalidate } }),
      fetch(process.env.SHEET_PT_CSV_URL!, { next: { revalidate } })
    ])
    
    const [mpData, ptData] = await Promise.all([
      mpResponse.text(),
      ptResponse.text()
    ])
    
    return NextResponse.json({
      materiaPrima: parseCSV(mpData),
      productoTerminado: parseCSV(ptData)
    })
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to fetch catalog' },
      { status: 500 }
    )
  }
}

Adjust revalidate time based on how frequently your catalog changes. For stable catalogs, use 3600 (1 hour) or more.

Troubleshooting

Error: Failed to fetch catalog data

Possible causes:

Sheet not published to web
Wrong URL format (not CSV)
Sheet permissions too restrictive

Solution:

# Test URL directly in browser
curl "YOUR_SHEET_URL" | head -20

Should return CSV text, not HTML.

Products not showing in catalog

Check:

CSV structure matches schema (headers in row 1)
No empty required fields
Encoding is UTF-8 (special characters display correctly)
No duplicate Codigo values

Debug:

console.log('Raw CSV:', await response.text())
console.log('Parsed products:', products)

Document links not working

Solutions:

Verify Google Drive link permissions (“Anyone with the link can view”)
Test links in incognito/private browser window
Ensure URLs use https:// (not http://)
Check for trailing spaces in sheet cells

Fix bulk links:

=TRIM(A2) // Remove spaces
=SUBSTITUTE(A2, "http://", "https://") // Fix protocol

Special characters (é, ñ, á) display incorrectly

Cause: Encoding issueSolution:

Google Sheets should auto-publish as UTF-8
In your fetch code, ensure:

const response = await fetch(url)
const text = await response.text() // Automatically handles UTF-8

If issues persist, manually specify:

const buffer = await response.arrayBuffer()
const decoder = new TextDecoder('utf-8')
const text = decoder.decode(buffer)

Changes not appearing in app

If using caching:

Wait for revalidation period to expire
Or manually clear cache:

mutate('/api/catalog') // If using SWR
router.refresh() // If using Next.js App Router

If no caching:

Hard refresh browser (Ctrl+Shift+R)
Verify sheet has “auto-republish” enabled
Check published URL returns latest data

Best Practices

Data Governance

Limit edit access to quality/product managers
Use protected ranges for critical columns
Maintain change log sheet for audit trail

Performance

Keep sheets under 10,000 rows for best performance
Use separate sheets for historical/archived products
Implement server-side caching for production

Data Quality

Use data validation rules
Standardize naming conventions
Regularly audit for duplicates or inconsistencies

Documentation

Include sheet instructions in first row (hide it)
Maintain README sheet with schema definitions
Document any custom formulas or validation rules

Migration to Database (Optional)

For large-scale deployments, consider migrating catalog data to Supabase:

Create Catalog Table

CREATE TABLE catalogo_productos (
  id BIGSERIAL PRIMARY KEY,
  codigo TEXT UNIQUE NOT NULL,
  nombre TEXT NOT NULL,
  familia TEXT NOT NULL,
  categoria TEXT NOT NULL,
  tipo TEXT NOT NULL,
  ficha_tecnica TEXT,
  hoja_seguridad TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Import Existing Data

Export CSV from Google Sheets and import:

COPY catalogo_productos(codigo, nombre, familia, categoria, tipo, ficha_tecnica, hoja_seguridad)
FROM '/path/to/export.csv'
DELIMITER ','
CSV HEADER;

Update Application

Replace CSV fetch with Supabase query:

const { data: products } = await supabase
  .from('catalogo_productos')
  .select('*')
  .order('nombre')

Keep Google Sheets for initial setup and small deployments. Migrate to database when catalog exceeds 5,000+ products or requires complex queries.

Get Started

Core Modules

Quality Management

User Management

Configuration

Overview

Architecture

Setup Instructions

Step 1: Create Google Sheets

Step 2: Sheet Schema

Column Definitions

Step 3: Publish to Web

Document Storage (Google Drive)

Setting Up Document Links

Data Validation

Required Field Validation

Syncing and Caching

Current Behavior

Recommended Production Setup

Troubleshooting

Best Practices

Data Governance

Performance

Data Quality

Documentation

Migration to Database (Optional)

Next Steps

Environment Variables

Multi-Location

Build docs developers (and LLMs) love

Get Started

Core Modules

Quality Management

User Management

Configuration

​Overview

​Architecture

​Setup Instructions

​Step 1: Create Google Sheets

​Step 2: Sheet Schema

​Column Definitions

​Step 3: Publish to Web

​Document Storage (Google Drive)

​Setting Up Document Links

​Data Validation

​Required Field Validation

​Syncing and Caching

​Current Behavior

​Recommended Production Setup

​Troubleshooting

​Best Practices

Data Governance

Performance

Data Quality

Documentation

​Migration to Database (Optional)

​Next Steps

Environment Variables

Multi-Location

Build docs developers (and LLMs) love

Overview

Architecture

Setup Instructions

Step 1: Create Google Sheets

Step 2: Sheet Schema

Column Definitions

Step 3: Publish to Web

Document Storage (Google Drive)

Setting Up Document Links

Data Validation

Required Field Validation

Syncing and Caching

Current Behavior

Recommended Production Setup

Troubleshooting

Best Practices

Migration to Database (Optional)

Next Steps