Replacing SharePoint List Connector in Canvas with Flow without premium licence (2021-05-06)

 

This article was inspired by the tutorial Power Apps SharePoint Document Library Browser from Shane Young. The demonstrated App suffers from the lack of delegation capabilities of the SharePoint list connector in Power Apps. That limits the proper working of the solution to libraries with a maximum of 500 (unless the default is increased, but 2000 is a hard max) files and folders.

In this post a different approach is demonstrated which uses a Power Automate flow instead. It still has limit boundaries, but at least it can be scaled as long as each folder has no more than 500 items.

 

The Power Automate Flow

 

Flows with a basic licence can only return strings. The http response action can return a json object, but that requires a premium licence. As it turns out, it is impossible - at least for me - to convert a JSON string to an object in a Canvas app. A CSV string however proves to be relatively simple.

The flow that replaces the connector covers three functionalities: Get Items to retrieve all files and folders in one folder of a SharePoint library, Upload to create a new file in the selected folder and ItemCount to retrieve the total number of files and folders in a SharePoint library. The upload functionality is also demonstrated by Shane in his tutorial PowerApps upload file to SharePoint document library.

 

Parameters Description
siteurl The target SharePoint site, for example https://desktopservices.sharepoint.com/sites/prj-dts
libraryrelativeurl The relative path of the library and folder, for example Shared Documents/2021/
uploadfilename Only applicable for the Upload action, the name of the file to create
uploadfilecontent Only applicable for the Upload action, the uploaded content of the file to create
action

This parameter controls what the flow actually will do and what to return. In all cases a string variable "retval" is returned.
Get Items: retval contains the item details of a SharePoint folder in a CSV format. Linefeeds are replaced by a pipe symbol to make the handling in the App easier.

Upload: retval contains the full name of the created file.

ItemCount: retval contains the number or items in a list / library.

 

From a Canvas App perspective, the run-syntax is as follows:  DTSSample-SharePointLibraryBrowser.Run([site url, library path, upload filename, upload file content, action).

The flow diagram below is shown in an iframe. Click here to open the source html file in a new window.


The most notable part is the Get Items branch. Instead of a SharePoint Library connector, the Flow uses two Send HTTP Requests to SharePoint. Using the siteurl and libraryrelativeurl parameters the item details (name, whether it's a folder, the medium thumbnail) is retrieved. The result is transformed in a JSON format and then transformed to a CSV table. CSV splits the lines with new lines which are difficult to replace in the Canvas App and is for that reason replaced with a pipe symbol (|).

 

The Canvas App

 

Starting with the App as demonstrated in the instruction video from Shawn, the  following steps transform the source from the SharePoint connector to the Flow.

1. Copy the screen in the App using the SharePoint connector

2. Connect the flow

3. Change the OnVisible formula

UpdateContext({varFolderPath:"Shared Documents/",varSiteUrl:"https://desktopservices.sharepoint.com/sites/prj-dts",varUploadControlY:1031,varRefreshTrigger:!varRefreshTrigger})

4. Add a toggle control, add the OnChange event formula

Set(flowdataarray,Split('DTSSample-SharePointLibraryBrowser'.Run("https://desktopservices.sharepoint.com/sites/prj-dts",varFolderPath,"none","none","Get Items").retval,"|"));
            Set(flowdataarray,LastN(flowdataarray,CountRows(flowdataarray)-1)); Set(flowdataarray,FirstN(flowdataarray,CountRows(flowdataarray)-1));
            ClearCollect(VarCollection,
            ForAll(flowdataarray,
            {
            Name:First(Split(Result,",").Result).Result,
            IsFolder:Last(FirstN(Split(Result,","),2).Result).Result,
            Thumbnail:Last(Split(Result,",").Result).Result
            }
            ))

and update the Default property to varRefreshTrigger

5. Replace the Items property of the gallery to

Sort(Sort(VarCollection,Name,Ascending),IsFolder,Descending)

6. Replace the Image formula

If(ThisItem.IsFolder="True","data:image/svg+xml;utf8, " & EncodeUrl("some svg string containing a folder"),ThisItem.Thumbnail)

7. Replace the Visible formula in the > button

ThisItem.IsFolder="True"

8. Update the 'Up' icon formula

UpdateContext({varFolderPath:Concat(FirstN(Split(varFolderPath,"/"),CountRows(Split(varFolderPath,"/"))-2),Result,"/") & "/",varRefreshTrigger:!varRefreshTrigger})

9. Hide Toggle control