Google Sheets脚本调用Finnhub API时UrlFetchApp.fetch报错求助

问题背景 #

在Google Sheets中编写脚本调用Finnhub API获取^SP500-50指数成分股数据，脚本在UrlFetchApp.fetch(url)环节抛出无效参数错误，但该URL在Postman中可正常返回数据，替换其他API URL时脚本运行正常。

脚本代码 #

var ss = SpreadsheetApp.getActiveSpreadsheet(); //获取绑定此脚本的活动电子表格
var sheet = ss.getSheetByName('Stock Candidates'); //指定要写入数据的工作表标签

var url = "https://finnhub.io/api/v1/index/constituents?symbol=^SP500-50&token=ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg"; //API端点字符串

var response = UrlFetchApp.fetch(url); //调用API端点
var json = response.getContentText(); //获取响应文本
var constituents = JSON.parse(json); //解析为JSON格式

Logger.log(constituents); //在日志中记录数据

var stats=[]; //创建存储数据点的空数组

var date = new Date(); //创建时间戳

//方括号中的数字对应数据实例，最近的调用为[0]，依次类推
stats.push(date); //添加时间戳
stats.push(constituents[0]);

//将stats数组追加到活动工作表
sheet.appendRow(stats);

错误信息 #

Exception: Invalid argument: https://finnhub.io/api/v1/index/constituents?symbol=^SP500-50&token=ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg
IndiceConstituent   @ Code.gs:9 

Postman正常返回的响应 #

{
    "constituents": [
        "META",
        "GOOGL",
        "GOOG",
        "CMCSA",
        "NFLX",
        "TMUS",
        "DIS",
        "VZ",
        "CHTR",
        "ATVI",
        "T",
        "EA",
        "WBD",
        "TTWO",
        "OMC",
        "IPG",
        "PARA",
        "MTCH",
        "FOXA",
        "LYV",
        "NWSA",
        "FOX",
        "NWS",
        "DISH"
    ],
    "symbol": "^SP500-50"
}

原因分析与解决方案 #

错误原因 #

URL中的^属于URL特殊字符，UrlFetchApp不会自动对这类字符进行编码，导致无法正确解析URL，最终抛出无效参数错误。而Postman会自动处理URL编码，因此能正常请求。

解决方案 #

使用encodeURIComponent()方法单独编码查询参数的值，避免手动编码出错，同时修正原脚本中对API返回数据的错误取值：

修改后的脚本：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Stock Candidates');

// 单独编码特殊参数值
var symbol = encodeURIComponent('^SP500-50');
var token = 'ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg';
// 拼接编码后的URL
var url = `https://finnhub.io/api/v1/index/constituents?symbol=${symbol}&token=${token}`;

var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var constituents = JSON.parse(json);

Logger.log(constituents);

var stats = [];
var date = new Date();
stats.push(date);
// 修正取值：API返回的成分股数组在constituents.constituents字段中，转为逗号分隔字符串写入单元格
stats.push(constituents.constituents.join(','));

sheet.appendRow(stats);

额外说明：原脚本中stats.push(constituents[0])是错误的，API返回的是包含constituents数组和symbol字段的对象，并非数组，因此需要通过constituents.constituents获取成分股列表，转为字符串后写入单元格更易阅读。

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