使用单元格中的 URL 将 XML 或 JSON 数据导入 Excel

Question

我需要从我的 Excel 电子表格中进行一个 API 调用，该调用将根据电子表格中输入的数据具有各种参数。

我已经正确格式化了 URL 并将其放在一个命名的单元格中，ApiUrl但是当我尝试在 Excel 中设置数据源时，它似乎总是要求我在设置向导中提供 URL，而不是引用电子表格中的数据（例如这个预先格式化的 URL）。

我确信一定有办法做到这一点...但我一直找不到。

我正在使用最新的Office365。

如果其中一种格式能够提供我想要的功能，我的 URL 可以格式化为返回 XML 或 JSON 数据。如果我需要将每个参数设置为对单元格的引用，而不是预先编写的 URL 字符串，那么这也是可以的。

一旦我将数据放入工作簿中，我就可以从那里获取数据……我只需要帮助从预编的 URL 字符串中获取数据。谢谢！

URL 类似如下：

https://developer.nrel.gov/api/pvwatts/v6.xml?api_key=MY-API-KEY-HERE&address=&system_capacity=12.5&azimuth=&tilt=&array_type=1&module_type=1&losses=10.5

这将返回格式如下的 XML（或稍加修改为 JSON）数据： https://developer.nrel.gov/docs/solar/pvwatts/v6/

我只是想获取“ac_monthly”数组……然后将这 12 个值插入到工作表的各个单元格中。

Answer 1

您可以在 Power Query 中编辑查询。

下面是使用命名范围（“apiURL”）中的内容作为 api URL 的示例代码：

let
    apiAddress = Excel.CurrentWorkbook(){[Name="apiURL"]}[Content]{0}[Column1],
    Source = Json.Document(Web.Contents(apiAddress)),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

一步步：

1. 在单元格中输入链接
2. 为单元格指定自定义名称
3. 开放电量查询
4. 创建一个新的空白查询：主页 - 新来源 - 空白查询
   
5. 打开高级编辑器
   
6. 复制上面的代码（如果需要，调整范围名称）
7. 关闭 Power Query（选择“保留更改”）