Lately I had a requirement to show the documents from SharePoint related to a Dynamics 365 implementation. The Canvas App or Custom Page should be able to show an overview of more SharePoint sites. I’m a newbie to SharePoint documents integration, so I’ve started to search the blogs and docs out there. Since I had to research, experiment and figure out some points by myself, I’m writing this blog as a note to myself, hoping that it might help you too. In case you know a better way, would be nice to leave me a note.

Thousand thank you to Reza Dorrani! I’ve learned so much from his awesome videos, including the idea for this implementation!

First approaches

This solution is based on GraphAPI, but before that I’ve tried out some other solutions.

  • Using the SharePoint (Document Library) Connector and a Cloud Flow (Power Automate) as I’ve learned in this video from Reza Dorrani
    • I had 2 issues with this solution. First the SharePoint Connector is connected to one site, so I cannot connect to a bunch of SharePoint sites which can change any time. And then the flow was a little slow for my requirement.
  • Using only the SharePoint (Document Library), as learned from another video from Reza. I’ve learned awesome tricks in this blog.
    • My problem with this approach was still about not being able to dynamically connect to different sites.

The solution I’ve picked

In the final solution I rely (again) on an approached showed by Reza Dorrani: direct call of the GraphAPI from Power Apps using Office365Groups

For that I’ve used the GraphAPI docs. You can test the requests using the Graph Explorer.

The Canvas App is not the final one, but here is how I test the connection to different sites. First I let the user input the name of the site.

On the “Retrieve” button, I can start retrieving the site ids. The Button “OnSelect” looks like this

//Search the siteId by name: sites?search={name}
Set(
    varSearchSiteByName,
    Office365Groups.HttpRequest($"https://graph.microsoft.com/v1.0/sites?search={txtSiteName.Text}","GET","")
);
//extract the siteId from the response
Set(varSiteId,First(varSearchSiteByName.value).id);
//get the drives for this siteId: sites/{siteid}/drives
Set(
    varDrive,
    Office365Groups.HttpRequest("https://graph.microsoft.com/v1.0/sites/" & varSiteId & "/drives","GET","")
);
//get the driveId from the drives (could be more, so I filter here)
Set(
    varDriveId,
    First(
        Filter(
            Table(varDrive.value),
            Value.name = "Dokumente"
        )
    ).Value.id
);
//create a collection with all the children 
//could be subfolder or documents
//request schema: sites/{siteId}/drives/{driveId}/root/children
ClearCollect(
    colDocsToLoop,
    Table(
        Office365Groups.HttpRequest("https://graph.microsoft.com/v1.0/sites/" & varSiteId & "/drives/" & varDriveId & "/root/children","GET",""
        ).value
    )
);
//this index will be user to loop over the collection
Set(varIndex,1);

Get the documents from the subfolder: recursive loop

The next challenge was to loop over the subfolders and retrieve the documents from there. For a recursive loop over the collection, I’ve used the idea from Éric Sauvé based on a slider control: here is his blog.

For that I place a slider control on my app

The “Max” property of the slider control, is set to the rows count of my collection:

And the “Default” will be the varIndex, which I’ll use for looping

I’ll go through all the records inside my colDocsToLoop, and for each position I’ll make a request to the GraphAPI, to get the children from the next level, and add them in the same collection. Because I add more records to the collection, and because I change the varIndex each time, this will trigger an OnChange for the Slider control. When the varIndex reaches the count of colDocsToLoop, I’m done.

This is the slider “OnChange” handler

//get the item from position varIndex
Set(varItem, Index( colDocsToLoop, varIndex));
//if the current item has children (.folder.childCount>0), make the next request
//schema sites/{siteid}/drives/{driveid}/items/{currentItemId}/children
//since I want the thumbnails too, I'll add the ?expand=thumbnails
If(
    varItem.Value.folder.childCount > 0,
    Collect(
        colDocsToLoop,
        Table(
            Office365Groups.HttpRequest(
                "https://graph.microsoft.com/v1.0/sites/" & varSiteId & "/drives/" & varDriveId & "/items/" & varItem.Value.id & "/children?$expand=thumbnails",  "GET",""
            ).value
        )
    )
);
//if the varIndex is not at the end, increment the varIndex
//this will trigger the next OnChange for the slider
If(
    varIndex < CountRows(colDocsToLoop) ,Set(varIndex,varIndex + 1)
);

I can use the slider to show the progress of my loop, by making the “fill” and “handle” color transparent.

And here is the result (have a look to the slider showing the progress, and the collection counter shown on the bottom of the page):

In the Gallery I show only the documents (filter the folders out).

We’re working with untyped objects. As Reza showed us, we can find the content of the GraphAPI response, using the Monitor).

The definition of the Gallery components: the name

…and the image

Right now I show only the documents but we cannot see in which folder they are located. As a next step, would be nice to implement a dataset PCF which takes this collection and shows it in a tree.

Preview the documents

The last part is to view the documents. For that I’m using a IFrame PCF (I have another one, but you could use the one from Yash Agarwal from PCF Gallery for instance. As Reza showed in one of the videos, we can call GraphAPI to retrieve the preview URL for the document.

For the Gallery, OnSelect I have this code (base on GraphAPI preview docs)

Set(varItemId,Gallery5.Selected.Value);
//schema sites/{siteid}/drives/{driveId}/items/{itemId}/preview
Set(
    varPreviewUrl,
    Office365Groups.HttpRequest(
        "https://graph.microsoft.com/v1.0/sites/" & varSiteId & "/drives/" & varDriveId & "/items/" & varItemId.id & "/preview", "POST", "" )
)

The IFrame url will use the varPreviewUrl from this requests (.getUrl)

And here is the result