Unable to connect error when trying to Get Data > From Online Services > From SharePoint Online List

Question

Hey

The refresh every n minute option is greyed out in Excel as follows:

As I tried to follow the guide here, using Get Data > From Online Services > From SharePoint Online List. I encountered another issue as follows instead:

Any setting issues that block the connection?

Look forward to getting solutions please!!!

Thanks

Answer 1

Hi klsl,

Sorry for the inconvenience it may have caused.

To troubleshoot the issue you encountered, there’re some suggestions you may take:

1. Update Office to the latest version. With Office updated, some issues would get resolved. So it’s a good idea to keep Office up to date.

Open Excel in safe mode to exclude the issue is related to some third-party add-ins. Then check the result.

3. Perform Office Online Repair to fix the problem.

4. Remove the PowerQuery Cache in the following path:

C:\Users*[UserName]*\AppData\Local\Microsoft\Office\16.0\PowerQuery

Remove all the content under this folder. This ought to reset the PowerQuery setting for Excel. After that, try to import the data again.

**Note:**1. The AppData folder is hidden by default.

2. To prevent important data from being lost, please back up them when deleting the contents of the folder.

And we would like to collect some information to further check it:

1. Which version of Office do you have? After starting Excel, click on File > Account, please take a screenshot of the Product Information part and share it with us.
2. Whether the issue occurred on all workbooks? Or just some certain ones?
3. If you have other SharePoint sites, try to test these sites in case the issue related to certain site.
4. Please provide the detailed steps you take if possible.

Best regards,

Madoc

Answer 2

Hey Madoc

I'm using m365, and had tried on different sharepoint sites (intranet), and the outcome is still the same.

Based in the traffic log, it seems like was being blocked by the security policy. 

Was there any other workaround so that the excel spreadsheet can be refreshed without opening once the users update the sharepoint list?

Thanks 

Klsl

Answer 3

Hi Klsl,

Thanks for your update and I'm glad that you found the cause of the issue.

By default, Excel can refresh the data imported through Power Query only when the workbook is opened. If you think the feature ought to be added, you may consider sending feedback to Excel product team directly, they read all feedback.

And for this situation, we recommend that you contact the administrator of your organization to modify the security policy, which is the most efficient way to solve the problem. How do I find my Microsoft 365 admin?

Best regards,

Madoc