Power Virtual agent > Power automate - how to pull Data from an excel sheet back to PVA

[JJ93]

I'm trying to pull data from an excel sheet (located on SharePoint) back to Power virtual agents but I'm a bit stumped.. Can anyone direct me to what the flow should look like.. there's only so much you can ask chatGPT and many rabbit holes it takes you down..  

If anyone has accomplished & has some screenshots that would be perfect!

[Pstork1]

Its fairly easy to pull back data formatted as a table using the Markdown language.  Use Markdown language to format Power Automate approvals - Power Automate | Microsoft Learn But there's no way to pull back an unformatted data table to use as data. Is that what you are trying to do?

[fchopo]

Hi @JJ93 

You could do something like this:

1) List rows of an excel file

2) Create an HTML Table from the excel table

3) Send the HTML back to PVA.

In PVA you can display this HTML table.

Hope that helps!

Ferran

[Srini007]

Hi @JJ93 

Check below post for your reference

https://powerusers.microsoft.com/t5/Building-PVA-chatbots-in/Read-from-Excel-file/td-p/1645271

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Regards,

Srini

[Pstork1]

@fchopo A quick correction. Unless something has changed recently, PVA bots don't have the ability to display an HTML table natively. That would require using either bot framework composer or some form of adaptive card. 

[JJ93]

Hi Ferran, I don't want it to return as table, basically its to tell them where they are able to access their emails in different countries, so the countries are in one column going day as below, so in the response on Power virtual agents I just want it to return;

Countries you can visit;

UK

France 

etc

[JJ93]

Hi,

I don't want to pull the actual table, I just want to pull the data from excel and put them in a power virtual agents reply as below;

Countries you can visit;

UK

France

Etc 

Here is the excel sheet

[fchopo]

Hi @JJ93 

OK! You're talking about showing those countries as options in a list in PVA, right?

Please, have a look at this: Power Virtual Agents: Advanced authoring canvas preview – Dynamics 365 Talk (d365goddess.com)

Hope it helps!

Ferran

PD: @Pstork1 You're right! We need to convert HTML to markdown in order to display that in PVA

[JJ93]

So below is the current flow on PVA, under 'Accessing Team/outlook abroad' I want it to pull the locations that are available from the Excel sheet & have it put that info in the reply on PVA 

So it would appear like below but with all the country locations from the excel sheet

[fchopo]

Please, read carefully the article I shared above that shows how to do that: https://d365goddess.com/PVA-advanced-authoring-canvas-preview/ 

Hope it helps!
Ferran

[JJ93]

Hi Ferran,

If it just wanted to pull the data from each row back what would be best to use? ie.

Return info;

Country names;

UK

France

USA 

etc

[JJ93]

Any idea's on why this is failing?

[Pstork1]

You can't send or return complex values like arrays to a flow from Power Automate. If you look at the trigger in the flow you can see the types of data that you can send.