PowerApps & SharePoint – The Ultimate User Experience – Dynamic Data Sources – Graph – Power Automate

3. PA_Graph_DriveItems Flow

This Flows returns a collection of files, folders, and / or other items for any given Document Library folder being queried.

GET /sites/{site-id}/lists/{list-id}?select=name,lastModifiedDateTime&expand=columns(select=name,description),items(expand=fields(select=Name,Color,Quantity)) 

PowerApps Trigger

This Flow expects 3 input parameters to be passed from PowerApps. A PowerApps Trigger action instantiates this Flow.

Get Drive Items

The subsequent step in this Flow, does nothing more then specify an If statement to calculate the value of the Uri property for the next step in the Flow:

• Get Drive Items Uri ( String )
  The value passed into this input parameter of the Flow is ignored if the odata.nextLink input parameter passed to the Flow is anything other than false.
  • The value of this input parameter should be in the following format:
    {siteId}/lists/{listId}/root
    
    As was with the relationship between this Flow and the PA_Graph_Site_List_Libraries Flow becomes pertinent.
    
    In order to construct this parameter value to pass to the Flow from PowerApps for any given Document Library in a site surfaced in the result set of the PA_Graph_Site_List_Libraries Flow run, append /root to the value of the SiteIdListLibraryId as is defined in Select Return Fields step of said Flow.
    
    For example, where the value of SiteIdListLibraryId may be the Uri: tenant.sharepoint.com,095f032c-ed85-48ad-93ec-8f5f836f1e1e,270246dc-f8e3-4c8f-8d4d-55ad9cb9826a/drives/b!Mcmxe9WTWO6cJoEDamPmR-V2ulUleSiLqEPpqd_jB7mKA7V_I00gRK5LMTPY9c-Z
    
    Quite simply concatenate the value of SiteIdListLibraryId with /root in your app in order to construct the value of this parameter when you instantiate this Flow from your app.
    
  • When querying the files and subfolders contained in a folder within a specific Document Library, the value of this input parameter will be in the following format:
    {siteId}/drives/{driveId}/items/{itemId}
    
    The only different between retrieving a list of files and folders of the root folder of a Document Library as opposed to retrieving a list of files and folders with any given folder contained in a Document Library is that for the root folder for the Document Library you would append /root to value of the SiteIdListLibraryId whereas for a folder you instead append /items/{itemId} where {itemId} represents that Id of the folder.
    
    The folder Id should be exposed in a previous Flow run of the exact same Flow as defined the Select Return Fields step of the Flow when the files and subfolders of the parent folder of this folder was queried. Per Select Return Fields step in the Flow, the Id field value will be the {itemId} where the ContentType field value for that row is Folder.
    
    Similarly as with example above, the value of SiteIdListLibraryId may be: tenant.sharepoint.com,095f032c-ed85-48ad-93ec-8f5f836f1e1e,270246dc-f8e3-4c8f-8d4d-55ad9cb9826a/drives/b!Mcmxe9WTWO6cJoEDamPmR-V2ulUleSiLqEPpqd_jB7mKA7V_I00gRK5LMTPY9c-Z
    
    Concatenate the value of SiteIdListLibraryId with /items/{itemId} in your app where {itemId} is the Id of the folder as is returned by this same Flow for rows returned where the Content Type field value is Folder in order to construct the value of this parameter when you insatiate this Flow from your app.
    
    The format of {itemId} value for a folder should look along the lines of 01XLBSU8RMIOSMVWYMABCDEPPK4YXHHMC
    
    In effect to transverse all the folders, subfolders and subfolders etc. within a Document Library irrespective of the number of levels deep they may be in that Document Library, the only part of this input parameter that will change as you query any folder in the {itemId} of that folder.
    
