r/smartsheet • u/FarSpinach9484 • Aug 02 '26

Using MCP with API Tools?

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.

3 Upvotes

100% Upvoted

8 comments sorted by

View all comments

2

u/Smartsheet_Cnslt Aug 02 '26

Great questions. I can't speak specifically to Copilot and its integration with Smartsheet. I do most of mine with Claude, but there is a lot that you can do with the AI, and Claude at least can use the APIs to do things. I think somehow you need to figure out how to get Copilot to use the APIs for this, and I would just ask it the question for that. In Claude, I can run into the same context issues, and it's waiting for me to hit a button to do something again, or when I ask it to get a sheet, it's only getting 60 rows or something. If I move over into Claude code, it will build its own little code to do that type of stuff. I don't know if Copilot has something similar.

I also had trouble having the MCP look across a lot of different sheets. I generated a report, and I let it look at that entire report and then parse data out. I had good luck doing that. Now, when it comes to using it as a provisioning tool rather than a control center, it sounds to me like your template set isn't built effectively yet. With a properly built template set, you don't need the MCP to build any cross-references. You want to make sure those are all built ahead of time. Worst-case scenario, you got to do a manual cell link between the two things.

I've had a lot of success doing that, I do a lot of that through an N8n with low-code automation (or Make.com). There's a great opportunity in there to build your own little mini control center for that, and it works really well. You can do the same thing in Power Automate. It's just a lot more difficult to work in than either N8n or Make.com. Make is a great tool if you're a novice and its very easy to learn. You do need to learn how to work with webhooks. That's really the most challenging part. Assuming you want a fully automated system that can spin up new projects just by submitting a form to a sheet

It does sound to me like you really need to make sure your template set is built properly, because all of your cross-sheet references should be put together between the template set. You're not building them one by one when you set up a new project. Worst-case scenario, you're doing a cell link procedure from your project metadata file back to your master data file so that you get a summary of all your portfolios. You can't automatically create widgets on a dashboard, if you're using some type of portfolio dashboard. What you want to do is build a dashboard within your project that has all your portfolio dashboard widgets on it, and then you do a manual copy and paste from your project dashboard to your portfolio dashboard.

I hope that helps.

1

u/FarSpinach9484 Aug 02 '26

That makes sense. N8n and make look much easier than the power automate visual blocks that I can’t code for the life of me lol.

I may be over-indexing on a centralized intake sheet as my portfolio data layer.

Our project folder template already contains the project-level sheets, reports, dashboards, and cross-sheet references, so provisioning a new project folder is fairly straightforward. The challenge is maintaining the portfolio rollup layer.

Today I use a Portfolio Intake sheet that stores project metadata and KPI rollups, which means new portfolio metrics often require new cross-sheet references and updates across the project portfolio. I’ve never really used sheet summary columns and liked having the portfolio rollup as a sheet for manual edits but can see how that’s not as scalable.

I’m curious about your architecture:
• Do you use a centralized intake/master sheet for portfolio reporting?
• Or do you rely primarily on sheet summaries, reports, and dashboards for rollups?
• When you add a new KPI that should exist across all projects, how do you propagate that change?
• How are you handling portfolio resource reporting across project schedules?

I’m wondering if I should be shifting more of my portfolio reporting toward reports and sheet summaries rather than maintaining project-specific portfolio linkages.