DEV Community

loading...
Cover image for PowerApps, Excel and the infamous dynamicProperties

PowerApps, Excel and the infamous dynamicProperties

Jorge Castro
You are free to believe in whatever you want to, me too. So, stop preaching your religion, politics, or belief. Do you have facts? Then I will listen. Do you have a personal belief? Sorry but no.
Originally published at southprojects.com ・4 min read

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: to list information from 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"
    },
Enter fullscreen mode Exit fullscreen mode

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)

Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

Forem Open with the Forem app