Excel 'Get Data From Web' loses Authorization header

Question

Hi, I work for a company that allows users to generate a HTML feed of data from various tables we hold within our software. In previous versions of Excel, users were able to generate a base64 encoded authorization string within our software, and then import the HTML into Excel via the 'Get Data from Web' functionality, by selecting the Advanced option, and adding an Authorization header, with the base64 string as the value.

This has stopped working in the latest version of Excel - when trying to identify why on our side, it seems that Excel performs two HTTP requests:

• The first request contains the custom Authorization header

The second request, which appears when the Navigator window pops up, does not include the Authorization header, resulting in our feed returning a 401 unauthorized error, and the user seeing a 'We were unable to retrieve the contents of the web page' message in Excel.

I've validated that this workflow continues to work as expected in an older version of Excel (2016), so we don't believe our software has caused the issue.

My questions are:

Is the change around the headers not being used in the second request something that was intentional, and if so is this likely to be fixed?

If not, what is the suggested way of allowed headers to be passed into a Get Data From Web request?

If the change was intentional, why was it made?

Hi, I work for a company that allows users to generate a HTML feed of data from various tables we hold within our software. In previous versions of Excel, users were able to generate a base64 encoded authorization string within our software, and then import the HTML into Excel via the 'Get Data from Web' functionality, by selecting the Advanced option, and adding an Authorization header, with the base64 string as the value.

This has stopped working in the latest version of Excel - when trying to identify why on our side, it seems that Excel performs two HTTP requests:

The first request contains the custom Authorization header

The second request, which appears when the Navigator window pops up, does not include the Authorization header, resulting in our feed returning a 401 unauthorized error, and the user seeing a 'We were unable to retrieve the contents of the web page' message in Excel.

I've validated that this workflow continues to work as expected in an older version of Excel (2016), so we don't believe our software has caused the issue.

My questions are:

Is the change around the headers not being used in the second request something that was intentional, and if so is this likely to be fixed?

If not, what is the suggested way of allowed headers to be passed into a Get Data From Web request?

If the change was intentional, why was it made?

Thanks

Answer 1

Hi,

Did you use the Power Query to get Data from Web or From Web (Legacy) option?

I suggest you try to From Web (Legacy) option, please go to Data tab- Get Data- Legacy Wizard- From Web (Legacy):

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.