Unanswered

Copilot and PowerAutomate Excel get a row function

(0) Share

Report

Posted on by theMac

I've been able to let Copilot use a Power Automate flow where
1- Copilot passes to PA a string parameter (a product code)

2- the flow search for the parameter value in a column of a given excel file ("get a row" action)

3- when flow matches parameter and xlsx data, returns to Copilot a numeric value contained in another column (the product price).

4- Copilot shows a message like "price of product is xxx"

It works fine while parameter contains something present in xlsx file.

When parameter is not present in xlsx, the flow returns an error (I think it's because it try to return to copilot a blank value into the numeric output but I'm not sure), Copilot exits from current argument and switches to "On error" argument.

I'd like to avoid the switch -Copilot side- and show a message like "code not found"... any suggestion is welcome

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (1)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

This is pretty easy to do depending on how you are returning things to Copilot. The most common way is to create a string variable and build out the response in the variable as data is found in the row. If you add a condition to check the result of the get a row action you can have it set that variable if the row was found or set the return variable to the "Code not found" string if it isn't.

Was this reply helpful? Yes No
theMac on at

Like (0)

Report

I agree that it should be pretty easy but I'm still here...

"If you add a condition to check the result of the get a row action"
You can't check the result of Get a row action in any way, that's the problem.
So far I didn't found a way to intercept the failure of the action when no rows are found.

I read somewhere else that I don't have to use Get a Row action but Get a list of rows filtered by input parameter and iterate on the rows but it seems to me an "expensive" way to obtain a result

Was this reply helpful? Yes No
Verified answer

Pstork1 68,707 Most Valuable Professional on at

Like (1)

Report

To handle that case you need to add a parallel branch with a set variable action that is set to run if the Get Row action fails. Make sure to then have the respond action run from both parallel paths and set it to run if the main branch is skipped. That way if the row is not found it will set the message in the variable and return it even though the Get a row threw an error. That's standard error handling for Power Automate.

Was this reply helpful? Yes No
theMac on at

Like (0)

Report

Thank you, it seems it works!
What if I need an entire rows of data from the excel file instead of a single value?
Do have I to use an array variable to store data from excel when Get a row is successful?

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

If you need multiple rows you would use the Get Rows action that returns an array. Then check the length of that array to see if its empty. The run after check for an error won't be necessary because get rows works even if it doesn't find a match. But the values still need to be returned as a formatted string since you can't return an array to the bot.

Was this reply helpful? Yes No
theMac on at

Like (0)

Report

My bad, I mean if I need data from multiple columns of a single row of Excel file

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

The run after setting will still handle the condition where the row isn't found. The way you do the regular condition depends on how you want to handle it if some of the columns contain data and some don't. But its still the same flow.

Was this reply helpful? Yes No