I am using Excel2010 Power Query From Web to call an Auth-required API which returns data in JSON.
Since this API requires Auth (username and password) to access, I set it in excel's Data Source Settings -> Credentials -> Basic, as taught in this url:
It's working and JSON data can be successfully retrieved from the API, and displaying in excel.
However, my main difficulty is that I have to share this excel file to other non-technical colleagues, who very likely will find it difficult to manually set the auth as mentioned above.
I would like to ask if there are any ways (any pre-setting I could make) such that when the colleagues receive this excel file, they won't have to manually set the Auth?
In other words, is there any way to make it more user-friendly such that when the non-technical staff opens that excel file, the Auth-required API will be called and result can be displayed immediately, without them having to make any further setting/configuration? Thx for your kind help!
Update:
I'm thinking if it's possible to create an excel login prompt for username and password, where username and password will then be automatically used to update credentials in power query data source setting.
If this is feasible, then when those non-technical staff open the excel files, the login prompt will show up and the staff can enter the username and password for that API used in power query.
I would like to ask if anybody can kindly explain if my thought is possible? Thx a lot!
