Google Sheets API: Hướng Dẫn Kết Nối & Tích Hợp Cho Developer Việt 2026

// Cài đặt dependencies
// npm install googleapis google-auth-library

const { google } = require('googleapis');
const { OAuth2Client } = require('google-auth-library');

const CLIENT_ID = 'your-client-id.apps.googleusercontent.com';
const CLIENT_SECRET = 'your-client-secret';
const REDIRECT_URI = 'http://localhost:3000/auth/callback';

// Bước 1: Tạo OAuth2 client
const oauth2Client = new OAuth2Client(
  CLIENT_ID,
  CLIENT_SECRET,
  REDIRECT_URI
);

// Bước 2: Generate authorization URL
const authUrl = oauth2Client.generateAuthUrl({
  access_type: 'offline',  // Lấy refresh_token
  scope: [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive.readonly'
  ]
});

console.log('Authorize at:', authUrl);

// Bước 3: Exchange authorization code for tokens
async function getTokens(code) {
  const { tokens } = await oauth2Client.getToken(code);
  oauth2Client.setCredentials(tokens);

  // Lưu tokens vào database để reuse
  console.log('Access token:', tokens.access_token);
  console.log('Refresh token:', tokens.refresh_token);

  return oauth2Client;
}

// Bước 4: Sử dụng client đã authenticated
async function readSheet(auth, spreadsheetId, range) {
  const sheets = google.sheets({ version: 'v4', auth });

  const response = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range,  // Ví dụ: 'Sheet1!A1:D10'
  });

  return response.data.values;
}

// Access token hết hạn sau 1 giờ, cần tự động refresh
// googleapis library xử lý điều này tự động nếu có refresh_token

oauth2Client.on('tokens', (tokens) => {
  if (tokens.refresh_token) {
    // Lưu refresh_token mới vào database
    saveRefreshToken(tokens.refresh_token);
  }
  // Luôn lưu access_token mới
  saveAccessToken(tokens.access_token);
});

// Restore tokens từ database khi khởi động
async function restoreAuth(userId) {
  const { accessToken, refreshToken } = await db.getTokens(userId);

  oauth2Client.setCredentials({
    access_token: accessToken,
    refresh_token: refreshToken
  });

  return oauth2Client;
}

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

// Cách 1: Dùng file credentials.json
async function getAuthFromFile() {
  const auth = new google.auth.GoogleAuth({
    keyFile: './credentials.json',
    scopes: ['https://www.googleapis.com/auth/spreadsheets']
  });
  return auth;
}

// Cách 2: Dùng environment variables (khuyến nghị cho production)
async function getAuthFromEnv() {
  const auth = new google.auth.GoogleAuth({
    credentials: {
      type: 'service_account',
      project_id: process.env.GOOGLE_PROJECT_ID,
      private_key_id: process.env.GOOGLE_PRIVATE_KEY_ID,
      private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
      client_email: process.env.GOOGLE_CLIENT_EMAIL,
      client_id: process.env.GOOGLE_CLIENT_ID,
    },
    scopes: ['https://www.googleapis.com/auth/spreadsheets']
  });
  return auth;
}

// Sử dụng
async function main() {
  const auth = await getAuthFromEnv();
  const sheets = google.sheets({ version: 'v4', auth });

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

  // Đọc dữ liệu
  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: SPREADSHEET_ID,
    range: 'Orders!A2:F100'
  });

  console.log('Data:', response.data.values);
}

main();

# pip install google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client

from google.oauth2 import service_account
from googleapiclient.discovery import build
import os

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'credentials.json'

def get_sheets_service():
    creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE,
        scopes=SCOPES
    )
    service = build('sheets', 'v4', credentials=creds)
    return service.spreadsheets()

def read_sheet(spreadsheet_id, range_name):
    sheets = get_sheets_service()
    result = sheets.values().get(
        spreadsheetId=spreadsheet_id,
        range=range_name
    ).execute()
    return result.get('values', [])

