如何使用FXMacroData与Google Apps脚本和Google表格

谷歌表格是宏分析师的剪贴板:更新快,共享简单,并且已经连接到谷歌工作空间的其他部分. Google Apps Script 页面内置的JavaScript运行时间 可以让您在不离开浏览器的情况下从电子表格调用任何 REST API. 本指南将FXMacroData公告数据拉到页面选项卡中,并使用 UrlFetchApp处理速度限制和重试,将多指标响应正常化为清洁行,并安排自动更新,以便您的宏仪表板在没有手动干预的情况下保持更新.

步骤1 创建一个Google表格,打开应用程序脚本编辑器

打开 页面.谷歌.com 给它一个描述性的名称,例如 汇率数据仪表板然后打开脚本编辑器:

1. 按下 扩展 在顶部菜单中.
2. 选择 应用程序脚本现在我们要做什么?
3. 编辑器以默认方式在新选项卡中打开. Code.gs 文件.
4. 改名项目 (左上方字段) 到 货币数据加载器 为了更明确.

您写的所有代码都在谷歌基础设施上运行, UrlFetchApp 通过 阅读和写表格 SpreadsheetApp 服务.

步骤2 写一个带有重试逻辑的获取辅助器

贴上下面的代码 Code.gs取代了位符函数. 这个助手包裹 UrlFetchApp.fetch 并且具有指数式回落,因此短暂的错误或短暂速度限制反应不会杀死整个运行.

/**
 * Fetches a FXMacroData endpoint with automatic retry and exponential back-off.
 *
 * @param {string} currency  - e.g. "usd", "eur", "chf"
 * @param {string} indicator - e.g. "policy_rate", "gdp", "inflation"
 * @returns {Object|null}    - Parsed JSON response, or null on permanent failure
 */
function fetchAnnouncement(currency, indicator) {
  const apiKey = PropertiesService.getScriptProperties()
                   .getProperty('FXMACRODATA_API_KEY') || '';
  const url = `https://fxmacrodata.com/api/v1/announcements/${currency}/${indicator}`
              + (apiKey ? `?api_key=${apiKey}` : '');

  const maxRetries = 4;
  let delay = 1000; // 1 second initial back-off

  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      const status = response.getResponseCode();

      if (status === 200) {
        return JSON.parse(response.getContentText());
      }

      if (status === 429) {
        // Rate limited — honour the back-off and retry
        Logger.log(`Rate limited on attempt ${attempt}. Waiting ${delay}ms.`);
        Utilities.sleep(delay);
        delay *= 2; // exponential back-off
        continue;
      }

      if (status === 404) {
        Logger.log(`No data for ${currency}/${indicator} (404). Skipping.`);
        return null;
      }

      // Other non-retryable errors
      Logger.log(`HTTP ${status} for ${currency}/${indicator}.`);
      return null;

    } catch (e) {
      Logger.log(`Network error on attempt ${attempt}: ${e.message}`);
      Utilities.sleep(delay);
      delay *= 2;
    }
  }

  Logger.log(`Permanently failed after ${maxRetries} attempts: ${currency}/${indicator}`);
  return null;
}

值得注意的一些事物:

• muteHttpExceptions: true 防止应用程序脚本在非-200代码上投放你得到状态代码,可以决定要做什么.
• 没有任何问题. 其他 (过多请求) 触发一次复试,延迟翻倍.FXMacroData强制执行每键的速率限制;一个适度的后退策略使脚本在整个指标扫描中保持在预算范围内.
• 没有任何问题. 没有 通常意味着指标尚未用于该货币助手返回 null 所以排列是清晰的跳过.
• Logger.log 输出可以看到下面 查看 → 日志 在Apps脚本编辑器中,使调试简单.

步骤3 将JSON响应标准化为电子表格行

汇率/指标对返回一个单个对象.为了构建有用的电子表格,您需要将请求列表平坦化成一致的行结构. 添加下面的正常化函数 fetchAnnouncement没有人知道.

/**
 * Converts a FXMacroData announcement response object into a flat array
 * suitable for appending as a single Sheets row.
 *
 * Columns: Timestamp, Currency, Indicator, Value, Prior, Consensus,
 *          Announcement DateTime, Direction
 *
 * @param {Object} data - Parsed JSON from fetchAnnouncement()
 * @returns {Array}     - Flat row array
 */
function toRow(data) {
  if (!data) return null;

  const direction =
    data.val > data.prior  ? 'Beat'  :
    data.val < data.prior  ? 'Miss'  : 'In line';

  return [
    new Date().toISOString(),          // Run timestamp
    (data.currency  || '').toUpperCase(),
    (data.indicator || '').replace(/_/g, ' '),
    data.val        ?? '',
    data.prior      ?? '',
    data.consensus  ?? '',
    data.announcement_datetime || '',
    direction
  ];
}

