基于Office.js的Excel插件能否修改Power Query的无DSN ODBC/OData连接字符串？

核心功能目标 #

• 对接通用身份提供商（IDP）完成OAuth认证，获取访问令牌
• 生成包含访问令牌的新Power Query
• 更新现有Power Query的连接信息，支持两种类型：
  • 无DSN ODBC连接字符串
  • OData连接字符串

当前已实现功能 #

仅能获取工作簿中现有Power Query的基础信息（查询名称、最后刷新日期、加载记录数），相关代码如下：

const queries = context.workbook.queries;
queries.load("items");

await context.sync();

context.workbook.worksheets.getActiveWorksheet().getRange("A1").values = [[`${sheet.name}`]];

queriesn = queries.items.map( q => q.name);
queriesrc = queries.items.map( q => q.rowsLoadedCount);
queriesrd = queries.items.map( q => q.refreshDate);

连接字符串示例 #

无DSN ODBC连接字符串 #

let
    Source = Odbc.DataSource("DRIVER={ODBCDRIVER Unicode(x64)};SERVER=host.org;DATABASE=databasename;PORT=7777;SSLmode=prefer;ReadOnly=0;<OTHER DRIVER-SPECIFIC ODBC PARAMATERS>;
    UseOAuth2=1;
    AccessToken=<ACCESS_TOKEN>", 
    [HierarchicalNavigation=true]),
    databasename_Database = Source{[Name="databasename",Kind="Database"]}[Data],
    databasename_Schema = databasename_Database{[Name="databasename",Kind="Schema"]}[Data],
    VIEW_NAME_View = databasename_Schema{[Name="VIEW_NAME",Kind="View"]}[Data]
in
    VIEW_NAME_View

OData连接字符串 #

let
    Source = OData.Feed("https://host.org/odata.svc/databasename/view_name", [Authorization = "Bearer <ACCESS_TOKEN>" ] )
in
    Source

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