Unanswered

Pick random row from Excel (Understanding Initialize Var, and RAND)

(0) Share

Report

Posted on by mcc42

I'm trying to setup a very simply bot that will post a message to our Slack server once a day.

This bot should read a random row from an Excel file, and post the contents of this row to Slack.

I managed to find this tutorial online, which seems to be exactly what I need, but it's vague for someone with little to no experience with Power Automate, such as myself: https://blog.johanpersson.nu/?p=4086

Essentially, what I'm stuck on is initiating the RAND variable. This article simply list the formula as:

rand(0,int(length(body(‘ListRows’)?[‘value’])))

But this on it's own, doesn't work, so clearly I'm supposed to substitute some information here with dynamic content, but I'm not sure what. It seems that no matter what combination of information I put into the Value for my Initialize Variable, results in:

InvalidTemplate. Unable to process template language expressions in action 'Initialize_variable' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

The only Dynamic Content I have to pick from, are the following:

Body (List of Items)
Value (List of Items)
body/value - items
%My Table Header%

I don't understand what any of this dynamic content is, or how to correctly use this information to correctly get my RAND function, to start at 0 and run to the "Length of my table".

Hoping someone can give me the ELI5 breakdown here to understand what is going on here.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

tom_riha 10,185 Most Valuable Professional on at

Like (1)

Report
Hello @mcc42 ,

the expression should look as below, you don't need the int(...) expression as length(...) will always give you a number. The only part that could be different is the 'List_rows_present_in_a_table' as that's the name of the action with spaces replaced by underscores and if you renamed the action it might be different.

rand(0,length(outputs('List_rows_present_in_a_table')?['body/value']))

Was this reply helpful? Yes No
torkil 12 on at

Like (0)

Report

Isn't there a classic off-by-one-error in this solution though?

If you have a table with 7 rows. They will have index numbers 0 to 6: Row number 0, row number 1 etc. When you use the LENGTH() function you get the number 7, since there are 7 items in the list.

So RAND(0, LENGTH(list item count)) will return a random number between 0 and 7, while your list only has items in indexes 0 through 6. So every 8th time (on average) this will probably fail, since it will try to get item with list index 7, which does not exist.

Was this reply helpful? Yes No
torkil 12 on at

Like (0)

Report

@mcc42 The request you are making to fetch rows from Excel will receive a response object in return.

The response object will have a statuscode (telling you the result of the request, 200 for success, 404 for not found, etc). It will also contain a headers object, which contains various technical info, and most importantly: It will have a body object, which will contain the actual payload of the response, i.e. the data you requested.

That Body object will in turn, in our case, contain a property called "value", which is an array (or collection) of row objects, each representing one row from Excel.

So you should be counting the number of items inside "value" if you want to know how many rows was returned from Excel.

Was this reply helpful? Yes No