Writing Collections from PowerApps to SharePoint using JSON & Power Automate

I had done this quickly back in October when I first tried out the ParseJSON() function in PowerApps, but hadn't gotten around to documenting it up.  Then when I went checking to see what everyone else had written up I saw that Shane has done a video on this.  Oddly enough, we both approached this nearly identically.

However, I'm dropping this here for posterity and because I always like to read my own typing vs. listening to anybody else's voice.  ;)

If you have solved grabbing lots of records from "somewhere" (i.e. SharePoint) and returning it to your PowerApp as a bundle of JSON using Power Automate, the reverse is now what you need to solve for.  Fortunately, writing out a lot of updates is similarly easy, but also similarly obtuse per guessing exactly how to get it done.  Here's the Q&D on how to make it happen.

Turning JSON data into Collections in PowerApps

After years of futzing about w/ workarounds and hacks, the new ParseJSON() function in Power Apps has given us a glimpse of how this will work for us going forward.  The ability to push data out to other applications has been possible for awhile now, but our ability to consume JSON back into Power Apps easily has eluded most app builders.

While this function is still in the Experimental branch of PowerApps, there is no doubt that some form of this will go forward.  The benefits are massive and this changes everything about how we deploy large-scale applications.

Not to mention the possible security benefits

Stripping HTML tags from SharePoint data at runtime

If you find yourself in a situation where "somebody" imported a bunch of SharePoint data that included what SharePoint decided had some HTML tags in it, then you might wind up with a multiline text field that has whatever value you care about surrounded with some very unique "<div></div>" tags.  Everything might look fine when you're glancing at your original list, but when you query the field you see something like:

<div class="ExternalClassCA06509747B841D08AA4020D3A431C1E">Some Value</div>

And don't think that SharePoint will be nice and use the same tag within that <div> tag.  It will not.  So if you're going to do any manipulation/comparison on that within your PowerApp (like looking for matching values), you're going to need to get rid of that garbage.

While it is of course always better to try and fix the data, if you're in a situation where you don't control it or cannot do it easily (say when you're dealing w/ hundreds of thousands of rows), then it is time to manipulate this within your app.

Power Automate Service accounts / Let users interact with data they have no rights to

The beautiful part of the Power Platform is truly that it is actually DIFFICULT to decouple who is using the application from what that user has the rights to do within the various systems they connect to.  The big focus of this platform has been to make it easy to let people work w/ data, while also tracking that they are doing so.  However, it is stupid-simple (if not exactly obvious at first glance) to grant a PowerApp user the ability to connect to data they otherwise couldn't.

Importing Excel data at run-time (15,000 row import workaround)

If you have found yourself working w/ some larger Collections, Excel Tables, or other data sources, you'll find some limits that you start to hit.  For importing data into a static Collection during design-time, that limit is 15,000 rows.  Now you can of course chop up your list into multiple lists to get past this, but that can get frustrating if you're doing this lots and lots of times.

As well, some of the default methods that might "work fine" for smaller lists, appear to blow up w/ large datasets.  However, w/ a little bad programming, you can get around this.  Read on.

Note: Microsoft made a recent (November 2023) change that limited input lengths on Text input fields.  

Getting traction inside your organization for PowerApps

I'll definitely say that this is primarily a conversation that is not only about PowerApps but also is about what I've learned in (checks calendar)...thi- (checks calendar again)...thirty years of technology work/consulting.  The process of getting any organization to pick up a specific technology is almost always a slog.  Add in that being successful in the "low-code" space means definitely abandoning some "best practices" from traditional coding that will make people's heads explode.

The key thing to realize is that every single organization prioritizes one thing:  results.  If you just abandon everything else and focus on this, you'll be successful.  Now, how long you'll be successful...maybe don't abandon everything.  See some ideas below.

However, before you go, I do feel a need to state the following simply as my own moral duty due to what I'll be sharing below.  Consider this my own personal ethical EULA for you to agree to:

By clicking this link and reading the article attached, I agree to use what I learned here for the betterment of humanity, my employer, my family, and myself (in that order of priority).

Where's my data? Microsoft might have just broken your app

I got a panicked call this morning (Dec-22) from a developer out in the business who suddenly was having severe data loss.  It looked like all of his data was gone.  Worse, it appeared some records still had data, but as he went looking around at it, that data started disappearing!

Ultimately, it wasn't the data disappearing, it was his own app writing blank values over the data because Microsoft had f@#%3d him over by changing something relating to Records vs. Collections.

NOTE: I did just do a test w/ a locally created Collection AND with a SharePoint Collection and everything still works as usual.  So now we're down to testing if this happens with Dataverse (probably), or just Dataverse For Teams.

Working with Large SharePoint Online Lists

First and foremost.  Please don't do this if you can avoid it.  Just back away from the mouse and keyboard and rethink everything you're doing.  I personally hate myself that my own work has led to situations like this, but I recognize that sometimes these kinds of applications expand beyond your control.

So, for those of you who are trapped and have to come up w/ a solution, I've laid out some strategies for dealing with large Lists in SharePoint Online (let's say 5,001 - 500,000 items) and some of the issues you will encounter when using Power Apps to access them.

NOTE: As of 2021/10/5 my account has been throttled and my list import into PowerApps from Excel limited to 15k rows.  Working w/ MS to understand if they have a better answer than the non-answers we have today.  The key to avoid this is you can do everything I outline below, just don't open 3 VMs and start doing the same stuff under the same account in each.  :D

Version Control & Warning

I have kept hoping that Microsoft would deliver a standardized method for catching users running old versions of the application.  However, I am giving up on waiting on them to deliver on this.

While the platform generally updates automatically, some user's devices or browsers can cache the application and wind up stuck with a prior version when they first open it instead of immediately refreshing/reloading.

This is a fairly simple method to help you build out your application and catch if/when this happens so that users will be told to close/re-open/refresh.

NOTE: I really stopped doing a SharePoint list here and just do a hardcoded version in a Power Automate flow(s) specifically just to check versions.  It limits the # of failure points but also means a developer needs to be the one to update this.  YMMV.

PowerApps for Teams: Broken items in ComboBox

I am enjoying my journey through the half-baked world of PowerApps within Teams (which if you haven't realized yet is a completely different thing than just building a normal PowerApp and linking to it within Teams).  The bad layouts, then limited choices, the awkward redesign of the base UI.  It all makes me so very happy.  Or not.

Today Microsoft has shown me that they aren't beyond failing at simply displaying the values in ComboBox for a hard-coded Collection.