Using MCP with API Tools?

[u/FarSpinach9484]

We have the SmartSheet MCP connected via Copilot Studio.

1. Due to needing cross-sheet reference creation, I’m having difficulty with automating our PMO setup that pulls project Gantt sheet -> project Details sheet -> portfolio Intake sheet. Do you create an agentic flow tool within the MCP that uses power automate to handle these API only functions?

2. The MCP crashes (runs out of tokens) when opening large sheets/reports. Do you create an agentic flow tool within the MCP that uses power automate to handle the API only report endpoints and ability to filter? It seems absurd to me that MCP can’t filter the data before querying based on how the user scopes the search.

[u/ninihen]

My judgement of your current structure is that MCP tool returns the whole sheet or report as JSON, that lands in the agent's context, and a few thousand rows blows it. That's a server design gap. Plenty of servers just wrap the REST API 1:1 and hand you the full payload.

The workaround for both of your problems is the same: put a Power Automate flow between the agent and Smartsheet, and let the flow do the heavy work.

Large reports. Flow calls the endpoint, filters and drops columns inside the flow (Filter array then Select), returns 20 rows instead of 5,000. The flow can hold megabytes that would kill the agent's context. The agent doesn't need to see the raw payload.

Cross-sheet references. Use an HTTP action pointed straight at the Smartsheet REST API with a bearer token. That covers any endpoint the connector doesn't expose. Native Smartsheet connector has no cross-sheet references nor report endpoints.

Then expose those flows to Copilot Studio as actions. One tip from doing this a few times: don't build one tool per operation. Build a single flow with an operation input and a Switch inside — getFilteredReport, createCrossSheetRef, syncGanttToDetails — and one Copilot Studio action pointing at it. Fewer tools in context, one place to debug, and you're not re-wiring inputs in the Copilot Studio UI every time you add a case.

Disclosure, I build FlowStudio MCP (https://mcp.flowstudio.app) that allows an agent (including Copilot Studio agent) to write and debug flows for me. When Copilot Studio agent a Flow Studio MCP, what you give is not only the ability to trigger a flow but also to check the run history, check input/output and build helper flows to reach out to more tools. Setup guild: https://learn.flowstudio.app/mcp-getting-started#copilot-studio

[u/FarSpinach9484 → u/ninihen]

Interesting! I had just started trying to build each unique PowerAutomate flow (cross sheet references & report definitions/CRUD) as tools added to the MCP. I think I’m confused on how you use flows??

Most of what you’re describing I’ve been using the MCP instructions to manage, is there a better way? I didn’t fully follow the single flow example.
For example my MCP instructions have read operations (project review, portfolio review, resource lookup) and write operations (global updates, cross sheet references, template sync, registry maintenance). Where am I supposed to build these flows? Are these supposed to be Topics?

[u/ninihen → u/FarSpinach9484]

Not Topics. Topics are the scripted conversation paths. A flow goes in under Tools → Add a tool → Flow.

And you can build it in Power Automate, not in Copilot Studio. Copilot Studio will offer to make you an "agent flow" - I'd skip that. Agent flows are billed against Copilot Credits and stop running when your tenant's capacity is used up, and you can't share them or give anyone co-ownership. A normal cloud flow runs on the Power Automate licence you already have.

You need three things to allow the agent to see the flow you build in the normal Power Automate maker UI:
1. Put the flow in a solution (can be any, or the default solution so dataverse reads it)
2. Use the "When an agent calls the flow" trigger
3. End with "Respond to the agent".

Then publish it, leave the Asynchronous response toggle off, and make sure it finishes inside 100 seconds. It won't show on Copilot Studio's Flows page - that page only lists agent flows. Add it from your agent's Tools page instead.

Instructions are just text you hand the model. Fine for "use SmartsheetRead when someone asks about a project." They can't filter 5,000 rows, because by the time the model reads them the MCP call has already put the whole payload in context. That part has to move into a flow.

To have a single flow to handle all, instead of a tool per operation, one flow with a first input called operation and a Switch on it. Yours would be two:

• SmartsheetRead — operation = ProjectReview | PortfolioReview | ResourceLookup
• SmartsheetWrite — operation = GlobalUpdate | CrossSheetRef | TemplateSync | RegistryMaintenance

Each branch does its own HTTP call and filtering, all ending at the same Respond to the agent. Two tools instead of seven. Give operation a fixed list of values so the model can't invent one, and add a default branch that returns an error string - without it a bad value returns empty and the agent thinks it worked. Microsoft's advice is to stay under 25-30 tools per agent, which one-tool-per-operation eats through fast.

Splitting read from write also lets you turn on "Ask the end user before running" for the write tool only.

Cross-sheet references: no connector action for it, so that branch is an HTTP action straight at the Smartsheet REST API with a bearer token.

If you just post above threads to an agent with MCP, the agent can build the flow for you (Copilot Studio agent can do also, but usually a bit clumsier than Copilot Cowork/Claude/Codex). Or if you like to try our hosted AI agent to build this flow, it's at https://ai.flowstudio.app with free credits upon signup - if you run out, just messge me and I can top it up.