Amount to Words Converter for Google Sheets

/**
 * Converts a currency amount to words, with optional case control.
 * @param {string|number} arg1 The currency code or the amount (if currency is omitted).
 * @param {string|number} [arg2] The amount (optional, if currency is provided in arg1).
 * @param {string} [caseType] The case: "title", "upper", "sentence", "lower" (optional).
 * @return {string} The amount in words.
 * @customfunction
 */
function A2W(arg1, arg2, caseType) {
  // --- Helper: flatten cell references ---
  function flatten(val) {
    if (typeof val === 'object' && val !== null && val.length) return val[0][0];
    return val;
  }
  arg1 = flatten(arg1);
  arg2 = flatten(arg2);
  caseType = flatten(caseType);

  // --- Capitalization functions ---
  function toTitleCase(str) {
    return str.replace(/\w\S*/g, function(txt) {
      return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
    });
  }
  function toSentenceCase(str) {
    return str.charAt(0).toUpperCase() + str.slice(1).toLowerCase();
  }
  function applyCase(str, type) {
    switch ((type || "title").toLowerCase()) {
      case "upper": return str.toUpperCase();
      case "lower": return str.toLowerCase();
      case "sentence": return toSentenceCase(str);
      case "title":
      default: return toTitleCase(str);
    }
  }

  // --- Currency patterns and names ---
  var currencyPatterns = {
    AED: /(?:^|\s|(?<=\d))AED(?=\s|$|\d)/i,
    GBP: /(?:^|\s|(?<=\d))(?:GBP|£)(?=\s|$|\d)/i,
    EUR: /(?:^|\s|(?<=\d))(?:EUR|€)(?=\s|$|\d)/i,
    USD: /(?:^|\s|(?<=\d))(?:USD|\$)(?=\s|$|\d)/i
  };
  var currencyNames = {
    AED: { main: 'UAE Dirham', fractional: 'Fil' },
    GBP: { main: 'Pound Sterling', fractional: 'Penny' },
    EUR: { main: 'Euro', fractional: 'Cent' },
    USD: { main: 'US Dollar', fractional: 'Cent' }
  };

  // --- Number format converters ---
  var formatConverters = [
    { prefix: 'European', fn: function(n) { return n.replace(/\./g, '').replace(',', '.'); }, pattern: /^[0-9]+(\.[0-9]{3})+,([0-9]+)$/ },
    { prefix: 'Continental European', fn: function(n) { return n.replace(',', '.'); }, pattern: /^[0-9]+,([0-9]+)$/ },
    { prefix: 'Swiss', fn: function(n) { return n.replace(/'/g, ''); }, pattern: /^[0-9]+('[0-9]{3})+\.[0-9]+$/ },
    { prefix: 'Indian', fn: function(n) { return n.replace(/,/g, ''); }, pattern: /^[0-9]+(,[0-9]{2})+(,[0-9]{3})?\.[0-9]+$/ },
    { prefix: 'Scandinavian', fn: function(n) { return n.replace(/ /g, '').replace(',', '.'); }, pattern: /^[0-9]+( [0-9]{3})+,([0-9]+)$/ },
    { prefix: 'ISO 31-0/SI', fn: function(n) { return n.replace(/ /g, ''); }, pattern: /^[0-9]+( [0-9]{3})+\.[0-9]+$/ },
    { prefix: 'US/UK Standard', fn: function(n) { return n.replace(/,/g, ''); }, pattern: /^[0-9]+(,[0-9]{3})+\.[0-9]+$/ },
    { prefix: 'Chinese/Japanese', fn: function(n) { return n; }, pattern: /^[0-9]+\.[0-9]+$/ }
  ];

  // --- Determine currency and amount ---
  var currency = null, amount = null;
  if (typeof arg2 === 'undefined' || arg2 === null || arg2 === '') {
    amount = arg1;
  } else {
    currency = arg1;
    amount = arg2;
  }
  if (!amount) return '';

  // --- Detect currency in amount string if not provided ---
  var detectedCurrency = null;
  var cleanInput = String(amount).trim();
  if (!currency) {
    for (var code in currencyPatterns) {
      if (currencyPatterns[code].test(cleanInput)) {
        detectedCurrency = code;
        cleanInput = cleanInput.replace(currencyPatterns[code], '').trim();
        break;
      }
    }
  } else {
    detectedCurrency = String(currency).toUpperCase();
    if (currencyPatterns[detectedCurrency]) {
      cleanInput = cleanInput.replace(currencyPatterns[detectedCurrency], '').trim();
    }
  }

  // --- Detect and normalize number format ---
  var normalized = cleanInput;
  for (var i = 0; i < formatConverters.length; i++) {
    if (formatConverters[i].pattern.test(cleanInput)) {
      normalized = formatConverters[i].fn(cleanInput);
      break;
    }
  }
  normalized = normalized.replace(/[^0-9.]/g, '');
  var number = parseFloat(normalized);
  if (isNaN(number)) return 'Invalid number';

  // --- Convert to words ---
  var words = convertToWordsGS(number, currencyNames[detectedCurrency] || null);

  // --- Apply case formatting ---
  return applyCase(words, caseType);
}

// --- Number to words conversion for Google Sheets ---
function convertToWordsGS(num, currency) {
  var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];
  var tens = ['', 'ten', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety'];
  var teens = ['ten', 'eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen'];
  var scales = ['', 'thousand', 'million', 'billion', 'trillion'];

  function convertLessThanThousand(n) {
    if (n === 0) return '';
    if (n < 10) return ones[n];
    if (n < 20) return teens[n - 10];
    if (n < 100) {
      return tens[Math.floor(n / 10)] + (n % 10 !== 0 ? ' ' + ones[n % 10] : '');
    }
    return ones[Math.floor(n / 100)] + ' hundred' + (n % 100 !== 0 ? ' and ' + convertLessThanThousand(n % 100) : '');
  }

  function convert(n) {
    if (n < 1000) return convertLessThanThousand(n);
    var result = '';
    var scaleIndex = 0;
    while (n > 0) {
      var chunk = n % 1000;
      if (chunk !== 0) {
        result = convertLessThanThousand(chunk) + ' ' + scales[scaleIndex] + ' ' + result;
      }
      n = Math.floor(n / 1000);
      scaleIndex++;
    }
    return result.trim();
  }

  var parts = String(num).split('.');
  var wholeNum = parseInt(parts[0], 10);
  var result = convert(wholeNum);

  if (currency) {
    if (parts[1]) {
      var fractional = parseInt(parts[1].padEnd(2, '0').substring(0, 2), 10);
      if (fractional > 0) {
        result = currency.main + ' ' + result + ' and ' + convertLessThanThousand(fractional) + ' ' + (fractional === 1 ? currency.fractional : currency.fractional + 's');
      } else {
        result = currency.main + ' ' + result;
      }
    } else {
      result = currency.main + ' ' + result;
    }
  } else if (parts[1]) {
    result += ' point';
    for (var i = 0; i < parts[1].length; i++) {
      result += ' ' + ones[parseInt(parts[1][i], 10)];
    }
  }
  return result;
}