没有什么. announcement_datetime 时间是作为一个除重键的理想选择:在添加之前,您可以检查表格中是否已经存在一个带有这个时间的行,防止重复运行时重复行.

步骤4 写入数据到Google表单选项卡

现在添加主要加载函数,将所有东西联系在一起:它在货币/指标对列表上代, fetchAnnouncement,将每个结果转换为 toRow,并将行添加到一个专用页面选项卡.

/**
 * Defines the currency/indicator pairs to track.
 * Extend this list to cover additional signals for your strategy.
 */
const INDICATORS = [
  { currency: 'usd', indicator: 'policy_rate' },
  { currency: 'usd', indicator: 'inflation' },
  { currency: 'usd', indicator: 'non_farm_payrolls' },
  { currency: 'eur', indicator: 'policy_rate' },
  { currency: 'eur', indicator: 'inflation' },
  { currency: 'chf', indicator: 'gdp' },
  { currency: 'chf', indicator: 'consumer_confidence' },
  { currency: 'chf', indicator: 'gov_bond_10y' },
  { currency: 'gbp', indicator: 'policy_rate' },
  { currency: 'gbp', indicator: 'inflation' },
];

const SHEET_NAME = 'MacroData';
const HEADERS    = [
  'Run Timestamp', 'Currency', 'Indicator', 'Value',
  'Prior', 'Consensus', 'Announcement DateTime', 'Direction'
];

/**
 * Main entry point — fetches all configured indicators and
 * appends new rows to the MacroData sheet tab.
 */
function loadMacroData() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet = ss.getSheetByName(SHEET_NAME);

  // Create the tab if it does not exist yet
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow(HEADERS);
    sheet.getRange(1, 1, 1, HEADERS.length)
         .setFontWeight('bold')
         .setBackground('#1a73e8')
         .setFontColor('#ffffff');
    sheet.setFrozenRows(1);
  }

  // Build a set of existing announcement_datetimes to avoid duplicates
  const lastRow  = sheet.getLastRow();
  const existing = new Set();
  if (lastRow > 1) {
    const dtCol = 7; // "Announcement DateTime" is column 7 (index 6, 1-based col 7)
    const values = sheet.getRange(2, dtCol, lastRow - 1, 1).getValues();
    values.forEach(([dt]) => { if (dt) existing.add(String(dt)); });
  }

  const newRows = [];

  INDICATORS.forEach(({ currency, indicator }) => {
    // Throttle requests — 200 ms between calls keeps well within rate limits
    Utilities.sleep(200);

    const data = fetchAnnouncement(currency, indicator);
    const row  = toRow(data);

    if (!row) return; // skip null / error responses

    const announcementDt = row[6]; // announcement_datetime column
    if (existing.has(announcementDt)) {
      Logger.log(`Skipping duplicate: ${currency}/${indicator} @ ${announcementDt}`);
      return;
    }

    newRows.push(row);
    existing.add(announcementDt); // guard against duplicates within the same run
  });

  if (newRows.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, HEADERS.length)
         .setValues(newRows);
    Logger.log(`Appended ${newRows.length} new row(s) to "${SHEET_NAME}".`);
  } else {
    Logger.log('No new rows — all announcements already present.');
  }
}

现在就跑吧. loadMacroData 通过编辑器手动 (点击 ▶ 跑步首先运行会提示您授权脚本点击 检查权限 → 允许 允许访问电子表格和外部网络请求.

function resetSheet() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  if (sheet) ss.deleteSheet(sheet);
  loadMacroData(); // recreates with fresh headers and data
}

步骤5 处理较大的指标扫描的利率限制

在步骤4中,200ms的请求间延迟对于上述十个指标列表是足够的.如果您扩展到50个或更多的对涵盖整个公告目录中的多种货币,您应该实施更有意义的缩.用基于计数器的暂停取代持续睡眠:

/**
 * Fetches a larger list of indicators with adaptive throttling.
 * Pauses for 1 second every 10 requests to respect rate limits.
 */
function loadMacroDataBulk(indicators) {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet = ss.getSheetByName(SHEET_NAME) || (() => {
    const s = ss.insertSheet(SHEET_NAME);
    s.appendRow(HEADERS);
    s.getRange(1, 1, 1, HEADERS.length)
     .setFontWeight('bold')
     .setBackground('#1a73e8')
     .setFontColor('#ffffff');
    s.setFrozenRows(1);
    return s;
  })();

  const newRows = [];
  let   count   = 0;

  indicators.forEach(({ currency, indicator }) => {
    count++;

    // Longer pause every 10 requests
    if (count % 10 === 0) {
      Logger.log(`Pausing after ${count} requests…`);
      Utilities.sleep(1500);
    } else {
      Utilities.sleep(150);
    }

    const data = fetchAnnouncement(currency, indicator);
    const row  = toRow(data);
    if (row) newRows.push(row);
  });

  if (newRows.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, HEADERS.length)
         .setValues(newRows);
  }
  Logger.log(`Bulk load complete — ${newRows.length} rows appended.`);
}

