Log In
Register
Home
Active Member
November 18, 2019
Offline
Hi there,
I've been using Power Query in Excel to access Sharepoint lists in mine and clients Office 365 tenant(s) for a while now using the Get Data / From Online Services / From Sharepoint Online List option specifying the site URL ie. https://mytenant.sharepoint.co.....s/sitename together with Organisational logon and all's worked well.
Recently the "From Online Services" option has now greyed out which I believe is because I changed my subscription to a Microsoft 365 Business Premium as opposed to what was Enterprise E3 so first question is .. is that why? In other words, does a Microsoft 365 Business Standard / Premium licence NOT include the "From Online Services" option?
Rather odd if this is the case as for SMB's with <300 users this is MS recommended license and also I've been doing work for a non-profit and just subscribed for them to the free 10 user license Microsoft 365 Business Premium that Microsoft provides for non-profits to provide their key workers with Office, specifically Excel to allow them to report of Sharepoint List(s) data which we use for their internal order/customer app etc. .. hence my change of subscription to test all works ok!
Assuming this is the case (no "From Online Services" option) I've been trying to access Sharepoint List(s) using the "From Web" option (which incidentally some web posts seem to imply is the "new way" to replace "From Online Services" ?) using same URL and Organisational logon that worked before but I'm getting nowhere as I just get "We couldn't authenticate with the credentials provided. Please try again." message trying to connect.
So question(s) are ..
a) Can we use the "From Web" option to connect to Sharepoint Lists?
b) If so, do we need to use a different URL perhaps?
c) If not, is there anyway to connect to Sharepoint List in Excel with Microsoft 365 Business Premium licence?
Hope you can help and thanks in anticipation 🙂
Derek
VIP
Trusted Members
December 7, 2016
Offline
Hello,
As far as I have been able to find out it seems this functionality to use Get data from online services comes with E3 license. I have not used it myself, but perhaps you can fetch the SharePoint list data using PowerApps. See Phil’s blog post for an example.
https://www.myonlinetraininghu.....troduction
Br,
Anders
Active Member
November 18, 2019
Offline
Hi Anders,
Thanks for the thought, we do indeed already access via PowerApps for app purposes but specifically want to retrieve list data in Excel for models etc.
Best regards,
Derek
July 16, 2010
Offline
Hi Derek,
Business Premium doesn't have the SharePoint List or Folder connectors. You need Enterprise licencing for that feature. You can see the connectors available with each Excel licence here. I'm not aware of a workaround to get data from SharePoint without an Enterprise licence, sorry.
Mynda
Active Member
November 18, 2019
Offline
Hi Mynda,
Thanks for that link, useful to know exactly what's with what so to speak!
Seems a drag to me that Microsoft 365 Business Premium at least (as opposed to Standard) doesn't provide connectors to Sharepoint Lists/Folders as I'm sure many SMB's (under 300 users) will have these licenses and also be using Sharepoint in their tenants so to deprive them of access seems odd but anyway ...
... it got me thinking though as it's quite possible to use the Export to Excel on a Sharepoint List which obviously produces a ".igy" query file which then loads fine into Excel so even standard license can access Sharepoint List ... so next thought ...
... can you open an Excel file produced with Power Query / Get Data / From Online Service / From Sharepoint List again with standard license Excel and yes you can indeed - plus refresh data etc. so ...
... if you edit that Query in Advanced Editor it's obviously just a bunch of 'M' code and taking the relevant bits did some testing and think I have the solution 🙂 in fact couple of different ways but shortest is as follows :
In standard license Excel (that lacks "From Online Sources" option) choose Get Data / From Other Sources / Blank Query which opens a new empty Query with the Source step open, into which then enter '= Sharepoint.Tables("https://mytenant.sharepoint.com/sites/mysite", [ApiVersion = 15])' and press the tick which then presents list of lists (will prompt for authentication first time if not already signed in) and then just click the "table" in the Items column for the required List and hey presto your then at the same stage as one would be having used From Other Sources / From Sharepoint List 🙂
So it appears that all the "From Sharepoint List" option is doing shortcutting the above in a way and in fact not much of a shortcut either!
I'd be interested in your thoughts on this as I can't find an issue with this approach but haven't done extensive testing so there maybe some gotchas but logically speaking it should be ok as it's just 'M' code so should be fine?
Hope this also helps others coming across this issue/complication.
Derek
July 16, 2010
Offline
I agree, Derek, Microsoft's licencing is frustrating and we MVPs have been telling them for years, but their marketing department have other ideas. The best you can do is escalate your views with them direct and hopefully it will get through. If you want to send me an email I can certainly send it to the Excel team, so they are aware of yet another frustrated customer.
As for your workaround, it sounds viable, but you'll need to do extensive testing to make sure there aren't any gotchas. It's not something I can test as I have an Enterprise Microsoft 365 licence.
Mynda
1 Guest(s)
