Excel : Unable to retrieve values. 401 Unauthorized

[VICL]

Hi Everyone,

I'm exploring the Flow for some excel file automation.

I'm unable to get the Step with Excel in Flow works.

I added the IP address as suggested in Sharepoint & OneDrive Admin (https://docs.microsoft.com/en-us/power-automate/limits-and-config#ip-address-configuration)

Still unable to resolve the issue of "Unable to retrieve values. 401 Unauthorized" on Document Library field.

Appreciate any help and ideas. Thanks. 

My Connections in Flow are connected:

[v-alzhan-msft]

Hi @VICL ,

Could you please take a try to update the connection ar add a new connection for the List rows present in a table action in your flow?

If the error code 401 appears and the error message contains "Unauthorized", the failure is always an authentication error. You could usually fix an authentication error by updating the connection.

More details please check this doc for a reference:

https://docs.microsoft.com/en-us/flow/fix-flow-failures

Please take a try and if issue couldn't solved please take a try to create a new flow with good network status.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VICL]

Hi Alice,

Thanks for your advice. I can select the excel file after adding new connection on the Step now.

But the Step was failed after Test run.

Any idea? thanks.

The Body of Error Output:

{

"status": 401,

"message": "您沒有在瀏覽器中開啟這個檔案的權限。\r\nclientRequestId: 9e78df61-991c-4c95-b3f8-69ee39f6a731\r\nserviceRequestId: c808affb-686c-429a-b8ce-20b3ff417490",

"error": {

"message": "您沒有在瀏覽器中開啟這個檔案的權限。"

},

"source": "excelonline-sea.azconn-sea.p.azurewebsites.net"

}

"您沒有在瀏覽器中開啟這個檔案的權限。" means: "You do not have permission to open this file in your browser"

[v-alzhan-msft]

Hi @VICL ,

What is the browser you using now?

Could you please take a try to change the brower such as Chrome or Edge to see if the issue still exists?

Please take a try with the "List rows present in a table" action, make sure the file haven't opened by anyone when the flow runs, and let me know if the issue still exists.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VICL]

I'm using Chrome Version 80.0.3987.122 (Official Build) (64-bit), test run failed on both Chrome or Edge.

The file is not opened by others or sessions.

Same error returned.

Here is my connection:

Another Error when Trying "List Rows present in a Table ",  problem cannot be fixed even after adding new connection

[v-alzhan-msft]

Hi @VICL ,

Please create a table to the excel file then your "List rows present in a table" action could find the table:

https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac6...

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VICL]

Hi Alice,

But same error on retrieving values:

If i enter Table name: "trade" manually and run test, failed again:

[v-alzhan-msft]

Hi @VICL ,

Have you choosed the file from the Picker in the File field?

We need to choose the file from the Picker.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VICL]

Yes, the file was chosen from the picker. I tried several times and start over by re-create the Step but same error returned "Could not retrieve values" on Table field.

[v-alzhan-msft · Accepted solution]

Hi @VICL ,

Sorry for I couldn't reproduce the issue and couldn't help you solve the problem, I suggest that you could contact Microsoft flow for troubleshootng your problem.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathan3]

@VICL 

Hello,

Any luck finding a solution to this problem?  I am having the same issue.

Thanks,

Nathan

[VICL]

Hi Nathan,

My issue had been resolved by Flow Support, below is their reply:

".. there are several factors that might affect the operation of a Flow including:

• Cache issue from server side – we do have mechanism to periodically refresh the cache; thus it’s usually temporary.
• Cache issue on client side – Need to clear the browser’s cache.
• Synchronization delay – after refreshing the connection, it might take some times for it to take effect. "

I still not sure about the root cause of problem, after reporting the issue to Flow Support and reproduced the problem, they asked me to retry few days later and problem was solved. I guess it should be some internal security issue between Excel vs  OneDrive & Sharepoint. 

I would suggest if anyone encountered the same issue, please contact Flow Support via support ticket.

Hope this help and Good Luck 🙂

[Nathan3]

@VICL 

Thank you very much for your reply.  This solved my issue!

Thanks,

Nathan