What is the fastest way to list files from a specific Sharepoint folder (including sub folders) ?

[F01536]

Hi All,
To list files there is SharePoint.Files + Path filtering but this is very slow as it needs to filter ALL files of huge SharePoint.
Or SharePoint.Content where you can scroll down the folders which is fast but once you stop in a folder it doesnt show all files from subfolders...

So i wanted to combine both ways ... First use the Content to navigate folders, then from a certain folder list all files.
Copilot is suggesting me the ListFile function below (after "Statements" which is my last folder navigation step)... But that function returns empty table.

Anyone knows how to fix this or another working method ?

Thanks 🙂
Gregory


let
    Source = SharePoint.Contents("SHAREPOINT_URL HERE", [ApiVersion = 15]),
    #"Documents partages" = Source{[Name="Documents partages"]}[Content],
    General = #"Documents partages"{[Name="General"]}[Content],
    Statements = General{[Name="Statements"]}[Content],

   ListFiles = (folder as table) as table =>
    let
          Files = Table.SelectRows(folder, each Record.HasFields(_, {"Content.Type"}) and [Content.Type] = "File"),
          Folders = Table.SelectRows(folder, each Record.HasFields(_, {"Content.Type"}) and [Content.Type] = "Folder"),
          FolderContents = Table.AddColumn(Folders, "Files", each ListFiles([Content])),
          AllFiles = Table.Combine({Files, Table.Combine(FolderContents[Files])})
    in
         AllFiles,
   AllFiles = ListFiles(Statements)

in
  AllFiles

[PwerQueryKees]

SharePoint.Contents is the fastest, but I found it to be unstable, so I switched back to SharePoint.Files for all the (many) workbook I am using it in....

If you want to use SharePoint.Contents, then @lbendlin is right, you need to build your own. Certainly doable, but will probably involve writing a recursive custom function.
With an luck, @lbendlin  is already looking into it!! 😃

[F01536]

Thanks @PwerQueryKees  🙂

Nobody has this function already written ? Not that i am lazy but is definately out of my compentency...

I'm surprised this is not common and not to be found on Web, as this kind of thing is quite general and that could have been used/asked already many times 🙂

have a nice day

[PwerQueryKees]

Never saw it. Ask chatgpt maybe?

[F01536]

ChatGPT ends up with the same ListFiles function i wrote in initial message (like Copilot). You keep saying it doesnt work but he gets lost into it 🙂

[PwerQueryKees]

Typical 😪

[lbendlin]

How To Use OData Filters In Power Automate With SharePoint (zeitgeistcode.com)

[lbendlin]

Another option would be to use the SharePoint REST API with the appropriate OData filter applied.

[F01536]

Asked Copilot about this, he gave me such code but seems to return also empty list 😞

let
// Define the SharePoint site and folder path
siteUrl = "https://XXX.sharepoint.com/sites/XXXXXX",
folderPath = "Documents partages/General/Statements/",

// Construct the API URL
apiUrl = siteUrl & "/_api/web/GetFolderByServerRelativeUrl('" & folderPath & "')/Files",

// Send the HTTP request
response = Web.Contents(apiUrl, [Headers=[Accept="application/json"]]),

// Parse the JSON response
jsonResponse = Json.Document(response)

in
    jsonResponse

[Anonymous]

Hi @F01536 ,

Thanks for lbendlin's and PwerQueryKees's replies.
And @F01536 , I tried it myself and could you please try this M code:

let
    siteUrl = "https://XXX.sharepoint.com/sites/XXXXXX",
    Source = SharePoint.Contents(siteUrl, [ApiVersion = 15]),
    DocumentsPartages = Source{[Name="Documents partages"]}[Content],
    General = DocumentsPartages{[Name="General"]}[Content],
    Statements = General{[Name="Statements"]}[Content],
    ListFiles = (folder as table) as table =>
        let
            Files = Table.SelectRows(folder, each [Content.Type] = "File"),
            Folders = Table.SelectRows(folder, each [Content.Type] = "Folder"),
            FolderContents = Table.AddColumn(Folders, "Files", each ListFiles([Content])),
            AllFiles = Table.Combine({Files, Table.Combine(FolderContents[Files])})
        in
            AllFiles,
    AllFiles = ListFiles(Statements)
in
    AllFiles

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[F01536]

Hi Dino,

Thanks, but this return "The name 'ListFiles' wasn't recognized. Make sure it's spelled correctly" on ListFiles step.
It's only when you add "Record.HasFields(_, {"Content.Type"})" in the Files & Folders lines (like originally posted) that it doesnt give an error but returns blank table.
Note that at "Statements" level, there are no files, only folders but the goal is to list all files "from there" in Statements (if any) + all subfolders. And not generate a random list, it should remain in the standard column form of Sharepoint.Files or .Contents as i need to have the "Content" column and Name, Extention etc to be able to aggregate files...

Anyway, it seems quite complex or impossible so i'll stick to Sharepoint.Files eventhough its pretty slow on large sharepoint.

Thanks for your help and have a nice day

[lbendlin]

Use SharePoint.Contents and implement your own process for subfolder traversing.

[F01536]

Thanks Ibendlin, but if i knew how to make my own process for subfolder traversing, i'm not sure i would have posted this 🙂 

Is there any ressource where a sample is available ? I couldnt find it ... 😞

Best regards