I had someone reachout to me recently around how to use the timer in Power Apps to get updates on a flow. This is called a 'Polling' API, and basically it works by the App asking the API (in this case a flow), "have you done yet?".
So I thought I would pull together a "How to", with a few extra cool techniques in the mix.
The demo will:
- Select an excel file in the app
- Upload to a flow
- The flow will loop over every row and do something
- The App will get a update on the progress (while not freezing the app)
There are 3 main steps/parts Im going to go through:
- File Upload
- Processing Flow
- App Polling Updates
1. File Upload
If you look for a file upload component you won't see it, but that's because you are not looking hard enough 😉 If you add a form and link it to a SharePoint list you will see a 'Attachment' item. Simply copy and paste it outside of the form.
You just then need to delete the parameters for:
- Items
- ToolTip
And Change:
- DisplayMode to DisplayMode.Edit
- BorderColor to Color.Black
Now here comes the interesting bit, getting the file data from the attachment.
First of all the component can accept multiple files, so its an array. Getting the one file we need we can use the Last() function:
Last(atFile.Attachments).Value
.Name for file name, .Value for the file content
This will then give you something like this:
appres://blobmanager/6dd2692f34654245adef789de4250aba/2
Not much use for creating a file, we need to convert it to base64. Luckily we have JSONFormat for this, but not quite. For some reason it doesn't work when referencing directly from the attachment component. But if you put that into an image, and then reference the image it works 🤷♂️
So it goes:
- image- image parameter:Last(atFile.Attachments).Value
- Convert image output parameter to JSON
The end expression is something like this:
Set(BinaryData, JSON(imHolder.Image,JSONFormat.IncludeBinaryData));
Next we get something like this:
"data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDBBQABgAIA"
but a lot longer
We don't want the metadata or the ", so we need to trim the start and end to get:
UEsDBBQABgAIA
To do that I split by "base64,", creating a 2 item array. I then use the second item, using Index(), and use the left to trim off the last character.
Set(vsBinaryData,
Left(
Index(
Split(BinaryData,"base64,")
,
2
).Value
,
Len(
Index(
Split(BinaryData,"base64,")
,
2
).Value
)-1
)
);
Finally we pass the file name and the file content to a flow, with the flow returning a SharePoint list item id.
Set(viID,pollingflow.Run(Last(atFile.Attachments).Name,vsBinaryData).id);
2. Processing Flow
The flow is quite simple:
- Create File
- Find Table
- Loop over Table
The key thing is that we have to create an item in a SharePoint list, this is going to be how we track the progress of the upload and where the app asks "are you done yet?".
Additionally we don't want the app waiting for the flow to finish so we need to reply after we have created the list item (so that we can pass back the id).
As I said the next steps are pretty simple (and can be replaced with whatever actions you need). The only thing to call out is we need to convert the file from bas64 back to binary when we crete the file in SharePoint.
base64ToBinary(triggerBody()['text_1'])
For the loop I have gone with a Do until (you can use a Apply to Each but I'm a sucker for a Do until).
The main benefit to a Do until is you can use the iterationIndexes() expression, but an incrementing variable works in an Apply to Each just the same.
We use the iterationIndexes() to update the SharePoint item with how many items we have completed.
The final action is to check to see if we have completed all items, if true that stops the Do until.
3. App Polling Updates
Finally to the cool bit, we are going to run a timer which every second looks up the SharePoint item and uses the 'Total' and 'Completed' fields to get a % complete.
The same button that triggers the flow will set a variable to start the timer, on the 'OnTimerEnd' we will refresh the list, store item in a variable and check to see if we have finished:
Refresh('polling-list');
Set(voProgress,LookUp('polling-list',ThisRecord.ID=viID));
If(voProgress.Completed=voProgress.Total,Set(vbStart,false);)
To be fancy I'm going to use the new progress bar to show the upload progress. The progress bar 'Value' parameter is:
If(IsError(voProgress.Completed/voProgress.Total)
,
0
,
voProgress.Completed/voProgress.Total
)*100
I wrap it in an IsError() to stop any 0/0 errors
And this is the final result:
And that's it, the app will hand over the long tasks to the flow and allow the user to continue in the app, all the while receiving updates on the flows progress.
A copy of the solution can be found here
Top comments (2)
This is amazing! A very smart approach. Thank you for sharing.
Would be a lovely little user offline updated capability