Introduction

This is my first blog post of 2020 and also my first pure Power Apps article (I seem to end up writing a lot about Power Automate and Logic Apps).

I recently set myself a little challenge: How do I automatically build a single collection from an API that limits the results per page?

There are probably a few ways you could do this; you could call a Flow to build the collection for you and return the result to the app or a SP list, but I wanted to see if I could do it within Power Apps only.

Instead of googling, I just got started with an idea so for all I know this topic has been done to death and I’m wasting my time! Anyway…

Background

My data source is Accelo, which is an excellent SaaS service ops platform we use at my work for, well basically everything like logging service tickets and managing projects etc.

The Accelo API is pretty powerful really. I’ll give credit to them for putting the effort in, but I only want to use the /companies endpoint to get a list of companies.

To access the data in Power Apps I needed to create a custom connector that uses OAuth 2.0 authentication. I don’t want to go into how to do that in this blog post, it’s not relevant for the topic and it would vary from API to API and this article isn’t Accelo specific.

The technique I’ll describe below can be used for any sort of looping operation, not just cycling through pages of HTTP responses, so I’ll skip through to having access to the data in Power Apps already.

The Accelo API returns 10 results per page by default, but you can increase that to 50 results with a query parameter. In my example I’ve left it on the default by omitting the query.

Another query you can use is _page=0 which returns a specific page. The default is 0 and if you enter a number that goes beyond the number of pages of results you get an empty array back.

I’ve played around with a few APIs and they often have different ways of handling pagination, Odata APIs such as the Microsoft Graph give you a URL you can hit for the next page if it exists via the odata.nextLink property, Cloud Elements give you a next page token you can use as a query parameter in the next request, to cite a couple of examples. Some APIs might give you the number of pages in the response body, or the number of records in the current page which might help you with stopping the loop. Accelo doesn’t do any of that, but you can count the number of records returned in the array and requesting a page that’s out of range returns an empty array and a 200 response, so you can easily tell when you’ve reached the last page.

The short of it is, this is a specific example using Accelo, but you will need to apply your own logic to suit your circumstances.

The How

So, in Power Apps I have a gallery, which looks like this, with col_companies as the Items property:

I’ve excluded the name of the companies in my blog because it’s our list of clients. As you can see there are 10 records in this gallery. This is page 0 of the results.

For the purpose of an example I put a button on the screen that grabs a page and adds it to the collection:

As you can see from the formula, the button grabs a page of results then increments a variable con_newPage by 1 so the next time it’s pressed it grabs the next page. The screen’s OnVisible property sets this variable to 0.

You could sit there clicking this button until the collection stops growing and you’d have the result; a collection of all the companies in your Accelo tenancy. However, this would be tedious so I want to automate it.

You might notice a couple of labels top right of the screen shot above, the top one is the value of con_newPage and the bottom is CountRows(Accelo.companies({_page:con_newPage}).response)

The Automation

So how do we automate building this collection? The answer is using timers.

We need to start a timer that on ending, grabs page 0, then increments con_newPage by 1 then starts again if the next page has more than 0 records: CountRows(Accelo.companies({_page:con_newPage}).response) > 0.

I had real trouble with getting a timer to reset itself and start itself, so I actually ended up using two timers. Timer 1 does the work and Timer 2 is used for resetting and restarting Timer 1. They both have a duration of 1 ms, both have AutoStart set to false and Repeat set to false, but you could have Timer 1’s AutoStart set to true if you wanted.

Starting with Timer 1. This is triggered using a variable:

con_startTimer

This can be set true with the page OnVisible, or a button or whatever. It starts the first cycle of Timer 1.

I’ll come back to Timer1.OnTimerStart later, and go straight to OnTimerEnd:

Collect(
    col_companies,
    Accelo.companies({_page: con_newPage}).response
);
UpdateContext({con_newPage: con_newPage + 1});
If(
    CountRows(Accelo.companies({_page: con_newPage}).response) > 0,
    UpdateContext({con_startOtherTimer: true}));
UpdateContext({con_startTimer: false});
UpdateContext({con_resetOtherTimer: false})

You’ll recognise the first bit of this as being the same as the “Add More” button earlier.

The next bit is an If() statement that starts Timer 2 if the number of records in the next page is greater than 0.

Lastly I’m setting the variable that started this timer in the first place to false and the one that resets Timer 2 to false (more on that soon).

Now let’s assume there are more than 0 records on the next page so we’ve started Timer 2 with UpdateContext({con_startOtherTimer: true}).

Timer2.OnTimerStart

UpdateContext({con_resetTimer:true})

This resets Timer1 (it’s Reset property is con_resetTimer)

Timer2.OnTimerEnd

UpdateContext({con_startTimer: true});
UpdateContext({con_resetOtherTimer: false});
UpdateContext({con_startOtherTimer: false})

We start Timer 1 again and set the variables that start and reset this timer to false.

Of course Timer1.OnTimerStart is used to reset Timer2:

UpdateContext({con_resetOtherTimer:true})

So what we have here is a loop that follows this logic:

1. Start Timer 1.
2. On Timer 1 Start, reset Timer 2
3. On Timer 1 End, Append the current page to a collection and increment page count by 1
4. If the number of records on the next page is more than 0, start Timer 2, else do nothing (no more timers run)
5. Timer 2 Starts, resets Timer 1
6. Timer 2 Ends, starts Timer 1
7. Back to 1.

Here’s the result. Those two labels now showing we’re on page 91, there are 0 records on the next page (so the timer has stopped) and we’ve collected 908 records.

Conclusion

That’s about it. Power Apps doesn’t really support looping natively but you can make it happen using a couple of timers and a variable. The world is your oyster in terms of what you do with the Timer1.OnTimerEnd event. I’m getting records from an API but you could literally do anything as long as you have a way of checking whether you should be starting the loop again on each run.

Because the loop isn’t restarted until the Collect() function is finished, the timer duration isn’t needed as a way of waiting for the operation to complete, so I set them to 1 millisecond. Basically the timer isn’t timing anything, I’m just exploiting the OnTimerStart and OnTimerEnd events to create this looping condition.

You can set both the timers Visible property to false and you could also add some logic to display a spinning icon or grey out your gallery etc, e.g Visible property is CountRows(Accelo.companies({_page:con_newPage}).response) > 0