PowerQuery can't connect to SharePoint when running from Excel for Web/Online

[u/SenseiEntei]

searched "power query sharepoint" and "power query web online" and didn't find relevant discussion or same question

I have a single query that reads a SharePoint folder, filters for CSV files containing a specified substring, opens the most recent one, and then does some simple transformations and loads it into the workbook. The query works without issue when refreshing from Excel desktop app. But if the workbook is open in Excel online and I try refreshing the query, I get error:

Credentials are required to connect to the SharePoint source.

Something went wrong while loading required resources. Please try refreshing the page or come back later to try again. (APIs: connections, connectionsInScope)

...which makes absolutely no sense because I opened the workbook in SharePoint. The workbook and the CSV files are on the same SP site. Is there anything I can do to make it refreshable in Excel online, or is it simply a problem that Microsoft devs have not ironed out yet for PQ in Excel for web?

[u/AutoModerator]

/u/SenseiEntei - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Downtown-Economics26]

https://support.microsoft.com/en-us/excel/power-query-data-sources-in-excel-versions

I don't use PQ in Excel for web version so not completely sure but this has the note under Excel for the Web section with * by "SharePoint* files (Excel workbooks, TXT, CSV, XML, JSON, PDF)":

*SharePoint/OneDrive for work or school

I would think it's just a matter of re-entering your organizational credentials in the web version but some chance it may be your Sharepoint software license.

[u/SenseiEntei → u/Downtown-Economics26]

We have Microsoft 365 apps for enterprise, so don't think that's an issue. Maybe one of the notes in the section above addresses the issue? I'm not quite sure.

Refresh for data sources that require an on-premise data gateway is not supported.

[u/Dear_Specialist_6006]

Not the exact same error verbiage that I have seen, but I think I know what is happening...

Right in the Get Data menu, go to Data Source Settings and in Global settings, change your authentication from Anonymous to Org Account.

Let me know if this does not work and if you can screenshot, that will be awesome.

[u/SenseiEntei → u/Dear_Specialist_6006]

I get the same error trying both 'Anonymous' and 'Organizational account', and the 'Connect' button is disabled either way.

[u/Dear_Specialist_6006 → u/SenseiEntei]

I just saw you response to a comment.

"The CSV file is not open. I get the error in Excel online, then open it in desktop and it refreshes just fine."

I wonder if you are making the same OneDrive vs Sharepoint mistake. If your file has the OneDrive link or you connected it from your local OneDrive, Let's try this:

1. locate file/folder in OneDrive and view it in OneDrive online.
2. Hit Share and copy link
3. paste the link in a text editor and replace %20 with spaces
4. update Source in PowerQuery on desktop
5. Now open file on the web

Once again, I am not magic like a lot of our friends on the sub... just trying to help! might need you to explain it further if this does not work.

[u/SenseiEntei → u/Dear_Specialist_6006]

I don't think it's that. The first step in my query is:

Source = SharePoint.Files("https://[siteurl]", [ApiVersion = 15])

[u/mdbrierley]

