Once upon a project we needed to get data from different SiteCollections within O365 SharePoint to display in a PowerApp.

The goal of the project was to create a structure within O365 SP to store the employee's hr-relevant documents. We decided to create a hubsite and a SiteCollection for each and every employee wherein all the documents will be stored. The employees were to get an app which would allow them to request reading rights on their file as well as enable them to see all their certifications and add new certificates to their file.

The challenge was to create a single PowerApp, that would show only the relevant information of the current logged in user from his/her file.

Since PowerApps does not support dynamic datasource adding and switching, we needed to find a workaround. We managed to find a way to have the needed functionality within one single PowerApp utilizing only one datasource.

We achieved it by utilizing PowerApps, Power Automate and SharePoint and not a single premium connector (which were out of bounds due to licensing restrictions).

 

Realization (schematic)

 

The solution we came up with works as follows:

From within the PowerApp we trigger a PowerAutomate Flow. 

  1. The Flow queries a list within the central HubSite and 
  2. gets back the URL of the current user's SiteCollection.
  3. The Flow then accesses the user's SiteCollection and queries a document library
  4. which is returned to the app as a csv file.

Within the PowerApp we can now display the contents of the document library inside a gallery control.


PowerAutomate Flow to display the contents of the document library inside a gallery control

Image 1: Dataflow

 

Technical realization

Ingredients:

  • 1 SharePoint List (for mapping purposes)
  • 1 PowerApp (Canvas)
  • 1 Power Automate Flow
  • n SiteCollections

 

Preparation

Set up a SharePoint List which will serve as the central mapping List. Herein you will store IDs/SiteNames as well as URLs of all Sites within the Hub. In our case it was the users-list where we stored all Names, eMails, SiteURLs and so on. 

Like this:

 SharePoint List to store IDs/SiteNames, URLs of all Sites with the Hub.
 
Image 2: Exemplary SharePoint List
 

Next, create a Canvas app within PowerApps. Therein set the onStart function to set a variable with the current user's email, as well as the identificator you chose  to identify the corresponding user's SiteCollection.

 
Codesnippet onStart function
Image 3: onStart Function
 

We now have a variable containing the current user's mail address as well as one variable containing the site name of the current user. Both of those will be needed further down the line.

 

The Flow (Power Automate)

So after taking the preliminary steps in Power Apps it's now time to move to Power Automate and create a Flow. Best way to do so is directly from Power Apps. Switch to the Actions Tab and click on Power Automate:

 

 
Switch to the Actions Tab and click on Power Automate
Image 4: PowerApps Ribbon
 

In the opening side bar/window choose "create new flow". If you have chosen your Timer Control the new Flow should be automatically associated with it.

 

 
Image 5: PowerApps add Flow
 

I called mine GetDocsFlow and as you can see it is associated with my Timer Control.

 

Constructing the Flow

As with almost every single Flow that you'll be creating, the first thing is to initialize some variables.

 

 

  1.  You should initialize a variabel which contains the "base" URL of your tenant with the corresponding path your SiteCollections are stored in like so: https://[yourtenant].sharepoint.com/sites/ and append the parameter from your Power App containing the name of the SiteCollection of the current user.

 

 
Append the parameter from your Power App containing the name of the SiteCollection of the current user
 
 

 

 

2. Initialize a variable in which you set the name of the library you want to get the information from. In our case it was a library where the employee's certificates are stored.

 
Set Library Name
 
 

 

  

3. Initialize a variable to hold the queried data (*.csv)

 
Initalize variable for Docs
 
 

 

4. Get the items from the list/library with the SharePoint Get Items action, where you put your library variable from step 2 and the variable holding your Site Address from step 1 in the corresponding fields.

 
Get Items from list/library with SharePoint
 
 


5. Use the Data Operations action Create CSV Table to create a CSV table from the queried output

 
Create CSV table
 
 

 

6. Next set the DocString variable to the Data Operation output

 
Set DocString
 
 

  

7. And finally send the result of the Flow as an response back to the PowerApp

 
Respond to a PowerApp or flow
 
 

Having completed the Flow you will need to make some more configuration steps in your PowerApp.

 

Start Screen

On the app's start screen, you will need to set a timer control, which in turn will trigger the flow that will conseuqently query the needed SharePoint SiteCollection.

Set the AutoStart property to true. We have set the duration to 2000ms but you should be able to go lower.

In the OnTimerEnd property you will trigger the flow. We also added an IF-statement to navigate to the next screen once we got a response from the flow.

 
Codesnippet timer control which in turn will trigger the flow that will consequently query the needed SharePoint SiteCollection
 
 

Dashboard Screen

Since our app is an app designed to show multiple types of information for the logged in user, we do have a sort of dashboard screen where all that information is being aggregated. 

On that screen I have placed another Timer Control to trigger the processing of the response from the Power Automate Flow, as well as a gallery to show the result of the processing.

  1. The Timer Control is set to AutoStart = true and onTimerEnd triggers the script shown on the right hand side.

What we are doing here is creating a collection (DocCollNew) through splitting the CSV data we received as a response from the Power Automate Flow. Depending on where in your CSV the needed data is stored, you will want to change the parameters accordingly.

Finally we check if an entry is already in the collection and remove it with the final IF-statement.

 
Creating a collection (DocCollNew) through splitting the CSV data
 
 

The last step is to simply add the new collection as datasource to a gallery control and select the appropriate fields to display.

Example: ThisItem.ID or ThisItem.Title.

We also got the item link in our collection and use that with Launch(Link) to enable the users to open the documents from within the app.

Questions? Suggestions? Get in touch with our expert!