populate desktop ui flow variable dynamically

[Shane_G]

Is there a way to populate a (PAD) Power Automate Desktop UI Flow variable dynamically, i.e. I was wanting to maintain an Excel Spreadsheet that the HR group could update, then the workflow could grab the emails from that field and set the variable dynamically, that variable is later used in the emailing of a payroll report later.

The above example is just that, an example, I could come up with 10 other scenarios in less than a minute where dynamically pulling in external info ad-hoc style at runtime or dynamically populating a variable at runtime would be useful, I know the above could be done as part of the flow, but the point of the question is it possible to dynamically populate a variable at run time for future use in that PAD....

Any insight into doing this or creative ideas you have done is always welcome, or maybe this is something for future features that MS could add for us, not sure, but thanks for your feedback and stay safe!

Best Regards.
SG.

[PRatcliff · Accepted solution]

Hi @Shane_G 

Not sure I know what you mean by populating the variable dynamically.  I'm interpreting it to be you want the flow to go check the excel file (in the example you mentioned) and get the contents of that file (maybe just one column with email addresses?) and store that list of values as a variable.  If the excel file is different the next time you run your flow, it will save the new values instead.  <If your question is different, then ignore this and someone else may have a more helpful response.>

If you are indeed trying to pull in data from an outside source as it exists now instead of defining your variable within the flow manually, then yes, PAD can definitely do that.  Use the Launch Excel to open the file and create an excel instance for PAD to reference, then Read From Excel Workbook to tell PAD to copy data from a specific field or range of fields, and store the results as a variable.  The resulting variable will have a single value, a list of values, or a table of values, of the data that it found in the excel file when the flow was ran. 

For this explanation, let's assume you don't rename the variable so it gets saved as %ExcelData%. You can then call any one piece of that data back by using the variable name and [#] notation before the second % sign.  You may already be familiar with this, but it's important to note a few things:  First, PAD's indexing starts at 0, NOT 1 (the first row is row 0, second row is row 1, etc).  Second, if you do two [#] references, the first one is the row reference, and the second is the column.  Third, columns are indexed as numbers, not letters, so you may have to get creative if you're trying to automate commands within excel.  

What does that mean?  Well, if you grab data from one row or one column of an excel file, you're making a list.  For example:

Blue

Black

Green

Red

If you then use %ExcelData[2]% in a PAD command, it returns the third value (remember, it starts at 0, not 1) in that list, so it will return "Green".

If you instead grab more than one row AND more than one column, you're making a table within the variable.

Luci	Desi
Fred	Wilma
Bart	Lisa
Ross	Rachel

If you then use %ExcelData[3][1]% in a PAD command, it returns the fourth row, second column item, which would give you "Rachel". 

This is how the variables work in PAD, and isn't unique to data read from excel or any other data source.  This generally works with any Read or Get command, where it returns more than one item (particularly useful with excel data, or Get Files in Folder).  You can call a specific value if that's what you want, or for more complex flows you can make a loop, to repeat a command with each value that you saved in the variable.  

Hope this helps, let us know how it goes.

PRatcliff

[Shane_G]

This is exactly what I want, we are building a list of user emails that may change base on employees in the role (As well as coming and going from the company) and I want to maintain that list in the external data source rather than the flow, this way I (Or someone) can maintain that list outside of PAD and the PAD flow can still run regardless of the value in the data source.

Thanks for you reply very helpful!
SG.