To homepage To homepage
Question

CData Excel Online not showing shared Excel files

K
Rank
Badge +1

Hi,

We are using the Excel Online connector which is authenticated with a service account and uses delegated permissions to access Excel files on Sharepoint. The idea is that all relevant files will be shared with this account and then loaded into our DWH. The required files are visible on Sharepoint when siging in with the user:

But for some reason, they are not when using the Excel Online connector (with option Show shared documents = ‘True’). I know that the connector uses the /SharedWithMe (OneDrive) call to fetch shared items, since that is what I inferred from the logging.

This call indeed retrieves no results through the Graph Explorer, but the files are visible through another, similar, call on the Graph Explorer (the one from ‘Insights’):

 

Why are files visible on Sharepoint but not on OneDrive? Is there a way to work around this? I have seen use cases where they actually are visible on both Sharepoint and OneDrive and the connector is working properly.

20 replies

S
Rank
Userlevel 3
Badge +3

How are permissions set on this service account for delegated access ?  This could be a good question on MS SharePoint forum using the Graph Explorer example.

K
Rank
Badge +1

Hi Syed,

These are the User consent permissions on the Graph Explorer in AAD:

And the Admin consent permissions on the Graph Explorer:

I think the most important question here is: what causes the files to be invisible through the OneDrive call?

Thomas Lind
Rank
Userlevel 5
Badge +5

Hi Kaj

What about Sharepoint rights like these?

 

I have an app, where I specifically wanted to use Application rights.

It gave me all the files, whereas the one above only gave me the files in my own documents folder.

 

K
Rank
Badge +1

On the Azure App registration it is like this:

 

To be honest, I do not think it has something to do with these permissions. The issue is that the user that we are authenticating with is just not seeing the files on OneDrive, it only sees it on Sharepoint. Since CData uses the OneDrive /SharedWithMe call, we cannot see the file in TX. So the question is: why does the file not appear on OneDrive?

FYI: we have another user which has access to the file in the same way and that user can see it on both Sharepoint and OneDrive… 

Thomas Lind
Rank
Userlevel 5
Badge +5

Hi Kaj

There is no difference between the two users rights, could it be something like the other user opening the files on their computer whereas the other one is not used for anything but authentication.

K
Rank
Badge +1

Not sure what you mean there, we are accessing onedrive.com to check whether we can see the files.

K
Rank
Badge +1

@Thomas Lind, FYI: posted a question on the MS Q&A page on how this sharing on Sharepoint and OneDrive works. Will let you know when I have a response.

Thomas Lind
Rank
Userlevel 5
Badge +5

Hi Kaj

OK, let me know what they say.

You can also try the https://developer.microsoft.com/en-us/graph/graph-explorer and see if you can locate the files through that. Supposedly that is what the CData provider uses behind the scenes.

 

Christian Hauggaard
Rank
Userlevel 5
Badge +5

@KajEmergo has the issue been resolved? did you receive a response in MS Q&A?

K
Rank
Badge +1

Hi @Christian Hauggaard, MS said they are looking into it but no response this far. Will let you know when I have an answer of course!

Christian Hauggaard
Rank
Userlevel 5
Badge +5

Hi @KajEmergo just following up - any update on this?

rvgfox
Rank
Userlevel 3
Badge +1

@Thomas Lind I’ve the ExcelOnline data source defined, but I cannot see the excel files:

 

 

If I define a transfer task, I get the error:

 

 

Thomas Lind
Rank
Userlevel 5
Badge +5

Hi Ramon

It is a table you need to use by creating a managed query table with a where clause that adds the workbookid.

rvgfox
Rank
Userlevel 3
Badge +1

@Thomas Lind Can give me an example?

Thomas Lind
Rank
Userlevel 5
Badge +5

So I chose this ID from the Workbooks table.
?name=billede.png
 
So with that Id that table gives me this.
?name=billede.png
 
You may also be able to make it work using this query.

SELECT * FROM Worksheets Where WorkbookId IN (SELECT WorkbookId FROM Workbooks GROUP BY Workbookid)

Then it should iterate over all workbook ids.

rvgfox
Rank
Userlevel 3
Badge +1

@Thomas Lind In my case, the table Workkbooks it’s empty. Why?

 

Thomas Lind
Rank
Userlevel 5
Badge +5

Hi Ramon

The issue is that your app or user does not have sufficient access.

Here is how it looks for me, note the sheets I can see in the tables area.

I still only really see local files in my own personal onedrive folder for this setup. I do not know why, as I am allowed access to other files than these.

rvgfox
Rank
Userlevel 3
Badge +1

@Thomas Lind I can see now the OneDrive files but not the files that are in the SharePoint...

K
Rank
Badge +1

@rvgfox, is your issue similar to that of mine? If that is the case, maybe you could reply to my question on the MS Q&A page.

@Christian Hauggaard, as you can see on the MS Q&A page I have not yet received an answer from them. Maybe you could also reply to the topic? It is still not clear why this is not working.

Thomas Lind
Rank
Userlevel 5
Badge +5

@KajEmergo 

Great idea, I have been trying to force my setup to only show these as well, but it seems to be rather difficult.

Reply


Terms and ConditionsCookie settings