PowerApps, Excel and the infamous dynamicProperties

Published in

Cook php

5 min readDec 4, 2020

Please take a seat and enjoy the show.

What is PowerApps? PowerApps is a service of Office365 (well, not really (1) ) in charge to create UI that could work on the web and on mobile devices. It’s neat but limited. If you are seasoned then InfoPath on steroids.

(1) Why PowerApps is not part of Office 365? PowerApps is business speaking, part of Power Platform but it is also technically part of Microsoft Dynamics. PowerApps most of the time doesn’t need Dynamics but it reeks of CRM. However, it fits well with Office365. Also, its security depends on Azure AD. Microsoft really loves that mess. I don’t but it still works (most of the time).

What is Power Automate? Power Automate is a service of Microsoft (Office 365, Azure, Dynamics). Why am I mentioning Power Automate? Because both services talk to each other a lot.

Mission: To list information from an excel sheet.

1- Create a worksheet and upload into SharePoint or OneDrive

2- It is the technical information about the file

(it is example information, I removed everything confidential)

site: https :// somesite.sharepoint.com/site/mysite
library: mylibrary
file: 5000 sales records.xlsx
table: Tabla1

3- Create a PowerApps application (Canvas) and adds the data source.

In this case, I am using SharePoint, so the data source must be of the kind ExcelOnline(Business) (instead of ExcelOnline(OneDrive))

4- Add a new gallery (to display the data and associates the data source)

And it failed (see the image with the red icons)

Why?

Excel Online is a web service so it works differently from other services (but technically, other services are also a web service).

The connections of Excel Online are a set of functions so we should use those functions.

In this case, it has the functions GetItems() (see image below)

The method GetItems has the arguments

drive = ??
file = ??
table = ??
source = ??
(and maybe other that I can’t see).

Does it make sense that drive is the url, file is the file, table is the name of the table and source 🤷‍♂️? Yes, but it is not what Microsoft is using. All those fields require GUID (unique identifiers) and they are really hard to find. We should look at the GUID of the site of SharePoint, then a GUID to the file, and a drive for the table, and finally, a GUID for the source. Is there an easy way? Well, yes but is tricky.

5- Power Automate

We must create a power automate workflow only to obtain the arguments. So, let’s create some new power automate flow (it doesn’t matter the kind of flow because we don’t want to run it)

6- In Power Automate add an Excel Online (Business) step, in this case, read a row from a table

This image is in Spanish but it still works in English.

So we must set all the information, the site, the library, the file, the table, and the column and value (we don’t need those fields)

6.1 Inspect code

Click on the 3 points in the excel step and click on the option Peek Code

It will show the next code:

},    "parameters": {
      "source": "groups/23f2d3ab-707c-4913-a032-3c8b98****",  
      "drive": "b!UF4j1I3rakejrneWQUJTw0O4ypTcN3VGk***-TRKTy***4b",
      "file": "01KOADULX***NL2DONHU5PG4",
      "table": "{7F5BFBDF-DA33-4506-889C-7****C844}",
      "idColumn": "Item Type",
      "id": "2222"
    },

Haha, we have the missing arguments.

7.0 Back to PowerApps

Let’s add a button and in the button, add the next code in the action onSelect

Set(rows,
'ExcelOnline(Business)'.GetItems("b!UF4j1I3rak****"
,"01KOADUL****"
,"{7F5BFBD******}"
,"groups/23f2d3ab-****").value)

Which arguments? It is the data obtained in the Power Automate (drive,file,table, and source). We are reading the excel online and storing the result in a variable row. Why? It is because we need to debug the operation.

And if we run the code, it will return the next values (in File -> variables we could see the variables)

WTF is dynamicProperties? I tried to find information and Google failed me. There is little information about it. Apparently, it is a dynamic result or some sort of un-processed information. If you look at the PROCESS MONITOR, you can see that the information is read correctly from Excel Online but it is not displayed here but these ugly dynamicProperties.

What we could do?

8 — Tricky, enable this advanced feature

In PowerApps -> File -> Settings -> Advanced Settings -> Experimental 🙄 -> Dynamic Schema, enable it Also Formula Pre-fetching

Save the project, and reload the page.

9 Capture Schema

If we look at the formula bar, we will see a new button called “Capture Schema”. This button will load the information (edition time), so the system could read the schema (instead of the dynamicProperties)