getgelrate.js

/**
 * Gets the official GEL rate on specified date
 *
 * @param {currency} Which currency to query (USD, EUR, etc)
 * @param {date} Date to query
 * @return The rate of specified currency in GEL
 * @customfunction
 */
function GETGELRATE(currency, date) {
    // Debug logs for initial parameters
    console.log("Original parameters - currency:", currency, "date:", date);
    console.log("Currency type:", typeof currency, "Date type:", typeof date);
    
    // Auto-detect parameter order - if first param looks like a date, swap them
    var actualCurrency, actualDate;
    
    if ((currency instanceof Date) || 
        (typeof currency === 'number' && currency > 1000) ||
        (typeof currency === 'string' && (currency.includes('-') || currency.includes('/') || currency.length > 10))) {
        // First parameter looks like a date, swap them
        console.log("Parameters appear swapped, correcting order");
        actualCurrency = date;
        actualDate = currency;
    } else {
        // Parameters are in correct order
        actualCurrency = currency;
        actualDate = date;
    }
    
    console.log("Corrected parameters - currency:", actualCurrency, "date:", actualDate);
    
    if (actualCurrency == "GEL") {
      return 1;
    }
    
    var cache = CacheService.getScriptCache();
    
    // Convert date to proper Date object and format it
    var dateObj;
    if (actualDate instanceof Date) {
        console.log("Processing as Date object");
        dateObj = actualDate;
    } else if (typeof actualDate === 'string') {
        console.log("Processing as string:", actualDate);
        dateObj = new Date(actualDate);
    } else if (typeof actualDate === 'number') {
        console.log("Processing as number:", actualDate);
        // Handle Excel/Sheets serial date numbers
        dateObj = new Date((actualDate - 25569) * 86400 * 1000);
    } else {
        console.log("Unknown date type, using current date");
        // Default to today if date is invalid
        dateObj = new Date();
    }
    
    console.log("Converted dateObj:", dateObj);
    console.log("dateObj.getTime():", dateObj.getTime());
    console.log("isNaN(dateObj.getTime()):", isNaN(dateObj.getTime()));
    
    // Check if date is valid
    if (isNaN(dateObj.getTime())) {
        console.error("Date conversion failed - original date:", actualDate, "converted:", dateObj);
        throw new Error("Invalid date provided - original: " + actualDate + ", type: " + typeof actualDate);
    }
    
    var formattedDate = dateObj.toISOString().split('T')[0];
    var cache_key = actualCurrency + "+" + formattedDate;
    var cached = cache.get(cache_key);
    
    if (cached != null) {
        return parseFloat(cached);
    }

    try {
        var response = UrlFetchApp.fetch("https://nbg-currency-proxy.cofob.workers.dev/gw/api/ct/monetarypolicy/currencies/en/json/?currencies="+ actualCurrency + "&date=" + formattedDate);
        var rates = JSON.parse(response.getContentText());
        
        if (!rates || rates.length === 0 || !rates[0].currencies || rates[0].currencies.length === 0) {
            throw new Error("No rate data found for " + actualCurrency + " on " + formattedDate);
        }
        
        var rate = rates[0].currencies[0].rate;
        cache.put(cache_key, rate, 1500); // cache for 25 minutes
        return Number(rate);
        
    } catch (error) {
        console.error("Error fetching rate:", error);
        throw new Error("Failed to fetch exchange rate: " + error.message);
    }
}