UrlFetchApp — Kết Nối API

UrlFetchApp Cơ Bản

UrlFetchApp cho phép Apps Script gửi HTTP request đến bất kỳ REST API nào. Đây là cầu nối giúp Google Sheets tích hợp với mọi dịch vụ bên ngoài.

// GET request don gian
function fetchExchangeRate() {
  const url = 'https://api.exchangerate.host/latest?base=USD&symbols=VND';
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());
  const usdVnd = data.rates.VND;

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A1').setValue('Ty gia USD/VND');
  sheet.getRange('B1').setValue(usdVnd);
  sheet.getRange('C1').setValue(new Date());
  Logger.log('USD/VND: ' + usdVnd);
}

POST Request — Gửi Dữ Liệu

// POST request voi JSON body
function createOrderViaAPI() {
  const url = 'https://api.myshop.com/orders';
  const payload = {
    product: 'GS Premium Template',
    qty: 1,
    price: 299000,
    customer: 'nguyen.van.a@gmail.com'
  };

  const options = {
    method: 'POST',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    headers: {
      'Authorization': 'Bearer YOUR_API_KEY',
      'X-App-Version': '1.0'
    },
    muteHttpExceptions: true // bat loi HTTP (4xx, 5xx) ma khong throw exception
  };

  const response = UrlFetchApp.fetch(url, options);
  const code = response.getResponseCode();

  if (code === 200 || code === 201) {
    const result = JSON.parse(response.getContentText());
    Logger.log('Tao don hang thanh cong: ' + result.orderId);
  } else {
    Logger.log('Loi ' + code + ': ' + response.getContentText());
  }
}

Xử Lý Lỗi Đúng Cách

function safeFetch(url, options) {
  try {
    const opts = options || { muteHttpExceptions: true };
    opts.muteHttpExceptions = true;

    const res = UrlFetchApp.fetch(url, opts);
    const code = res.getResponseCode();

    if (code < 200 || code >= 300) {
      Logger.log('HTTP Error ' + code + ': ' + res.getContentText().substring(0, 200));
      return null;
    }

    return JSON.parse(res.getContentText());
  } catch (err) {
    Logger.log('Exception khi goi API: ' + err.message);
    return null;
  }
}

// Dung lai
function importData() {
  const data = safeFetch('https://api.example.com/products');
  if (!data) {
    SpreadsheetApp.getUi().alert('Khong lay duoc du lieu API!');
    return;
  }
  // xu ly data...
}

Gửi Notification Telegram

// Gui thong bao qua Telegram Bot
function sendTelegramNotification(message) {
  const BOT_TOKEN = 'YOUR_BOT_TOKEN';
  const CHAT_ID = 'YOUR_CHAT_ID';
  const url = 'https://api.telegram.org/bot' + BOT_TOKEN + '/sendMessage';

  UrlFetchApp.fetch(url, {
    method: 'POST',
    contentType: 'application/json',
    payload: JSON.stringify({
      chat_id: CHAT_ID,
      text: message,
      parse_mode: 'HTML'
    })
  });
}

// Goi khi co don hang moi
function onNewOrder() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const orderData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];
  sendTelegramNotification(
    'Don hang moi: ' + orderData[0] + ' - ' + orderData[3] + ' VND'
  );
}

Xử Lý Rate Limit & Retry

function fetchWithRetry(url, maxRetries) {
  maxRetries = maxRetries || 3;
  for (var attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      if (res.getResponseCode() === 429) {
        // Rate limited — cho chuc hoi roi thu lai
        Logger.log('Rate limited, wait 2s... (lan ' + attempt + ')');
        Utilities.sleep(2000 * attempt);
        continue;
      }
      return JSON.parse(res.getContentText());
    } catch (err) {
      if (attempt === maxRetries) throw err;
      Utilities.sleep(1000);
    }
  }
  return null;
}