Answered

Struggling to Access Excel Data in Copilot Studio

Like (0) Share

Report

Posted on 19 Feb 2025 16:47:27 by JT-29071526-0

I am struggling and failing to do something that seems like it should be very simple. I want to create a custom agent in Copilot Studio that can read data in an Excel file and customize its behavior based on that data. I would be very grateful for any guidance that can help me reach that end state.

I set up dataverse tables for this purpose, but realized I can't actually use Dataverse with Copilot Studio unless I pay for premium connectors. Now I am trying to use an Excel 365 Online Business Connector. When I create this connector action, I am prompted to fill in values for the location/library/file/table. While the UI looks really similar to a "List rows present in a table" action one might create in Power Automate. In Power Automate, I can click on a field and it will give me a list of options to choose from. When I create this connector action node within a topic, that's not the case, which seems very strange. If I click a field, nothing happens at all. If I click the arrow to expand, I can pull from a variable, but I don't see a way to navigate my Onedrive to select the location of the table. Is this expected behavior? I did refresh the connection prior to testing.

Working on the assumption that I just need to be able to provide the appropriate string values, I created a similar Connector action in Power Automate and copied and pasted the values used to target my table. This is what my nodes look like:

Set Variable Value
1. Set Variable : Global.User (string)
2. User.PrincipalName (string from System)
Message
1. The chosen User ID is Global.User
Connector Action - List Rows Present in a Table - Excel Online (Business)
1. Inputs
  1. Location = OneDrive for Business
  2. OneDrive
  3. /Copilot Demo Proficiency Data.xlsx
  4. Table1
2. Advanced inputs
  1. Filter Query = Student ID eq 'Global.UserID'.
  2. null for other advanced inputs
3. Outputs
  1. Global.StudentUnitProficiency (table)
Message
1. Successfully fetched table
Set Variable Value
1. Set Variable: Global.StudentName
2. to Value: Global.StudentUnitProficiency[0]['Student Name']
Nodes 6 and beyond are similar to node 5. Goal is to create a variable for each column of the selected student's row.

When I try to test my agent, nodes 1 and 2 work as expected. However, instead of making it to my test statement in node 4, I'm getting caught on node 3 and receiving this message: Error Message: The connector 'Excel Online (Business)' returned an HTTP error with code 400. Error Code: ConnectorRequestFailure Conversation Id: 125e8bf6-368b-4212-9ef8-992df2aa239b Time (UTC): 2025-02-19T15:51:31.278Z

It looks like this error can occur within Copilot Studio if it's attempting to move large amounts of data. However, the Excel file is only 12kb. It also looks like it could be a generic "something went wrong with this connection" error.

I am grateful for any suggestions you can provide, or for any information regarding how I can find more detailed information about where exactly the error was encountered. I'd also LOVE IT if there are any resources that have examples of existing custom agents where it's possible to view all of the existing nodes.

Categories:

General

All responses (5)

Answers (1)

JT-29071526-0 85 on 24 Feb 2025 at 19:59:45

Like (0)

Report

Struggling to Access Excel Data in Copilot Studio

Thank you for your answers! I am now trying to skip the built-in connectors and instead connect to a Power Automate flow. At the moment, I'm trying to just pull in table of values that my agent can use as a reference.

I went to "add a new action" > New Power Automate Flow.I then am directed to Power Automate, where I can choose the options to locate my specific table within a "get tables" Excel for Business action. I can now see the flow exists within my current Copilot Studio topic, and it seems to run when I test the agent?

However, I'm not really sure if it's running because there is no output. I would assume there would be some way to specify that I want to store the table I just got as a table variable within my agent that I can use moving forward, but I can't find any way to do that. The action node that calls the flow offers no customization options (other than naming the action and selecting a latency option). There is a pre-populated "Respond to Copilot" step. Within that step, I can add an output, but only Text, Yes/No, File, Email, Number and Date. My output is none of those things (although it could be converted to a file or a text value in theory). Even if I pick one of these things, I need to provide a value to specify the output, but the "get tables" node of my Power Automate flow doesn't specify how I can reference the returned value.
CT-26090144-0 4 on 21 Feb 2025 at 01:29:05

Like (0)

Report

Struggling to Access Excel Data in Copilot Studio

1. To use premium connectors, you need to have CP Tenant License $200/month and assign user license

2. I have tried different ways to use tabular data:

- Add Gen Action: SharePoint / Excel Connector: there is size limitation in CP response, I think retrieving 256 items at most. If within this no. of rows, I think it is ok to add action and dynamically provide input.

- Add Action in topic: SharePoint / Excel Connector: no human response

- Add knowledge: Dataverse: totally not work

- Add Ai Prompt grounding Dataverse: 60-70% work, not a stable one, not really suggest

3. If you have many items, I will suggest Power Automate get items + Ai Prompt, which is the classic way
Verified answer

AllanMouraDev 133 on 20 Feb 2025 at 21:13:36

Like (1)

Report

Struggling to Access Excel Data in Copilot Studio

To be honest I don't like the connectors on copilot a lot, they tend to not work as expected. I would suggest you to send anything you need to do to Power Automate and after your flow is working just get the output you want.
JT-29071526-0 85 on 20 Feb 2025 at 20:23:21

Like (0)

Report

Struggling to Access Excel Data in Copilot Studio

@AllanMouraDev thank you very much for responding!

I hadn't tried Lists yet. I am trying that now though. When I create a connector action to get a Sharepoint item, I'm able to click the input boxes for Site Address and List Name and navigate to my list. Hurray!

But now I'm stuck again because I need to provide an ID for my item, which is going to vary. I see that I can use an expression to return a dynamic value. I tried this formula:

LookUp('Copilot Demo Proficiency Data', 'User ID' = Global.User).ID In theory, I think this should return the ID value for the row where the User ID matches my Global.User variable. However, I get an error message saying the name of the list isn't valid. But that's the exact name that autopopulated as an option under List Name.

Here's the full error message:

Error Message: The expression on the 'ActionInputBinding' node at 'cr5be_apModernWorldHistoryTutor.topic.ConversationStart/main/main/invokeConnectorAction_UIKaGk' failed with the following errors: 'Name isn't valid. 'Copilot Demo Proficiency Data' isn't recognized.' ('Copilot Demo Proficiency Data'),

'The function 'LookUp' has some invalid arguments.' (LookUp),

'Name isn't valid. 'User ID' isn't recognized.' ('User ID'),

'Incompatible types for comparison. These types can't be compared: Error, Text.' (=),

'The '.' operator cannot be used on Unknown values.' (.ID). Error Code: ContentValidationError Conversation Id: 31ac4f65-ec49-40cf-aeb2-429569a14b03 Time (UTC): 2025-02-20T20:09:19.581Z
AllanMouraDev 133 on 19 Feb 2025 at 19:18:38

Like (1)

Report

Struggling to Access Excel Data in Copilot Studio

Did you try using sharepoint lists? You mentioned that you were trying to use Dataverse but couldn't use because of license limitation. Try creating the same table you would create on dataverse as a list in sharepoint and see if it works.

Hope it helps!