修改Google Apps Script实现多币种自动转换为英镑并格式化

以下是修改后的脚本，可从E列读取带货币符号的金额，自动转换为英镑数值并输出到指定列（示例为AD列），同时设置英镑货币格式——全程通过脚本调用GOOGLEFINANCE获取汇率，避免单元格公式自动重算的问题：

function convertToGBP() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const sourceRange = sheet.getRange("E3:E" + lastRow);
  const displayValues = sourceRange.getDisplayValues();
  const outputRange = sheet.getRange("AD3:AD" + lastRow);
  const outputValues = [];
  
  // 货币符号对应三位代码映射，可按需扩展
  const symbolToCode = {
    "$": "USD",
    "€": "EUR",
    "£": "GBP",
    "¥": "JPY",
    "C$": "CAD"
  };

  for (let i = 0; i < displayValues.length; i++) {
    const cellText = displayValues[i][0].trim();
    if (!cellText) {
      outputValues.push([""]);
      continue;
    }

    // 提取货币符号和纯金额数值
    let currencySymbol = cellText.match(/^[^\d.]+/)[0].trim();
    const amount = parseFloat(cellText.replace(/[^0-9.]/g, ""));
    
    // 匹配不到对应货币代码或非数值则跳过
    const fromCurrency = symbolToCode[currencySymbol];
    if (!fromCurrency || isNaN(amount)) {
      outputValues.push([""]);
      continue;
    }

    // 用临时单元格执行GOOGLEFINANCE获取汇率，避免公式残留
    const rateFormula = `=GOOGLEFINANCE("CURRENCY:${fromCurrency}GBP")`;
    const tempCell = sheet.getRange("ZZ1");
    tempCell.setFormula(rateFormula);
    SpreadsheetApp.flush(); // 强制刷新获取计算结果
    const exchangeRate = tempCell.getValue();
    tempCell.clearContent();

    // 计算转换后的英镑金额，示例取整，如需小数可改为toFixed(2)
    const gbpAmount = Math.round(amount * exchangeRate);
    outputValues.push([gbpAmount]);
  }

  // 写入结果并设置英镑显示格式
  outputRange.setValues(outputValues);
  outputRange.setNumberFormat("£#,##0"); // 整数格式，小数格式用"£#,##0.00"
}

关键修改说明 #

• 符号提取优化：通过正则匹配开头的非数字/小数点字符，兼容多字符货币符号（如C$）
• 汇率获取逻辑：用临时单元格执行GOOGLEFINANCE公式，获取结果后立即清空，避免污染表格
• 错误防护：自动跳过空单元格、无法识别的符号或非数值内容，防止脚本中断
• 格式直接设置：通过setNumberFormat一步完成英镑格式配置，无需手动调整单元格

自定义调整点 #

• 更换输出列：修改outputRange中的列标识（如将AD改为AE）
• 保留小数精度：将Math.round替换为(amount * exchangeRate).toFixed(2)，同时调整格式字符串为"£#,##0.00"
• 历史汇率查询：修改GOOGLEFINANCE公式，例如添加日期参数：=GOOGLEFINANCE("CURRENCY:USDGBP", "price", DATE(2024,1,1))

内容的提问来源于stack exchange，提问作者user20777937