• Odata Query Parameter ( String )
  The value passed into this input parameter of the Flow is ignored if the odata.nextLink input parameter passed to the Flow is anything other than false.
  
  Technically you “should” be able pass in any valid Odata system query parameter(s) for this input query parameter of the Flow.
  
  In the demo app showcased in this blog I by and large primary only make of the &$top=### page size (aka threshold) Odata query string parameter such that the Flow can then automatically provide to paging functionality as has been demonstrated in the app showcased in the video demo of the overall solution described in this blog.
  
  • For example, to return the first 250 files and folders within any given Document Library or Folder with a Document Library, the value of this parameter would be &$top=250. If so desired you can increase the page size to as high as 5000, and in doing so essentially override the maximum data row limit of 2000 rows you would otherwise natively be constrained / limited to in PowerApps. Just because you can though doesn’t mean you should!
    
  • As alluded to, you can equally construct any valid alternate Odata system query options for this input parameter when you instantiate this Flow from your app. I suspect, based on the testing I did for the demo app I created, that only the $select, $expand, $top and $filter=startswith() query parameters are supported.
    
    For example: &$top=250&$filter=startswith(name,'Demo‘)
    This will accordingly return the first 250 rows in the Document Library folder being queried where the name of the file or folder (or package) starts with Demo. A OneNote file is deemed to be a type of package.
    
• odata.nextLink ( String )
  body('Get_Drive_Items')?['@odata.nextLink']
  
  If, having called this Flow previously for any given folder in a Document Library whereby the total number of files and subfolders in that folder exceeds the page size as was specified for the Odata Query Parameter in the previous Flow run, the response for the previous Flow run will include a URL in the odata.nextLink property as is defined in the Select Drive Items Return Fields of this same Flow.
  
  This URL in turn references the next page of results and accordingly enables the functionality with your app to handle automated paging, regardless of how many rows (files and subfolders) the Document Library folder being queried may contain (i.e. easily 10k+).
  
  Should you pass any value other than false for this input parameter to the Flow, the other input parameters passed to the Flow will be ignored. With that said the other input parameters are nonetheless required / mandatory input parameters. As such you can is essence pass in any rubbish to this Flow for the other 2 input parameters as those parameters will in any case be ignored if this input parameter is present (i.e. not false).
  
  This is far easier to explain by way of example:
  
  • Assuming your initial call to this Flow requests a collection of up to a maximum of 50 files and folders in the root folder of a specific Document Library, the call to run the Flow from PowerApps can be represented as follows:
    ClearCollect(
SiteLibraryItems,
PA_Graph_DriveItems.Run(
Concatenate( v_SiteIdListLibraryId, "/root" ),
"&$top=50",
false
)
)
    The value of the v_SiteIdListLibraryId variable would be value of the SiteIdListLibraryId property derived from the collection of Document Libraries, Lists and Subsites returned having previously called the PA_Graph_Site_List_Libraries Flow for the corresponding site.
    
  • Assuming the total number of files and folders in the Document Library folder being queried exceeds the page size specified in the initial call to this Flow from PowerApps (per this hypothetical example being 50), the OdataNextLink property in in each row of the collection returned from the previous Flow run for the same Document Library folder being queried will contain the URL to retrieve the next page of results.
    
    Should you henceforth instantiate this Flow from your app in order to retrieve the next batch of up to 50 files and folders for that same Document Library folder, all that is required is to specify the odata.nextLink URL when you instantiate the Flow for each subsequent batch retrieved.
    
    You will however need to strip the https://{tenant}.sharepoint.com/ prefix in the OdataNextLink property in any given row in the collection into which the result set from the previous Flow run for the aforementioned Document Library folder was returned.
    
    Subsequent calls to the Flow from PowerApps retrieve the next pages of results and so-forth can accordingly be represented as follows:
    UpdateContext({
v_odatanextLink :
Substitute(
First( SiteLibraryItems ).OdataNextLink
"https://{tenant}.sharepoint.com",
""
}),
ClearCollect(
SiteLibraryItems,
PA_Graph_DriveItems.Run(
"false",
"false",
v_odatanextLink
)
)

Get Drive Items

This step takes the ‘Get Drive Items Uri‘ input parameters passed to the Flow as well as the ‘odata.nextLink‘ input parameters passed to the Flow. If the value of the ‘odata.nextLink‘ input parameter is false, it means a new drive / document / library / subfolder or subsite is being queried. If however any value for this parameter is anthing other than false as is passed into the Flow, the Flow assumes this to be the URL query to retrieve the next batch / page, following a previous flow run for the corresponding entity.

If( not ( equals( triggerBody()['Initializevariable-odatanextLink_Value'], 'false' ) ), triggerBody()['Initializevariable-odatanextLink_Value'], concat('_api/v2.0/sites/',triggerBody()['GetDriveItems_Uri'], '/children?$expand=thumbnails($select=large),listitem($expand=fields)&$select=*,MediaServiceMetata', triggerBody()['GetDriveItems_Uri_1'] ))

The subsequent step in this Flow is a Send an HTTP request to SharePoint action to call the SharePoint Graph v2 List children of a driveItem API. This API to quote:

“Return a collection of DriveItems in the children relationship of a DriveItem.“

In layman’s terms when calling this API the collection of files and folders within the Document Library folder (or package) being queried is returned.

Alternatively if the odata.nextLink input parameter to the Flow is present (i.e. not false), the next page / batch of files and folders within the Document Library folder (or package) being queried is returned.

Like Einstein, I spent an exorbitant in fruitless attempt to eliminate this step in the Flow.

Select Drive Items Return Fields

“It wasn’t easy. Einstein tried every solution he could think of, and nothing worked. Almost out of desperation, he began to consider a notion that was simple but radical.”

Einstein’s Relativity Explained in 4 Simple Steps

In the Select Drive Items Return Fields step of the Flow you can select whichever properties of the files and folders within any Document Library folder being queried may be of relevance for you to consume with your app(s).

The most important / significant field properties that the overall solution showcased in this blog consumes which in turn enabled me to implement a completely dynamic lightweight user interface mimicking a subset of the functionality that would otherwise only be possible to accomplish using the native SharePoint and / or OneDrive sites (excluding bespoke custom developed applications or 3rd party vendor solutions) include:

• OdataNextLink
body('Get_Drive_Items')?['@odata.nextLink']
  
  Enables automated paging in your apps.
  
• DownloadUrl
item()?['@content.downloadUrl']
  
  This property exposes a (temporary) pre-authenticated URL for each file returned in the result set of the Flow run.
  
  • On face value with the greatest of ease you could, for example, add a Download icon to a Gallery control displaying the collection of files retuned by the Flow and set the OnSelect property of that icon to:
    Launch( ThisItem.DownloadUrl )
    
    If this was the only purpose for which you could use this property for in your apps it would nonetheless be of great value for use cases where you may perhaps require such functionality.
    
  • This blog however is about showcasing how incredibly simple it can actually be possible to implement numerous features with your apps that defy logic insofar as how long App Makers have been requesting such functionality in PowerApps.
    
    With that said, would you be unbelievably astounded if you could use this property to render any PDF documents you may have in any SharePoint Document Library?
    
  • Furthermore wouldn’t it be beyond belief if the only thing you need to app is set yup to use this woubt be set the Document property of a PDF Viewer control to the DownloadUrl surfaced by this Flow for PDF documents in the Document Library folder queried? And finally to to bed what had been one longs still outstanding furture request (aka ideos) put forwer on PowerApps Commumunity thas this should
    
    Perhaps as simple as:
    Gallery1.Selected.DownloadUrl
    
    Fact or fiction? ?
    
• Thumbnail
if(empty(item()['thumbnails']),'',item()['thumbnails'][0]['large']['url'])
  
  The value of this property is incredibly significant as it relates to an extensive amount of functionality as is showcased in the video of demo app for this blog.
  
  • On face value, the value of this property and insofar as how it was intended to be used, seemingly simply returns the URL of a large (800px x 800px) thumbnail image of perhaps a photo stored in a Document Library, given the expression used that returns this property in the result set for any Document Library folder being queried implies such:
    item()['thumbnails'][0]['large']['url']
    
  • With a little bit of magic however we can transform (tongue in cheek) the thumbnail URL returned for this property to enable monumentally more functionality within the apps you may create.
    
    For example, given the following (shortened) URL for this property value:
    https://...-mediap.svc.ms/transform/thumbnail?provider=spo&inputFormat=jpg&cs=fFNQTw&correlationId=GUID&docid=https%3A%2F%contoso%2Esharepoint%2Ecom%2F%5Fapi%2Fv2%2E0%2Fdrives%2Fb%21LR...Gwl%2Dh...bm%2Fitems%2F01.....HCL%3Ftempauth%3DeyJ…aXw0%26version%3DPublished&width=800&height=800
    
    • Display renditions of photos stored in SharePoint at whatever resolution you so desire. In your app simply substitute the width and height query string parameters and values in the URL with any resolution / quality you so desire.
      
      Albeit that the Graph API does in fact support specifying a custom thumbnail size, doing so would require you to add an additional input parameter to the Flow where you would need to specify the exact dimensions of the thumbnail URL property return for all the rows return in the result set of the Flow run.
      
      That may be perfectly acceptable where your only requirement was to display a static size thumbnail image of all files or photos returned by this Flow in a Gallery control in your app and nothing more.
      
      If however you perhaps had an additional requirement for your app whereby users of the app would like to click on a specific item / row in that gallery such that a significantly higher quality rendition of corresponding item should then be rendered in an Image control in, for example, a popup window in your app, you would accordingly need to include an additional Flow to return a new “alternate” thumbnail URL for that item selected in the Gallery.
      
      Why bother with all that unnecessary complexity when you can simply hack the thumbnail URL for any item in your gallery ??
      

      It’s so simple to be wise… just think of something stupid to say and then don’t say it.

      Homer Simpson

      
      In order that the rendition of the photo is scaled in accordance with the dimensions of the original photo stored in SharePoint, all you need to do substitute the width and height query string parameters at run time in your app for any given thumbnail URL in the result set returned by the Flow with whatever desired width dimension you may want. The dimension of the height of the image rendered in your app will then automatically scale accordingly. The height query string parameter is only necessary for scenarios where your requirement may be to crop any given thumbnail image.
      
      Thereafter it’s as set simple as it gets. For example, in the popup window containing an Image control that appears when a user clicks on a specific row in that Gallery set the Image property in that Image control to:
      Substitute( Gallery1.Selected.Thumbnail, “&width=800&height=800”, “&width=2048&height=2048” )
      
      Fact or fiction? ?
      
      The thumbnail URL property will equally be exposed for numerous types of documents returned in the result set of this Flow.
      
      For example, URLs to thumbnail images of the first page of any Word document, PowerPoint presentation, HTML file etc. will be surfaced in this property.
      
      Equally, as with the thumbnail URLs generated for actual photos stored in SharePoint, the quality / resolution of the thumbnail images returned for different types of documents can be substituted at run time within your app and set to any value you may choose.
      
      Fact or fiction? ?
      
  • Taking this to an even higher level, the thumbnail URLs generated contain a thumbnail authentication token such that images rendered in your app consuming the thumbnail URL property will equally work regardless of whether that app is launched within a web browser session or alternatively from mobile devices.
    
    Fact or fiction? ?
    
  • As if that wasn’t enough ?, this same thumbnail URL property can be used within your apps to generate a PDF versions of numerous types of documents stored in SharePoint on the fly at runtime with the greatest of ease.
    
    All that is required is to substitute transform/thumbnail in thumbnail URL generated for any document returned in the result set with transform/pdf instead. There is absolutely nothing else you need to do in your app other than to set the Document property in a PDF Viewer control to the transformed thumbnail URL. The ultimate hack of all hacks ?.
    Substitute( Gallery1.Selected.Thumbnail, "transform/thumbnail", "transform/pdf" )
    
    No ways I hear… Fact or fiction? ?
    
• FieldsArray
  The value of this property is intended for advanced app developers only. One the most complex features requested from the PowerApps is the idea suggested for is to provide some type of capability to enable App Makers to specify Dynamic Data Sources (84+votes).
  
  In fact even beyond this particular idea suggested, numerous other ideas have been put forward to specify including Let PowerApps Connect to a Document Library (376+ votes), SharePoint Folder as a DataSource (269+ votes), and various other idea suggested to expose custom metadata fields defined in any given SharePoint List or Document Library for that matter.
  
  This particular PA_Graph_DriveItems flow, as well as the PA_Graph_ListItems flow and the PA_Graph_Site_List_Library_Columns flow collectively can essentially enable App Makers to implement all this functionality, and that functionality is in fact showcased in the video of the demo app created for this blog. One notable constraint is that unfortunately Data Cards have to bound to a specific data source, whereas as the solution as is detailed in this blog relies on Collections because collections can be populated from dynamic schema definitions.
  
  In the demo app showcased, I in essence leverage the value of the FieldsArray property exposed by this flow which contains a JSON array of every single metadata field defined for any List or Document Library for each item returned by both this PA_Graph_DriveItems flow and equally for the PA_Graph_ListItems flow.
  
  By returning a JSON array in a single property of the result set returned by both flows effectively negates the typical requirement to define a static schema for this property, and alternatively this property is defined as an Array in the Response action Schema definition.
  
  The complexity of exposing this content in PowerApps lies in the fact that there is PARSE JSON function in PowerApps so as app developer in order to expose this content, you’ll need to write your own code to transform the JSON Array returned in the FieldsArray property into a Collection you can thereafter utilize within the app you are creating.
  
  With that said though, the complexity in exposing this content in your does not end there.
  
  In order for you to determine the type of field for each property per item returned in the FieldsArray collection, you’ll need to correlate that collection with the actual schema definition for that given List or Document Library, which is in turn exposed / returned in the result set of the PA_Graph_Site_List_Library_Columns flow for that List or Document Library.
  
  And as if that wasn’t enough, you equally need to thereafter link each sub-property value exposed in the Fields property returned by either the either the PA_Graph_DriveItems flow or the PA_Graph_ListItems flow as required as Fields property of each row returned contains the actual value of the corresponding metadata fields defined for that List or Document Library.
  
  I did forewarn that this property is genuinely intended for advanced app developers only. However, of significance though is that in the demo app I created for this blog, this particular functionality has in fact been implemented and thus is possible, and is equally showcased in the video demo of the app.
  
  By all means reach to me if you’d like assistance on implementing this kind of functionality in apps for your own organization.
  
  Understandably in publishing a blog such as this one, I certainly would hope to generate some income for the effort I’ve put into it ?.

Response

Should you add or remove properties to return to PowerApps in the Select Drive Items Return Fields step of the Flow, it is monumentally important that you equally modify the schema in the final Response step of this Flow to reflect those changes.

You will not be able to add this Flow, or any other Flow for that matter, to any PowerApp if the schema definition for even one field in that flow does not specify a strict explicit type definition for that field as will be reflected in the Schema property of the Response action step.

By example – correct:

            "Id": {
                "type": "string"
            },
            "Attachments": {
                "type": "boolean"
            },

By example – incorrect:

            "Id": {
                "type": "string"
            },
            "Attachments": {
            },

Furthermore in the event that you do in fact modify the schema in the final Response step of this Flow, for every app that may be consuming this Flow you will equally need to unassociate / delete the Data Source connection to this Flow in each of those apps, subsequent to which you can then add the Flow back to all of those apps.

Up next: 4. PA_Graph_ListItems Flow