# Sử dụng
SPREADSHEET_ID = 'your-spreadsheet-id'
data = read_sheet(SPREADSHEET_ID, 'Sheet1!A1:D100')
for row in data:
    print(row)

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

  // Đọc nhiều ranges trong 1 request — tiết kiệm quota
  const response = await sheets.spreadsheets.values.batchGet({
    spreadsheetId,
    ranges: [
      'Sản Phẩm!A2:E1000',    // Danh sách sản phẩm
      'Đơn Hàng!A2:H5000',    // Đơn hàng
      'Khách Hàng!A2:G2000',  // Khách hàng
    ],
    valueRenderOption: 'UNFORMATTED_VALUE',  // Lấy raw value, không format
    dateTimeRenderOption: 'SERIAL_NUMBER'   // Ngày tháng dạng số
  });

  const [products, orders, customers] = response.data.valueRanges;

  return {
    products: products.values || [],
    orders: orders.values || [],
    customers: customers.values || []
  };
}

// Chuyển đổi array thành object có key
function rowsToObjects(headers, rows) {
  return rows.map(row =>
    headers.reduce((obj, header, index) => {
      obj[header] = row[index] || null;
      return obj;
    }, {})
  );
}

// Sử dụng
const data = await batchReadSheets(auth, SPREADSHEET_ID);
const headers = ['id', 'name', 'price', 'stock', 'category'];
const products = rowsToObjects(headers, data.products);
console.log(products[0]); // { id: 'SP001', name: 'Áo thun', price: 150000, ... }

// Đọc tất cả đơn hàng, lọc đơn hàng trong ngày
async function getTodayOrders(auth, spreadsheetId) {
  const sheets = google.sheets({ version: 'v4', auth });

  const response = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range: 'Đơn Hàng!A2:H',
    valueRenderOption: 'UNFORMATTED_VALUE'
  });

  const rows = response.data.values || [];
  const today = new Date().toLocaleDateString('vi-VN');

  // Lọc phía client — với dataset nhỏ đây là cách đơn giản nhất
  const todayOrders = rows.filter(row => row[1] === today);  // Cột B = ngày

  return todayOrders;
}

// Với dataset lớn, dùng Google Sheets QUERY function thay thế
// Đặt công thức QUERY vào một sheet riêng, đọc kết quả qua API
// =QUERY(Đơn Hàng!A:H, "SELECT * WHERE B = date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'")

