PowerApps, Excel and the infamous dynamicProperties

PowerApps, Excel and the infamous dynamicProperties

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 lists information for an excel sheet.

1- Create a worksheet and upload into SharePoint or OneDrive

powerapps-1

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

powerapps-2

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

powerapps-3

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

powerapps-4

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)

Alt Text

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)

powerapps-5

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)

powerapps-6

6.1 Inspect code

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

powerapps-7

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

```c# 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.

Alt Text

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

powerapps-8

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.

powerapps-9

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)

powerapps-10

10 And finally, we could see the fields of the variable

So we could see the columns (File->variables->rows) instead of the dynamicProperties

powerapps-11

About Jorge Castro

Currently: Entrepreneur and Private Consultant
Civil Engineer in Informatics - USACH Chile.
Master in Business Administration (MBA) CEPADE Spain
Microsoft Certified Professional
Oracle Certified Associate
ScrumMaster Certified
Former developer
Former Project Manager

Related Posts