Bot WA dengan Integrasi Google Sheets

Google Sheets = Database gratis untuk bot kamu!

Tidak perlu setup database rumit. Integrasikan bot dengan Google Sheets untuk simpan data, kelola produk, dan kolaborasi tim.

Kenapa Google Sheets?

✅ KELEBIHAN:

- GRATIS (Google account saja)
- Mudah diakses (browser/app)
- Real-time collaboration
- Visual & easy to edit
- Tidak perlu skill database
- Auto-save, ada history
- Bisa share ke tim

❌ KETERBATASAN:

- Tidak untuk volume sangat besar
- Rate limit API (100 req/100 sec)
- Performance tidak secepat database

Use Cases

📊 GOOGLE SHEETS BISA UNTUK:

1. Database Produk & Harga
2. Log Order Masuk
3. Database Customer
4. Template Responses
5. FAQ & Knowledge Base
6. Tracking Inventory
7. Report & Analytics

Setup Integrasi

Step 1: Buat Google Sheets

1. Buka sheets.google.com
2. Buat spreadsheet baru
3. Struktur contoh untuk Produk:

| ID | Nama Produk | Harga | Stock | Deskripsi |
|----|-------------|-------|-------|-----------|
| 1  | Kaos Hitam  | 150000| 50    | Kaos cotton|
| 2  | Kemeja Navy | 250000| 30    | Kemeja formal|

Step 2: Setup Google API

1. Buka console.cloud.google.com
2. Buat project baru
3. Enable "Google Sheets API"
4. Buat Service Account
5. Download JSON credentials
6. Share spreadsheet ke email service account

Step 3: Install Library

bash

npm install googleapis

Step 4: Basic Connection

javascript

const { google } = require('googleapis');

// Load credentials
const auth = new google.auth.GoogleAuth({
    keyFile: 'credentials.json',
    scopes: ['https://www.googleapis.com/auth/spreadsheets']
});

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

const SPREADSHEET_ID = 'your-spreadsheet-id-here';

Read Data dari Sheets

Get All Products:

javascript

async function getProducts() {
    const response = await sheets.spreadsheets.values.get({
        spreadsheetId: SPREADSHEET_ID,
        range: 'Produk!A2:E100' // Skip header row
    });
    
    const rows = response.data.values || [];
    
    return rows.map(row => ({
        id: row[0],
        name: row[1],
        price: parseInt(row[2]),
        stock: parseInt(row[3]),
        description: row[4]
    }));
}

// Usage di bot
client.on('message', async msg => {
    if (msg.body.toLowerCase() === 'katalog') {
        const products = await getProducts();
        
        let message = '📦 KATALOG PRODUK\n\n';
        products.forEach(p => {
            message += `${p.name}\n`;
            message += `💰 Rp ${p.price.toLocaleString()}\n`;
            message += `📊 Stock: ${p.stock}\n\n`;
        });
        
        await msg.reply(message);
    }
});

Search Product:

javascript

async function findProduct(keyword) {
    const products = await getProducts();
    
    return products.filter(p => 
        p.name.toLowerCase().includes(keyword.toLowerCase())
    );
}

// Usage
client.on('message', async msg => {
    if (msg.body.toLowerCase().startsWith('cari ')) {
        const keyword = msg.body.slice(5);
        const results = await findProduct(keyword);
        
        if (results.length === 0) {
            await msg.reply('Produk tidak ditemukan 😔');
        } else {
            let message = `🔍 Hasil pencarian "${keyword}":\n\n`;
            results.forEach(p => {
                message += `• ${p.name} - Rp ${p.price.toLocaleString()}\n`;
            });
            await msg.reply(message);
        }
    }
});

Write Data ke Sheets

Log Order:

javascript

async function logOrder(orderData) {
    const values = [[
        new Date().toISOString(),
        orderData.phone,
        orderData.customerName,
        orderData.product,
        orderData.quantity,
        orderData.total,
        'PENDING'
    ]];
    
    await sheets.spreadsheets.values.append({
        spreadsheetId: SPREADSHEET_ID,
        range: 'Orders!A:G',
        valueInputOption: 'USER_ENTERED',
        insertDataOption: 'INSERT_ROWS',
        resource: { values }
    });
}

// Usage saat customer order
async function processOrder(phone, product, qty) {
    const orderData = {
        phone,
        customerName: await getCustomerName(phone),
        product: product.name,
        quantity: qty,
        total: product.price * qty
    };
    
    await logOrder(orderData);
    
    return `✅ Order tercatat!\n\n📦 ${product.name} x${qty}\n💰 Total: Rp ${orderData.total.toLocaleString()}`;
}

Save Customer Data:

javascript

async function saveCustomer(phone, name, address) {
    // Check if customer exists
    const existing = await findCustomer(phone);
    
    if (existing) {
        // Update existing row
        await updateCustomer(existing.rowIndex, { phone, name, address });
    } else {
        // Append new row
        await sheets.spreadsheets.values.append({
            spreadsheetId: SPREADSHEET_ID,
            range: 'Customers!A:D',
            valueInputOption: 'USER_ENTERED',
            resource: {
                values: [[phone, name, address, new Date().toISOString()]]
            }
        });
    }
}

Update Data di Sheets

Update Stock:

javascript