async function appendOrder(auth, spreadsheetId, orderData) {
  const sheets = google.sheets({ version: 'v4', auth });

  const row = [
    orderData.id,
    orderData.date,
    orderData.customerId,
    orderData.customerName,
    orderData.total,
    orderData.status,
    orderData.shippingAddress,
    new Date().toISOString()  // timestamp tạo
  ];

  const response = await sheets.spreadsheets.values.append({
    spreadsheetId,
    range: 'Đơn Hàng!A:H',  // Append vào cuối vùng dữ liệu
    valueInputOption: 'USER_ENTERED',  // Google xử lý format (ngày, số)
    insertDataOption: 'INSERT_ROWS',   // Thêm hàng mới
    requestBody: {
      values: [row]
    }
  });

  const updatedRange = response.data.updates.updatedRange;
  console.log(\`Order ${orderData.id} added at ${updatedRange}\`);

  return response.data;
}

// Thêm nhiều hàng cùng lúc
async function appendMultipleOrders(auth, spreadsheetId, orders) {
  const sheets = google.sheets({ version: 'v4', auth });

  const rows = orders.map(order => [
    order.id, order.date, order.customerName,
    order.total, order.status
  ]);

  await sheets.spreadsheets.values.append({
    spreadsheetId,
    range: 'Đơn Hàng!A:E',
    valueInputOption: 'USER_ENTERED',
    requestBody: { values: rows }
  });

  console.log(\`Added ${orders.length} orders\`);
}

// Cập nhật trạng thái đơn hàng theo row number
async function updateOrderStatus(auth, spreadsheetId, rowNumber, newStatus) {
  const sheets = google.sheets({ version: 'v4', auth });

  // Chú ý: row 1 = header, dữ liệu bắt đầu từ row 2
  const range = \\`Đơn Hàng!F\${rowNumber}\\`;  // Cột F = status

  await sheets.spreadsheets.values.update({
    spreadsheetId,
    range,
    valueInputOption: 'RAW',
    requestBody: {
      values: [[newStatus]]  // 2D array
    }
  });

  console.log(\\`Row \${rowNumber} status updated to: \${newStatus}\\`);
}

// Tìm hàng theo ID rồi cập nhật
async function updateByOrderId(auth, spreadsheetId, orderId, updates) {
  const sheets = google.sheets({ version: 'v4', auth });

  // Đọc tất cả IDs để tìm row
  const response = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range: 'Đơn Hàng!A:A'  // Chỉ đọc cột ID
  });

  const ids = response.data.values.map(row => row[0]);
  const rowIndex = ids.indexOf(orderId);

  if (rowIndex === -1) throw new Error(\\`Order \${orderId} not found\\`);

  const rowNumber = rowIndex + 1;  // 1-based

  // Cập nhật nhiều cells trong cùng hàng
  await sheets.spreadsheets.values.update({
    spreadsheetId,
    range: \\`Đơn Hàng!E\${rowNumber}:F\${rowNumber}\\`,
    valueInputOption: 'USER_ENTERED',
    requestBody: {
      values: [[updates.total, updates.status]]
    }
  });
}

async function syncInventory(auth, spreadsheetId, inventoryData) {
  const sheets = google.sheets({ version: 'v4', auth });

  // Cập nhật nhiều ô trong 1 request
  const data = inventoryData.map((item, index) => ({
    range: \\`Kho!C\${index + 2}\\`,  // Cột C = tồn kho
    values: [[item.stock]]
  }));

  await sheets.spreadsheets.values.batchUpdate({
    spreadsheetId,
    requestBody: {
      valueInputOption: 'RAW',
      data: data  // Mảng nhiều ranges
    }
  });

  console.log(\\`Updated \${inventoryData.length} inventory items\\`);
}

// Clear và ghi lại toàn bộ (efficient cho full sync)
async function fullSync(auth, spreadsheetId, sheetName, allData) {
  const sheets = google.sheets({ version: 'v4', auth });

  // Bước 1: Clear sheet
  await sheets.spreadsheets.values.clear({
    spreadsheetId,
    range: \\`\${sheetName}!A2:Z\\`  // Giữ header hàng 1
  });

  // Bước 2: Write tất cả data mới
  if (allData.length > 0) {
    await sheets.spreadsheets.values.update({
      spreadsheetId,
      range: \\`\${sheetName}!A2\\`,
      valueInputOption: 'USER_ENTERED',
      requestBody: { values: allData }
    });
  }

  console.log(\\`Full sync: \${allData.length} rows written\\`);
}

// Xử lý rate limit với exponential backoff
async function sheetsRequestWithRetry(requestFn, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await requestFn();
    } catch (error) {
      if (error.code === 429) {  // Too Many Requests
        const delay = Math.pow(2, attempt) * 1000;  // 1s, 2s, 4s
        console.log(\\`Rate limited. Waiting \${delay}ms...\\`);
        await new Promise(resolve => setTimeout(resolve, delay));
      } else {
        throw error;  // Lỗi khác, không retry
      }
    }
  }
  throw new Error('Max retries exceeded');
}

// Sử dụng
const data = await sheetsRequestWithRetry(() =>
  sheets.spreadsheets.values.get({
    spreadsheetId,
    range: 'Sheet1!A1:Z1000'
  })
);

async function formatHeaderRow(auth, spreadsheetId, sheetId) {
  const sheets = google.sheets({ version: 'v4', auth });

  await sheets.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: {
      requests: [
        {
          // Format header row (row 0 = row 1 trong UI)
          repeatCell: {
            range: {
              sheetId: sheetId,
              startRowIndex: 0,
              endRowIndex: 1
            },
            cell: {
              userEnteredFormat: {
                backgroundColor: { red: 0.2, green: 0.4, blue: 0.8 },  // Màu xanh
                textFormat: {
                  foregroundColor: { red: 1, green: 1, blue: 1 },  // Chữ trắng
                  bold: true,
                  fontSize: 11
                },
                horizontalAlignment: 'CENTER',
                borders: {
                  bottom: {
                    style: 'SOLID',
                    color: { red: 0, green: 0, blue: 0 }
                  }
                }
              }
            },
            fields: 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment,borders)'
          }
        },
        {
          // Freeze header row
          updateSheetProperties: {
            properties: {
              sheetId: sheetId,
              gridProperties: { frozenRowCount: 1 }
            },
            fields: 'gridProperties.frozenRowCount'
          }
        },
        {
          // Auto resize tất cả columns
          autoResizeDimensions: {
            dimensions: {
              sheetId: sheetId,
              dimension: 'COLUMNS',
              startIndex: 0,
              endIndex: 10
            }
          }
        }
      ]
    }
  });
}

// Highlight hàng có tồn kho thấp (màu đỏ)
async function highlightLowStock(auth, spreadsheetId, sheetId, lowStockRows) {
  const sheets = google.sheets({ version: 'v4', auth });

  const requests = lowStockRows.map(rowIndex => ({
    repeatCell: {
      range: {
        sheetId: sheetId,
        startRowIndex: rowIndex - 1,  // 0-based
        endRowIndex: rowIndex
      },
      cell: {
        userEnteredFormat: {
          backgroundColor: { red: 1, green: 0.8, blue: 0.8 }  // Màu đỏ nhạt
        }
      },
      fields: 'userEnteredFormat.backgroundColor'
    }
  }));

  await sheets.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: { requests }
  });
}

// Next.js API route: /api/orders (webhook từ Shopify/WooCommerce)
import { google } from 'googleapis';

const SPREADSHEET_ID = process.env.GOOGLE_SPREADSHEET_ID;

async function getAuth() {
  const auth = new google.auth.GoogleAuth({
    credentials: {
      client_email: process.env.GOOGLE_CLIENT_EMAIL,
      private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n'),
    },
    scopes: ['https://www.googleapis.com/auth/spreadsheets']
  });
  return auth;
}

export async function POST(request) {
  const order = await request.json();

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

  // Format dữ liệu
  const row = [
    order.id,
    new Date(order.created_at).toLocaleDateString('vi-VN'),
    order.customer.name,
    order.customer.phone,
    order.total_price,
    order.financial_status,  // paid/pending/refunded
    order.fulfillment_status, // fulfilled/unfulfilled
    order.shipping_address?.city,
    order.line_items.map(i => i.title).join(', ')
  ];

  // Append vào sheet Đơn Hàng
  await sheets.spreadsheets.values.append({
    spreadsheetId: SPREADSHEET_ID,
    range: 'Đơn Hàng!A:I',
    valueInputOption: 'USER_ENTERED',
    requestBody: { values: [row] }
  });

  // Cập nhật tồn kho
  for (const item of order.line_items) {
    await decrementStock(sheets, item.sku, item.quantity);
  }

  return Response.json({ success: true });
}

async function decrementStock(sheets, sku, quantity) {
  // Tìm row có SKU tương ứng trong sheet Kho
  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: SPREADSHEET_ID,
    range: 'Kho!A:D'
  });

  const rows = response.data.values || [];
  const rowIndex = rows.findIndex(r => r[0] === sku);

  if (rowIndex > 0) {
    const currentStock = parseInt(rows[rowIndex][2]) || 0;
    const newStock = Math.max(0, currentStock - quantity);

    await sheets.spreadsheets.values.update({
      spreadsheetId: SPREADSHEET_ID,
      range: \\`Kho!C\${rowIndex + 1}\\`,
      valueInputOption: 'RAW',
      requestBody: { values: [[newStock]] }
    });
  }
}

// Node.js script chạy mỗi 5 phút để cập nhật dashboard
const { google } = require('googleapis');

async function updateRevenueReport() {
  const auth = await getAuth();
  const sheets = google.sheets({ version: 'v4', auth });

  // Đọc tất cả đơn hàng trong tháng từ database
  const orders = await db.getOrdersThisMonth();

  // Tính toán các metrics
  const totalRevenue = orders.reduce((sum, o) => sum + o.total, 0);
  const totalOrders = orders.length;
  const avgOrderValue = totalRevenue / totalOrders;
  const paidOrders = orders.filter(o => o.status === 'paid');
  const conversionRate = paidOrders.length / totalOrders;

  // Revenue by day
  const revenueByDay = orders.reduce((acc, order) => {
    const date = order.date;
    acc[date] = (acc[date] || 0) + order.total;
    return acc;
  }, {});

  // Cập nhật summary dashboard
  const summaryData = [
    ['Tổng Doanh Thu', totalRevenue],
    ['Số Đơn Hàng', totalOrders],
    ['Giá Trị Đơn Trung Bình', avgOrderValue],
    ['Tỷ Lệ Chuyển Đổi', conversionRate],
    ['Cập Nhật Lúc', new Date().toLocaleString('vi-VN')]
  ];

  // Batch update: Summary + Chi tiết theo ngày
  await sheets.spreadsheets.values.batchUpdate({
    spreadsheetId: SPREADSHEET_ID,
    requestBody: {
      valueInputOption: 'USER_ENTERED',
      data: [
        { range: 'Dashboard!B2:C6', values: summaryData },
        {
          range: 'Doanh Thu Ngày!A2',
          values: Object.entries(revenueByDay).map(([date, revenue]) => [date, revenue])
        }
      ]
    }
  });

  console.log('Dashboard updated at', new Date().toLocaleString('vi-VN'));
}

// Chạy mỗi 5 phút
setInterval(updateRevenueReport, 5 * 60 * 1000);
updateRevenueReport(); // Chạy ngay lập tức

// Express.js API endpoints cho CRM trên Sheets
const express = require('express');
const app = express();

// GET /leads - Lấy danh sách leads
app.get('/leads', async (req, res) => {
  const rows = await readSheet(SPREADSHEET_ID, 'CRM!A2:I');

  const leads = rows.map(row => ({
    id: row[0],
    name: row[1],
    company: row[2],
    email: row[3],
    phone: row[4],
    source: row[5],     // Facebook/Google/Referral
    stage: row[6],      // New/Qualified/Proposal/Won/Lost
    value: row[7],
    lastContact: row[8]
  }));

  res.json(leads);
});

// POST /leads - Thêm lead mới
app.post('/leads', async (req, res) => {
  const lead = req.body;
  const id = \\`LEAD-\${Date.now()}\\`;

  const row = [
    id,
    lead.name,
    lead.company,
    lead.email,
    lead.phone,
    lead.source,
    'New',  // Default stage
    lead.estimatedValue || 0,
    new Date().toLocaleDateString('vi-VN')
  ];

  await appendToSheet(SPREADSHEET_ID, 'CRM!A:I', [row]);
  res.json({ id, ...lead });
});

// PATCH /leads/:id/stage - Cập nhật stage
app.patch('/leads/:id/stage', async (req, res) => {
  const { id } = req.params;
  const { stage } = req.body;

  await updateFieldById(SPREADSHEET_ID, 'CRM', 'A', id, 'G', stage);
  res.json({ success: true });
});

class SheetsClient {
  constructor(auth, spreadsheetId) {
    this.sheets = google.sheets({ version: 'v4', auth });
    this.spreadsheetId = spreadsheetId;
  }

  async request(fn, options = {}) {
    const { maxRetries = 3, initialDelay = 1000 } = options;

    for (let attempt = 1; attempt <= maxRetries; attempt++) {
      try {
        return await fn();
      } catch (error) {
        const status = error.code || error.response?.status;
        const isRetryable = [429, 500, 502, 503, 504].includes(status);

        if (isRetryable && attempt < maxRetries) {
          const delay = initialDelay * Math.pow(2, attempt - 1);
          console.warn(\\`Request failed (${status}). Retry \${attempt}/\${maxRetries} in \${delay}ms\\`);
          await new Promise(r => setTimeout(r, delay));
          continue;
        }

        // Log lỗi chi tiết
        console.error('Sheets API error:', {
          status,
          message: error.message,
          attempt
        });

        throw error;
      }
    }
  }

  async read(range) {
    return this.request(() =>
      this.sheets.spreadsheets.values.get({
        spreadsheetId: this.spreadsheetId,
        range,
        valueRenderOption: 'UNFORMATTED_VALUE'
      })
    );
  }

  async append(range, values) {
    return this.request(() =>
      this.sheets.spreadsheets.values.append({
        spreadsheetId: this.spreadsheetId,
        range,
        valueInputOption: 'USER_ENTERED',
        requestBody: { values }
      })
    );
  }
}