Google Apps Script调用CoinGecko API月初报Invalid date错误

报错完全是日期处理逻辑bug导致的，和月初月末API限流、第三方服务问题无关，核心问题有3个：

• 昨日日期计算逻辑错误：直接用当前日期的日字段减1计算昨天，每月1号运行时，getDate() -1会得到0，生成00-07-2022这类不存在的日期；1月1号运行时还会出现月份、年份不自动回退的问题，直接触发接口返回invalid date
• 月份补零逻辑被注释：getMonth方法中用于截断两位长度的.slice(-2)被注释，10/11/12月运行时会生成3位长度的月份值（比如10月会返回010），拼接出的日期格式完全不符合接口要求
• 隐性逻辑问题：getYesterday方法重复3次调用getYearMonthDay()生成新的Date实例，脚本如果刚好在零点前后运行，会出现日期拼接错位；另外pushOtherField方法里的marketData、currency变量未加声明，会污染全局作用域。

不要手动做日期加减，JS原生Date对象提供了自动处理跨月、跨年、闰年的日期偏移方法，直接基于当前时间回退1天取昨天的日期即可，从根源上避免月初/年末的日期计算错误。

替换原有代码中日期处理相关的方法，同时修复未声明变量、对象键值取值不规范的问题，修复后的完整可运行代码如下：

function getImportData0() {
  function updateSheets() {
    for (let i = 0; i < CoinGecko.coinSheetNames.length; i++) {
      CoinGecko.tryCatch(i);
    }
  }
  updateSheets();
}
const CoinGecko = {
  // # Entity
  serverName: 'https://api.coingecko.com/api/v3/',
  endpointName: 'coins/',
  coinSheetNames: [
    { bitcoin: 'Bitcoin (BTC)' },
    { aave: 'Aave (AAVE)' },
    { aeon: 'AEON (AEON)' },
    { cardano: 'Cardano (ADA)' },
    { 'pirate-chain': 'Pirate Chain (ARRR)' },
    { dero: 'Dero (DERO)' },
    { enjincoin: 'Enjin (ENJ)' },
    { eos: 'EOS (EOS)' },
    { 'loki-network': 'Oxen (OXEN)' },
    { wownero: 'WowNero (WOW)' },
    { triton: 'Equilibria (XEQ)' },
    { haven: 'Haven (XHV)' },
    { monero: 'Monero (XMR)' },
    { ripple: 'Ripple (XRP)' },
  ],
  resourceType: 'history',
  subResourceType: ['market_data'],

  fieldTypes: ['market_cap', 'total_volume', 'current_price'],
  currency: 'usd',

  // # Main Functions
  tryCatch: function (i) {
    try {
      this.updateSheet(i);
    } catch (error) {
      CoinGecko.handleError(error);
      this.tryCatch(i);
    }
  },
  updateSheet: function (i) {
    const jsonObject = CoinGecko.fetchJsonObject(i);
    const record = CoinGecko.getRecord(jsonObject);
    CoinGecko.setRecordToSheet(record, i);
  },
  handleError: function (error) {
    if (!error.toString().includes('1015')) {
      throw error;
    }
    console.log(
      `CoinGecko server is busy. Restarting the program. [Errror Message]: ${error}`
    );
    Utilities.sleep(1000);
  },

  // # Input Boundary
  fetchJsonObject: function (i) {
    const requestUrl = this.getRequestUrl(i);
    const jsonString = UrlFetchApp.fetch(requestUrl).getContentText();
    const jsonObject = JSON.parse(jsonString);
    return jsonObject;
  },
  getRequestUrl: function (i) {
    const coinName = Object.keys(this.coinSheetNames[i])[0];
    return (
      this.serverName +
      this.endpointName +
      coinName +
      '/' +
      this.resourceType +
      '?date=' +
      this.getddmmyyyyYesterday()
    );
  },

  // #Interactor
  getRecord: function (jsonObject) {
    let record = [];
    this.pushDate(record);
    this.pushOtherFields(record, jsonObject);
    return record;
  },
  pushDate: function (record) {
    const date = this.getyyyymmddYesterday();
    record.push(date);
  },
  pushOtherFields: function (record, jsonObject) {
    const fieldTypes = this.fieldTypes;
    for (let i = 0; i < fieldTypes.length; i++) {
      this.pushOtherField(record, jsonObject, fieldTypes[i]);
    }
  },
  pushOtherField: function (record, jsonObject, fieldType) {
    const marketData = jsonObject[this.subResourceType[0]];
    const currency = this.currency;
    const value = marketData[fieldType][currency];
    record.push(value);
    return record;
  },
  getddmmyyyyYesterday() {
    const {year, month, day} = this.getYearMonthDay();
    return `${day}-${month}-${year}`;
  },
  getyyyymmddYesterday: function () {
    const {year, month, day} = this.getYearMonthDay();
    return `${year}-${month}-${day}`;
  },
  getYearMonthDay: function () {
    // 自动处理跨月、跨年、闰年的昨日日期计算，不需要手动加减
    const yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);
    const year = yesterday.getFullYear();
    const month = ('0' + (yesterday.getMonth() + 1)).slice(-2);
    const day = ('0' + yesterday.getDate()).slice(-2);
    return {year, month, day};
  },

  // # Output Boundary
  setRecordToSheet: function (record, i) {
    const sheetName = Object.values(this.coinSheetNames[i])[0];
    const range = this.getRange(record, i, sheetName);
    range.setValues([record]);
    console.log(`Importing ${sheetName} Completed`);
  },
  getSheet: function (sheetName) {
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(sheetName);
    if (sheet == null) {
      throw new Error(
        `'${sheetName}' sheet is not found in this Spreadsheet. Please fix the sheet name or create a new sheet.`
      );
    }
    return sheet;
  },
  getRange: function (record, i, sheetName) {
    const sheet = this.getSheet(sheetName);

    let lastRow = sheet.getLastRow();
    if (lastRow == null) {
      lastRow = 1;
    }
    const colRow = record.length;
    const range = sheet.getRange(lastRow + 1, 1, 1, colRow);
    return range;
  },
};

原有代码中Object.keys(this.coinSheetNames[i])和Object.values(this.coinSheetNames[i])没有取数组下标0，会导致拼接请求URL、取Sheet名时传入数组对象而非字符串，部分运行环境下会出现隐式类型转换的问题，修复版里已经补上了下标取值逻辑。

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