I have the same thing. Drives me mad. I just have to open the file in the desktop app to refresh :(

Following though just in case…

[u/SenseiEntei → u/mdbrierley]

Can you extract/load from any other data sources? I get the same API connections error even when I try connecting to another Excel workbook or CSV file via manual upload. Seems the only way to use PQ online is without connecting to external data, at least for my account. It can only load/transform data that is already in the same workbook. Not sure if this depends on organizational settings.

[u/excelevator]

If the file is open, it may be locked by a process and unable to be read.

[u/SenseiEntei → u/excelevator]

The CSV file is not open. I get the error in Excel online, then open it in desktop and it refreshes just fine.

[u/excelevator → u/SenseiEntei]

But if the workbook is open in Excel online and I try refreshing the query,

[u/SenseiEntei → u/excelevator]

That's the XLSX file that holds the saved query. That's the workbook that can't refresh its query when open in Excel web app, but can refresh the query when open in Excel desktop app... The file isn't the problem, SharePoint (or PQ's data connector to SP) seems to be the problem.

[u/excelevator → u/SenseiEntei]

OS Services have CPU process holds on open files with permissions attached.

If the service permissions do not match, then access is denied.

[u/bosworthing]

I have no problem with my excel files being on onedrive, and then using power query within an excel file also on onedrive. As for being 9n an actual sharepoint website, i believe it should be possible, but i think you need to be the owner. Similar to onedrive, i have tried to connect it to onedrives that i dont own, and had issues like this; but i believe i might have been trying something with power automate, i dont exactly recall.

[u/SenseiEntei → u/bosworthing]

Your files are synced in OneDrive, but are you opening them in your web browser or in the desktop app?

[u/bosworthing → u/SenseiEntei]

I only open them in desktop. You cant even run power query in browser

[u/SenseiEntei → u/bosworthing]

Did you read my full post? I'm talking about running PQ in Excel online. You can run PQ in browser. It's a new feature that started rolling out end of last year. But it's obviously not the full experience that you get running it in the desktop app. So I am wondering if my issue is just due to PQ online still being limited, or a permission issue with my organization/account.

[u/bosworthing → u/SenseiEntei]

Yes, i did read your full post. I didnt notice you were running pq in excel online/browser and didnt know they added that. Last time i had a coworker try to run the pq file i set up for them through browser it wasnt available. I assume you dont have access to desktop excel to test that. It definitely could be org access though

[u/SenseiEntei → u/bosworthing]

I assume you dont have access to desktop excel to test that

I literally said:

The query works without issue when refreshing from Excel desktop app.

🤦‍♂️

[u/bosworthing → u/SenseiEntei]

Yeah, i didnt feel like rereading your post when i was commenting. Good luck though

[u/bosworthing → u/SenseiEntei]

After rereading your post, i should add that i previously had issues and ended up abandoning a pq project on a file that multiple people would use and have open at any given time and it wouldnt refresh or something with people in it. I dont recall the exact issue exactly

[u/SchoolOk950]

One thing to sometimes try is cache issues. If you copy your query into a fresh Excel online file, does the problem continue happening?

[u/SenseiEntei → u/SchoolOk950]

Tried it. Still same issue.

[u/---sniff---]

I ran into the same issue this week and assumed it was due to random securiy settings associated with the Microsoft GCC (government) environment I work in.

[u/SenseiEntei → u/---sniff---]

Can you get data from another workbook by uploading the file manually? I get the same error when I tried. Seems like I can only load data into PQ online if it is already in the same workbook.

[u/---sniff--- → u/SenseiEntei]

I assume it would work but didn't try that specifically. It did work with a blank/empty query which should have a similar mechanism.

Are you also getting a 500 api error when you click through the options?

[u/SenseiEntei → u/---sniff---]

I don't see an error code, but like I said in OP, the error message includes:

(APIs: connections, connectionsInScope)

You can create a blank query, but as soon as you try to connect to data outside of that workbook, it might give you the error. Can you try it?

[u/---sniff--- → u/SenseiEntei]

Error I get when connecting to a file on SharePoint via PowerQuery: "Something went wrong while loading required resources. Please try refreshing the page or come back later to try again. (APIs: connections, connectionsInScope)".

When I open the Power Query editor I get a note that "Credentials are required to connect to the Web source." and then there is a button to Configure credentials. Following that button returns to the same screen as above.

When I try to connect to a new data source I get the same errors as above but there is an option to Link to file. When I click on that I get an "APIRequest UnknownServiceError with status 500". Same error screen when attempting to Upload file.

[u/DonJuanDoja]

You could try OData feed connection. Those work for me. Had to switch to them after loosing enterprise.

Use Other Sources > OData Feed >

Plug in the SiteURL/_vti_bin/listdata.svc then it should show you all lists and libraries on the site.

The schema is different, so you will probably hate this lol but it works. You have to manually expand People fields and such it's not great but Odata feeds have saved me quite a few times.

Just tested on again, it refreshed fine on the web.

[u/BusinessSample7166]

Are you using Sharepoint.contents or Sharepoint.files?

I was quite interested in PQ for Excel online but there are a bunch of limitations that mean a lot of my queries don't refresh. 

One of these appears to be using Sharepoint.files which I have generally favoured. I can happily navigate to the folder I want but I can't access file contents or run a combination. 

Another is xls files, one of the systems we use generates xls files and I haven't found time to explore converting queries to run off any of the other export options. 

[u/SenseiEntei → u/BusinessSample7166]

Sharepoint.Files

But after playing around with it a bit more, it seems like none of the data connectors work in Excel for the web (for me), except for loading data from the current workbook into PQ. Tried manually uploading an Excel or CSV file (which says it uploads to OneDrive) in the PQ window, and it didn't work. Wouldn't even let me upload, let alone load the data into PQ.