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).