async function updateStock(productId, newStock) {
    // Find row number for product
    const response = await sheets.spreadsheets.values.get({
        spreadsheetId: SPREADSHEET_ID,
        range: 'Produk!A:A'
    });
    
    const rows = response.data.values || [];
    const rowIndex = rows.findIndex(row => row[0] === productId);
    
    if (rowIndex === -1) {
        throw new Error('Product not found');
    }
    
    // Update stock (column D, row = rowIndex + 1)
    await sheets.spreadsheets.values.update({
        spreadsheetId: SPREADSHEET_ID,
        range: `Produk!D${rowIndex + 1}`,
        valueInputOption: 'USER_ENTERED',
        resource: {
            values: [[newStock]]
        }
    });
}

// Usage: Kurangi stock setelah order
async function processOrderWithStock(product, qty) {
    const newStock = product.stock - qty;
    await updateStock(product.id, newStock);
}

Update Order Status:

javascript

async function updateOrderStatus(orderId, newStatus) {
    // Find order row
    const response = await sheets.spreadsheets.values.get({
        spreadsheetId: SPREADSHEET_ID,
        range: 'Orders!A:G'
    });
    
    const rows = response.data.values || [];
    const rowIndex = rows.findIndex(row => row[0] === orderId);
    
    // Update status column (G)
    await sheets.spreadsheets.values.update({
        spreadsheetId: SPREADSHEET_ID,
        range: `Orders!G${rowIndex + 1}`,
        valueInputOption: 'USER_ENTERED',
        resource: {
            values: [[newStatus]]
        }
    });
}

Template Responses dari Sheets

Setup Sheet Template:

| Keyword | Response |
|---------|----------|
| harga   | Silakan lihat katalog kami di [LINK] |
| ongkir  | Ongkir ke Jakarta: Rp 15.000, Bandung: Rp 12.000 |
| jam     | Jam operasional: Senin-Sabtu, 09:00-17:00 |
| rekening| BCA 1234567890 a.n. Toko ABC |

Load & Use Templates:

javascript

async function getTemplates() {
    const response = await sheets.spreadsheets.values.get({
        spreadsheetId: SPREADSHEET_ID,
        range: 'Templates!A2:B100'
    });
    
    const templates = {};
    (response.data.values || []).forEach(row => {
        templates[row[0].toLowerCase()] = row[1];
    });
    
    return templates;
}

// Cache templates
let templatesCache = null;

async function getResponse(keyword) {
    if (!templatesCache) {
        templatesCache = await getTemplates();
    }
    
    return templatesCache[keyword.toLowerCase()];
}

// Usage
client.on('message', async msg => {
    const template = await getResponse(msg.body);
    
    if (template) {
        await msg.reply(template);
    }
});

// Refresh cache setiap 5 menit
setInterval(async () => {
    templatesCache = await getTemplates();
}, 5 * 60 * 1000);

Sheet Structure Examples

Products Sheet:

| ID | Name | Price | Stock | Category | Image_URL | Description |
|----|------|-------|-------|----------|-----------|-------------|

Orders Sheet:

| Order_ID | Date | Phone | Name | Products | Total | Status | Resi |
|----------|------|-------|------|----------|-------|--------|------|

Customers Sheet:

| Phone | Name | Address | City | Joined | Total_Orders | Last_Order |
|-------|------|---------|------|--------|--------------|------------|

FAQ Sheet:

| Question | Answer | Category |
|----------|--------|----------|

Caching untuk Performance

javascript

// Simple caching untuk reduce API calls
const cache = new Map();
const CACHE_TTL = 5 * 60 * 1000; // 5 minutes

async function getCached(key, fetchFn) {
    const cached = cache.get(key);
    
    if (cached && Date.now() - cached.timestamp < CACHE_TTL) {
        return cached.data;
    }
    
    const data = await fetchFn();
    cache.set(key, { data, timestamp: Date.now() });
    
    return data;
}

// Usage
async function getProductsCached() {
    return getCached('products', getProducts);
}

Error Handling

javascript

async function safeSheetOperation(operation) {
    try {
        return await operation();
    } catch (error) {
        if (error.code === 429) {
            // Rate limit exceeded
            console.log('Rate limit hit, waiting 60s...');
            await sleep(60000);
            return await operation();
        }
        
        if (error.code === 403) {
            // Permission issue
            console.error('Permission denied. Check sheet sharing settings.');
        }
        
        throw error;
    }
}

// Usage
const products = await safeSheetOperation(getProducts);

Best Practices

DO ✅

- Cache frequently accessed data
- Use batch operations when possible
- Validate data before writing
- Handle rate limits gracefully
- Keep sheet structure simple
- Regular backup (export)

DON'T ❌

- Query sheets setiap message (rate limit)
- Store sensitive data tanpa encryption
- Overload dengan terlalu banyak data
- Ignore error handling
- Complex formulas yang slow

FAQ

Apakah Google Sheets aman untuk production?

Untuk small-medium business, YES! Untuk high-volume (>10k orders/day), consider proper database.

Rate limit berapa?

100 requests per 100 seconds per user. Dengan caching, ini sangat cukup untuk most use cases.

Bisa multi-user edit bersamaan?

Ya! Ini salah satu keunggulan Google Sheets - real-time collaboration.

Kesimpulan

Google Sheets = Simple database untuk bot!

Simple, free, effective!

Setup Sheets Integration →

Artikel Terkait

• Bot WA untuk Lead Generation
• WhatsApp API dengan Google Sheets
• Bot Jualan dengan Katalog