Google Apps Script custom function to fetch and average Bank of Taiwan (BoT) TWD/USD spot exchange rates for a specific date. Supports multiple date formats and automated CSV parsing.

GetTWDUSDSpotRate.gs

/**
 * Google Apps Script: Fetch TWD/USD Exchange Rate from Bank of Taiwan
 * 
 * Description: Retrieves "Spot Buy" (Index 3) and "Spot Sell" (Index 13) 
 * from BoT's historical CSV and returns the average value.
 * 
 * Usage in Sheets: =GetTWDUSDSpotRate("2026/04/01")
 */

function GetTWDUSDSpotRate(inputDate) {
  let formattedDate = "";
  
  // Normalize date input
  if (inputDate instanceof Date) {
    formattedDate = Utilities.formatDate(inputDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
  } else {
    formattedDate = inputDate.toString().replace(/\//g, "-");
  }

  const dataObject = FetchBoTRawData(formattedDate);
  
  if (typeof dataObject === "string") {
    return dataObject; // Returns error message
  }
  
  const buy = parseFloat(dataObject.SpotBuy);
  const sell = parseFloat(dataObject.SpotSell);
  
  if (isNaN(buy) || isNaN(sell)) {
    return `Error: Data at Col 3 (${dataObject.SpotBuy}) or Col 13 (${dataObject.SpotSell}) is not a number.`;
  }
  
  return (buy + sell) / 2;
}

function FetchBoTRawData(dateStr) {
  const url = `https://rate.bot.com.tw/xrt/flcsv/0/${dateStr}`;
  
  try {
    const response = UrlFetchApp.fetch(url);
    const content = response.getContentText();
    const data = Utilities.parseCsv(content);
    
    for (let i = 0; i < data.length; i++) {
      let currencyName = data[i][0] || "";
      if (currencyName.includes("USD")) {
        return {
          "SpotBuy": data[i][3],  // User specified index 3
          "SpotSell": data[i][13] // User specified index 13
        };
      }
    }
    return "USD not found for this date.";
  } catch (e) {
    return "Fetch Error: Bank may be closed on this date.";
  }
}