"User was not authorized" using RelativePath option of Web.Contents() for excel files on Sharepoint

[scottkruyswyk]

I am building a Power BI report that needs to be published to web with automatic refreshes. The report I am building pulls data from 10 excel documents, all located in separate document libraries on sharepoint online. Each excel document has 10 tabs, each with 2 named ranges that I need to pull data from - so that would result in about (10 documents) * (10 tabs) * (2 ranges) = 200 queries to pull all data in manually. Clearly a no-no.

I created a couple functions that would allow me to use a dynamic query to get all this data with only a few queries - the code for one of those functions is below:

(Location as text, Period as number) as table =>
let
	location_trimmed = Text.Remove(Location, {".", " "}),
	Source = SharePoint.Files("https://xxxxxx.sharepoint.com/teams/teamOne/" & location_trimmed, [ApiVersion = 15]),
	#"File Name" = location_trimmed & "MillScorecard.xlsx",
	#"MillScorecard" = Source{[Name=#"File Name",#"Folder Path"="https://xxxxxx.sharepoint.com/teams/teamOne/" & location_trimmed & "/Shared Documents/"]}[Content],
	#"Imported Excel" = Excel.Workbook(#"MillScorecard"),
	#"Range Name" = "Period " & Number.ToText(Period) & "!P" & Number.ToText(Period) & "_All_data",
	All_data_DefinedName = #"Imported Excel"{[Item=("Period " & Number.ToText(Period) & "!P" & Number.ToText(Period) & "_All_data") ,Kind="DefinedName"]}[Data]
in
	All_data_DefinedName

However, I soon found out that Power BI online doesn't support automatic refreshes for dynamic queries:

https://community.powerbi.com/t5/Service/Problem-with-automatic-refresh-data-source-not-supporting/t...

I found an article that used Web.Contents options as a hack get around this, so I figured I would give it a shot - using the RelativePath option, it seemed like I could get the same kind of hack done. 

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

My resulting code:

(Site as text, Location as text, Period as number) as table =>
let
    location_trimmed = Text.Remove(Location, {".", " "}),
    relative_url = Site & "/Shared Documents/" & location_trimmed & "MillScorecard.xlsx",
    url = "https://xxxxxx.sharepoint.com/teams/teamOne",
    Source = Excel.Workbook(Web.Contents(url, [RelativePath=relative_url]), null, true),
    #"Period 1!P1_All_data_DefinedName" = Source{[Item="Period 1!P1_All_data",Kind="DefinedName"]}[Data]
in
    #"Period 1!P1_All_data_DefinedName"

When I tried to invoke this function - after entering my SharePoint credentials - I get this error:

When I change the function to use the entire url as the base url in the Web.Contents() call, it works perfectly fine.

Is there any way I can get around this? Does anybody know of a better way to go about doing this??

[v-ljerr-msft]

Hi @scottkruyswyk,

Based on my research, there is a limitation to use base path and RelativePath within the Web.Contents, that the base path by itself must be accessible, or the connection will fail.

So you may need to contact your SharePoint Administrator to make sure you have access to both the full path and base path to make the connection successful in this case.

Regards

[scottkruyswyk]

@v-ljerr-msft Thanks for your response. The account I was using to sign in has full access to the base path and all of the sub-sites/document libraries that I am trying to access.

[v-ljerr-msft · Accepted solution]

Hi @scottkruyswyk,

I found a similar thread here which mentioned a workaround that change the URL by replacing https with http. Could you go and check if it helps?

Regards

[scottkruyswyk]

@v-ljerr-msft It worked! Thanks so much.