Google Apps Script调用AlphaVantage API出现多余请求问题

使用Google Apps Script调用AlphaVantage API获取数据时遇到异常：脚本从Google Sheets的I6单元格动态获取股票代码，获取四个指定功能（"OVERVIEW"、"BALANCE_SHEET"、"CASH_FLOW"、"INCOME_STATEMENT"）的数据。运行时前4次请求成功，但额外触发第5次请求，此时传入API的function参数变为undefined，生成无效URL（如https://www.alphavantage.co/query?function=undefined&symbol=aapl&apikey=9R6A1KGBO4C1CYWD）。已检查代码确认function名称正确传入，但问题仍存在。

代码片段 #

const apiKey = "XXXXXX"; 
const alphaVantageBaseUrl = "https://www.alphavantage.co/query";

async function fetchData(functionUrl) {
  try {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheetWithTicker = spreadsheet.getSheets()[2]; 
    const tickerSymbol = sheetWithTicker.getRange("I6").getValue();
    const url = `${functionUrl}&symbol=${tickerSymbol}&apikey=${apiKey}`;
    console.log("URL to fetch: " + url);

    const response = await UrlFetchApp.fetch(url);
    if (response.getResponseCode() === 200) {
      return JSON.parse(response.getContentText());
    } else {
      throw new Error(`Unable to fetch data. Err: ${response.getResponseCode()}`);
    }
  } catch (error) {
    throw new Error(`Error occurred during fetch: ${error}`);
  }
}

async function fetchAllData() {
  try {
    const functionUrls = [
      `${alphaVantageBaseUrl}?function=OVERVIEW`,
      `${alphaVantageBaseUrl}?function=BALANCE_SHEET`,
      `${alphaVantageBaseUrl}?function=INCOME_STATEMENT`,
      `${alphaVantageBaseUrl}?function=CASH_FLOW`
    ];

    const fetchedData = await Promise.all(functionUrls.map(url => fetchData(url)));
    console.log("Fetched data:", fetchedData);
      
    } catch (error) {
    console.error(`Error occurred: ${error}`);
  }
}

fetchAllData();

控制台日志 #

URL to fetch: https://www.alphavantage.co/query?function=OVERVIEW&symbol=aapl&apikey=XXXXXX
URL to fetch: https://www.alphavantage.co/query?function=BALANCE_SHEET&symbol=aapl&apikey=XXXXXX
URL to fetch: https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=aapl&apikey=XXXXXX
URL to fetch: https://www.alphavantage.co/query?function=CASH_FLOW&symbol=aapl&apikey=XXXXXX
URL to fetch: undefined&symbol=aapl&apikey=XXXXXX   
Error   Error: Error occurred during fetch: Exception: Bad request: http://undefined&symbol=aapl&apikey=XXXXXX
fetchData   @ Code.gs:19

解决思路 #

• 检查触发器设置：进入脚本编辑器的「编辑」→「当前项目的触发器」，删除所有非手动触发的规则，避免意外触发fetchData且未传参。
• 移除全局执行的fetchAllData()：将全局作用域的fetchAllData()调用改为手动执行（通过编辑器运行按钮）或绑定到工作表菜单/按钮，避免场景化重复执行。
• 给fetchData加参数校验：在函数开头加入判断，拦截无效参数：

  async function fetchData(functionUrl) {
    if (!functionUrl) {
      throw new Error("Invalid function URL provided");
    }
    // 原函数逻辑...
  }
  

• 排查关联脚本冲突：检查当前工作表绑定的其他GAS项目，确认是否存在跨脚本的函数调用冲突。

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