When you use the UI to import files from SharePoint, you’ll end up with the Sharepoint.Files function. This function can become fairly or super slow when you use it on large SharePoint sites. This is due to the fact, that it will retrieve metadata for ALL files that lie on the site. Meaning: The root site whose URL you have to enter as the function argument. So I’ve developed a better way for File import from SharePoint.
A faster alternative is the function SharePoint.Contents. This function will read much less metadata and that seems to make it faster. But it comes with a different navigation experience: It basically only allows to select files from one folder.
Therefore I’ve created 2 functions that overcome those limitations.
SharePoint.GetAllFilesInFolder and Sharepoint.GetFile
Sharepoint.GetAllFilesInFolder allows to enter a folder path and will trigger the import of all the files from that folder including all subfolders. So you don’t have to navigate to all of them individually or create multiple queries for them.
The functions reference a parameter or query named “RootPath”. This needs to exist or being created in your file. Unfortunately it cannot be passed as an ordinary function parameter, as it would cause refresh problems in the service. The root path is basically the name of your SP site, generally everything before the 5th slash (“/”) in the URL. To get the syntax right, you should retrieve the folder name from the details-section like so:
The second function Sharepoint.GetFile is a convenient function if you want to import one specific file. It allows you to simply enter the full path to the desired file. No need for manual navigation. Also, this can speed up the load process as well, as navigation steps that are manually created, can slow down the import as well.
Get all files from folder
Get single file
How to use
Both functions use the fast SharePoint.Contents function (it took my queries to run half as slow than before) and use some dynamic logic to retrieve the data. But as a side effect, the formula firewall seems to kick in sooner. So there might be some query redesign necessary if you’re going to use this function on existing models.
These are a pretty new function and I haven’t tested it extensively yet. But the potential performance gain will probably strongly depend on the size of the SharePoint sites you’re running them on.
I’d be curious to hear your experiences with these functions.
Enjoy & stay queryious 😉