Forum Discussion
Get Excel data from several sharepoint folders
So I finally managed to get all the data from the different folders in Sharepoint.
What ended up being the solution was setting up individual queries for each of the sharepoint folders, then appending them all as one new query.
From there I used jennratten's tip on text filters, and was left with only the files I wanted.
Hello - I can't see the depth of the Sharepoint url you are querying so I can provide only general info.
The depth of the Sharepoint url needed to get the files you are wanting depends on a few factors.
Basic Info (I think this is likely your problem, assuming you have permission to site(s))
Each Team in the organization has it's own Sharepoint site root url. Note, the team name and private channel names included in the URL omit spaces.
This structure will return files that are in any team channel that is not private.
https://Organization.sharepoint.com/sites/Team/
This structure will return files that are in a Team channel that is private.
https://Organization.sharepoint.com/sites/Team-PrivateChannelName/
When filtering using Text.Contains, keep in mind that it is a case sensitive search unless otherwise specified. There may also be things unaccounted for, such as spaces/no spaces, spelling errors, etc. At a minimum, I recommend using a case-insensitive search, omit spaces and include the file extension you are targeting.
In this snip below, I was looking for pdf files containing "Color Palette" in the file name and want to be sure the results were case-insensitive and also exclude any files that were not pdf. Note, the file extension can also appear as upper or lower case.
So to use this method in your situation, the script would be:
Table.SelectRows(Source, each Text.Contains( Text.Replace ( [Name], " ", "" ), "runninghours", Comparer.OrdinalIgnoreCase ) and Text.Contains ( [Extension], ".xls", Comparer.OrdinalIgnoreCase ) )Using Text.Contains with .xls ensures that xls and xlsx files are both returned in the results.
It looks like the step before filtering for a substring in your file name was also a filter step. Be sure you didn't do anything in that step that would have eliminated the potential results.
Additional Info
The files may have various versions and may include files that include 'running hours' in the file name that should not be included in the search results. Those are just some other scenarios you may want to account for in your script.
Hi Jennratten, thanks for your detailed reply!
Good to know about the text filter being case sensitive, I thought otherwise.
Applying your steps I managed to find a copy of one of the files stored in another sharepoint area. Which means I now know the text filter works how I want.
I apologize if I misunderstand you, but I still cant quite grasp where I go wrong.
I struggle to understand the sharepoint url, depth and so on. (I struggle to understand sharepoint entirely to be honest)
Our organization has one "root" the way I figure - "https://Organization.sharepoint.com/".
When using this url only, I find files stored on our branches area, but cant seem to access other branches files. The other branches have an added "/sites/branchname" in their url when I locate the files on sharepoint. The first part of the url is the same.
I thought this would be kind of like searching through a hierarchy; Search from the top and locate files downwards through subfolders. This seems to not be the case?
jennratten wrote:It looks like the step before filtering for a substring in your file name was also a filter step. Be sure you didn't do anything in that step that would have eliminated the potential results.
The first filter step was applying an extension filter for excel files 😊
I'm glad this info was helpful. To answer your question, when you query the organization's root, you only get the files that are shared at the organization level, not any underlying Team sites. In the example below, first I have queried the org root and see only 24 files and then query for a Team site and see over 1000 files which include all files for the Team (less files within private channels).
Org Files (unfortunately no hierarchy is included that can be expanded)
Team Files
If you create a table with a column containing the Team site names, I can help you use one query to connect to each.
Thanks for further clarifying, very useful information!
Appreciate the offer of a single query, but it works allright the way its set up now 🙂
Again, thanks for the great help!