Google Sheets中如何基于列引用批量生成IMPORTJSON()调用数组

• 当前使用IMPORTJSON()自定义函数实现Google表格内的JSON接口数据拉取
• 经测试，内置函数HYPERLINK()可配合ARRAYFORMULA实现基于列内容批量拼接生成链接的效果，参考公式如下：

=ARRAYFORMULA(
    HYPERLINK("https://api-apollo.pegaxy.io/v1/pegas/"&QUERY(
        {A2:A},"SELECT * WHERE Col1 IS NOT NULL")
        )
    )

• 期望复用该逻辑批量构建IMPORTJSON()调用数组，批量拉取所有接口返回的指定字段数据。

两次不同写法的尝试均运行失败：

1. 直接拼接URL传入IMPORTJSON()配合ARRAYFORMULA使用，公式写法如下：

=ARRAYFORMULA(
    ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/"&QUERY(
        {A2:A},"SELECT * WHERE Col1 IS NOT NULL"),
        "/energy", "noHeaders")
        )

返回404状态码错误，报错信息如下：

Exception: Request failed for 
https://api-apollo.pegaxy.io returned code 404. 
Truncated server response: <!DOCTYPE html> <html lang="en">
     <head> 
        <meta charset="utf-8"> 
        <title>Error</title> 
        </head> 
    <body> 
        <pre>Cannot GET /v1/pegas/923195,https://api-apo... 
            (use muteHttpExceptions option to examine full response) 
            (line 217). 

从返回内容可判断，实际发起请求的URL将整列ID拼接为带逗号的长字符串，未按行拆分单独请求。

2. 提前拼接生成完整URL列表存放在E2:E区域，再直接传入单元格区域调用，公式写法如下：

=ARRAYFORMULA(ImportJSON({E2:E}))

返回URL长度超限错误，报错信息如下：

Exception: Limit Exceeded: URLFetch URL Length. (line 217).

该错误由传入的单元格区域被识别为单个超长字符串导致，长度超出了URL合法限制。

原生IMPORTJSON()自定义函数本身不支持数组类型入参，无法被ARRAYFORMULA触发逐行迭代调用，和内置函数HYPERLINK的数组适配逻辑存在差异。

通过编写适配数组入参的自定义批量拉取函数，替代原生IMPORTJSON()实现批量请求，以下是根据实际需求微调后的可用代码：

function importAllJSONArray(url) {
  var listUrls = Array.isArray(url) ? url.flat() : [url]
  var listXpath = []
  var result = []
  listUrls.forEach((address, i) => {
    var prov = []
    var json = JSON.parse(UrlFetchApp.fetch(address).getContentText())
    if (i == 0) { 
      listXpath = Object.keys(json); 
      result.push(listXpath)
    }
    listXpath.forEach(xp => {
      prov.push(json[xp])
    })
    result.push(prov)
  })
  return result
}

使用方式：

• 将上述代码粘贴到表格的脚本编辑器中保存，完成权限授权后即可作为自定义函数在单元格内调用
• 调用时直接传入存放完整接口URL的单元格区域即可，函数会自动逐行发起请求，将第一条返回结果的JSON键作为表头，后续逐行填充对应字段值

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