0

I'm thinking about an Excel add-in based on office.js technology (https://learn.microsoft.com/en-us/office/dev/add-ins/overview/office-add-ins) , to handle OAuth authentication against a generic IDP, get an access token, generate new power queries using the token, update existing power queries connections with the token. Queries could be based on dsn-less OBDC and OData strings.

Currently, I have only been able to get some general information about queries existing in the Excel workbook, such as name, last refresh date, number of records.

    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-less ODBC string example

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 string example

let
    Source = OData.Feed("https://host.org/odata.svc/databasename/view_name", [Authorization = "Bearer <ACCESS_TOKEN>" ] )
in
    Source
Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
trick73
  • 1

1 Answers1

0

As you mention in your question, the Query class in the Office JS API has very limited functionality that can read basic information about a query and there is currently not a way to edit the query using the Office JS API.

Jakob Nielsen-MSFT
  • 504
  • 2
  • 8