FXMacroData的公告终点速度快 每个响应通常从Google Apps脚本执行环境中返回不到100ms. 大扫描的主要瓶是每个键请求预算而不是延迟; 间隔与 Utilities.sleep 是最简单的方法, 保持在计划的限制范围内, 没有批量或缓存逻辑.

步骤6 计划自动更新,并使用时间驱动的触发

应用程序脚本 触发器 系统允许您在没有专用服务器的时间表上运行任何函数. 下面的辅助器程序化地创建一个平日早晨触发器 从编辑器运行一次以注册它,然后删除辅助程序本身:

/**
 * Registers a time-driven trigger that runs loadMacroData()
 * every weekday between 07:00 and 08:00 UTC.
 *
 * Run this function ONCE from the Apps Script editor to set up the trigger.
 * You do not need to call it again — it persists in the project.
 */
function createWeekdayTrigger() {
  // Remove any existing triggers for loadMacroData to avoid duplicates
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === 'loadMacroData')
    .forEach(t => ScriptApp.deleteTrigger(t));

  ScriptApp.newTrigger('loadMacroData')
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(7)
    .create();

  // Also register Tuesday through Friday
  [
    ScriptApp.WeekDay.TUESDAY,
    ScriptApp.WeekDay.WEDNESDAY,
    ScriptApp.WeekDay.THURSDAY,
    ScriptApp.WeekDay.FRIDAY,
  ].forEach(day => {
    ScriptApp.newTrigger('loadMacroData')
      .timeBased()
      .everyWeeks(1)
      .onWeekDay(day)
      .atHour(7)
      .create();
  });

  Logger.log('Weekday triggers registered for loadMacroData.');
}

在跑步之后 createWeekdayTrigger打开 触发器 (编辑器左侧侧中显示的警钟图标) 确认五个触发器出现,每周一都有一个.每个触发机在您的Google帐户配置的时区中在7点到8点之间发射.

步骤 7 获取发布日历以事件日预先过

没有什么. /v1/calendar/{currency} 终点返回货币的即将发布的计划. 周一使用它来构建一组本周的公告日期,然后在没有事件的日子跳过获取步骤 这避免了安静的星期中不必要的API调用.

/**
 * Returns a Set of date strings ("YYYY-MM-DD") for which at least one
 * high-impact announcement is scheduled this week for the given currency.
 *
 * @param {string} currency - e.g. "usd"
 * @returns {Set} */函数getAnnouncementDatesThisWeek(currency) { const apiKey = PropertiesService.getScriptProperties() .getProperty (('FXMACRODATA.getCode.getResponse.API_KEY') '; const url = https://fxmacrodata.com/api/v1/calendar/${currency}` + (apiKey ? `(((?api_key=${apiKee}`: ' '); const response = UrlFetch mute.Appfetchurl, {HttpExceptions: true }); if (response.getCalCode) == 200) return = new; fetchdate = set; fetcheddate = new); fetcheddata = set (MacData.getData.date.date; fetcher=set); fetchdata = new (Macdata.date); fetcher = fetcheddatetable; fetchesdate.data = load (MacDate); fetchesdata = fetchdate; fetchingdate (Macdate); forgetdata.data.context = fetches;date); *getdata (data); fetching dates); fetcheatdate (data) = load;date (date);); fetchs); fetchaineddate (no); fetchet (date) = new)); fetes (date (day); fetedates); fetshut;date) {date); date (date;date; date); date); run (date=today); date;date=date);date); (date).date);date (date)).

为了使用这个模式,注册 loadMacroDataCalendarAware 作为触发器处理器而不是 loadMacroData 取代函数名称字符串 createWeekdayTrigger 根据情况.

总结

您现在有一个完整的,生产准备的管道, 通过应用程序脚本将FXMacroData连接到Google表格:

• 一个带有重试和指数式后置的获取辅助器, 能优雅地处理短暂网络错误和速度限制响应.
• 一个将每个公告响应转换成一个一致的,安全的电子表格行.
• 一个页面编辑器,在第一次运行时创建标题,添加只有新版本,并跳过以前看到的 announcement_datetime 价值观.
• 适应性缩对于数十种货币/指标对的大量扫描.
• 完全自动更新的每日更新.
• 选项日历预先检查,避免在没有计划发布的日期中冗余的API调用.