Can it be done?! Querying files stored in SharePoint from a table of SharePoint site URLs.

[junkpwan]

Hey all, this one is truly baking my noodle.

We have annual reports (.xlsx) for customers shared on different SharePoint sites.

We want to combine some tables stored in those files. The files all have an identifier in them, to help pick them out from the site contents.

We also have a directory stored in an excel table of all those sites' urls (https://company.sharepoint.com/sites/companyA, B, C...  etc).

Can we build something dynamic that queries all those sites from the directory, therefore meaning that when we win / lose customers, we can amend the directory rather than the queries?

[junkpwan]

This sounds ideal.  We've given it a whirl and are coming across this hurdle:

To be able to search through those folders, we need access to the parent folder https://company.sharepoint.com/sites/

Issue is, that we don't have access to that it seems.

Here's the error we get:

Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (404 FILE NOT FOUND)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (404 FILE NOT FOUND)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (404 FILE NOT FOUND)"

[lbendlin · Accepted solution]

Use separate queries per site.

[junkpwan]

Thanks Ibendlin, that was our plan B, then amend the queries and appended table with customer losses and wins.

[lbendlin]

Yes, that is a standard pattern. Use the "SharePoint Folders"  connector, filter for the files you want to ingest, and then write your ingestors and use them in Table.AddColumn  before combining the results.