Connecting Smartsheet to Power BI - What Works and What to Watch

Smartsheet has quietly become the project tracking tool of choice for a lot of Australian organisations, especially in construction, engineering, professional services and state government. It's easy for non-technical teams to pick up, it looks like a spreadsheet, and it doesn't require a project management office to run. The problem comes six months later when leadership wants a single view across 80 project sheets and someone suggests Power BI.

Connecting the two is easy. Getting a report that people trust is harder. This post covers the Smartsheet integration Microsoft documents in the Power BI Smartsheet connection article, plus what we've learned from building Smartsheet reporting for clients.

Two ways in

There are two main routes to getting Smartsheet data into Power BI.

The Smartsheet template app in the Power BI service. You install it from AppSource, sign in with your Smartsheet account using OAuth, and it creates a workspace with a pre-built dashboard, report and semantic model. It gives you an overview of your Smartsheet account: sheets, reports, workspaces, how many items are in each, recent activity, and who owns what. It refreshes daily by default.

The Smartsheet connector in Power BI Desktop. Get Data, search for Smartsheet, sign in, and you get a navigator showing your workspaces, folders, sheets and reports. You pick what you want, shape it in Power Query, and build your own model.

These solve different problems, and I see people confuse them all the time.

The template app is about Smartsheet usage, not your project data

This is the most common misunderstanding. People install the template app expecting a project portfolio dashboard and get a report about their Smartsheet account. It tells you how many sheets exist, which ones are most active, which have been modified recently. That's useful for a Smartsheet admin trying to understand adoption or clean up abandoned sheets. It's not what the head of delivery wants when they ask for "a dashboard of all our projects".

The template app is also built from a fixed model. You can customise the report, but you're working within the shape Microsoft and Smartsheet designed. For anything specific to your business, you'll end up in Desktop.

My honest take: install the template app if you're curious about Smartsheet usage across your organisation. Don't expect it to be your project reporting solution. For that, go straight to Desktop.

Using the connector in Power BI Desktop

The Desktop connector is where the real work happens. A few things to know.

Sheets and reports both appear

You can connect to individual sheets or to Smartsheet reports. Smartsheet reports are useful because they can already roll up rows from many sheets with a consistent set of columns. If your project managers all use the same template sheet, a Smartsheet report across all of them can give you a single table to pull into Power BI instead of 80 separate queries.

This is the approach I recommend in most cases. Let Smartsheet do the cross-sheet roll-up, and have Power BI connect to one or a handful of reports. It's simpler to maintain and much less fragile.

The data comes in messy

Smartsheet is designed for humans typing into cells, and the data shape reflects that. Expect:

• Column types that don't come through cleanly. A "date" column where someone typed "TBC" or "early March" will cause type conversion errors.
• Hierarchy (parent and child rows, which Smartsheet uses heavily for project plans) comes through as flat rows. You lose the indentation unless you rebuild it using the parent and row identifiers.
• Contact columns that come through as names or emails, with inconsistencies depending on how people entered them.
• Dropdown columns where someone added a new option on one sheet but not the others.
• Symbol columns (the RAG traffic lights) that come through as text values like "Red", "Yellow", "Green", which is fine, but sometimes also as "Amber" on a sheet where someone used a different symbol set.

Plan for a decent amount of Power Query cleaning. Replace errors, standardise status values, handle nulls. Build a mapping table for status values rather than hard-coding replacements across ten steps.

Template discipline matters more than Power BI skill

The biggest predictor of success for Smartsheet reporting isn't anything in Power BI. It's whether the organisation uses a consistent sheet template. If every project manager has added, renamed or deleted columns on their copy of the template, combining those sheets is painful. Column names that differ by one character break appends.

On one engagement with a construction client, we spent more time agreeing on a locked-down project template with the PMO than we did building the Power BI report. Once the template was locked (Smartsheet lets admins restrict column changes) the reporting became straightforward. Before that, every week someone renamed "Forecast Completion" to "Forecast Complete Date" and the refresh broke.

If you're starting a Smartsheet reporting project, fix the template first.

Refresh and authentication

Once you publish to the Power BI service, you'll need to set up credentials for scheduled refresh. Smartsheet uses OAuth, and the connection is tied to the Smartsheet account that signed in.

This creates a classic problem. If the person who set up the refresh leaves the organisation, or their Smartsheet licence changes, or they lose access to a sheet, the refresh breaks. We strongly recommend using a dedicated service account in Smartsheet for Power BI refresh, with access to exactly the sheets and reports needed. Document it, and make sure more than one person knows how to update the credentials.

Also watch for Smartsheet API rate limits. If you have a report pulling dozens of individual sheets and refreshing several times a day, you can hit limits. Another reason to use Smartsheet reports to consolidate data rather than querying every sheet separately.

Smartsheet is a cloud service, so you don't need an on-premises gateway, which is one less thing to manage.

Performance and scale

For a few dozen sheets with a few thousand rows each, the connector is fine. Once you get into hundreds of sheets or tens of thousands of rows, refreshes slow down and become more likely to fail. The connector pulls data through the Smartsheet API, which isn't built for bulk extraction.

At that scale, I'd look at a different approach. Options include:

• Using Smartsheet's own data export or data shuttle features to push data somewhere more suitable for analytics
• Building a small extraction pipeline with Azure Data Factory or a Fabric pipeline that calls the Smartsheet API, lands data in a lakehouse or SQL database, and lets Power BI connect to that instead
• Keeping historical snapshots, which the connector doesn't do. If you want to see how a project's forecast date changed over time, you need to store snapshots yourself. The connector only ever shows you the current state.

That last point is worth stressing. Leadership almost always ends up asking "how has this changed since last month?" and Smartsheet doesn't keep that history in a form Power BI can easily get at. If trend reporting is on the cards, design for snapshots from day one. A daily pipeline writing the current state to a table with a date stamp is not much work and saves a painful conversation later.

Security considerations

The data Power BI can see is whatever the connecting account can see. If you use a service account with broad access and publish a report widely, people may see project data they don't have access to in Smartsheet. Think about row-level security in the Power BI model if different groups should see different projects, and be deliberate about who the report is shared with.

When Smartsheet plus Power BI is a good fit

It works well when:

• Teams already live in Smartsheet and aren't going to move
• Sheets follow a consistent, locked template
• You need cross-project roll-ups and visualisation that Smartsheet dashboards can't do
• Data volumes are moderate

It's harder going when sheets are inconsistent, volumes are large, or you need historical trend reporting. In those cases you're better off with a small data pipeline in between.

Where AI comes into it

We're increasingly seeing clients want to go a step further: ask questions about project status in plain English, get automated weekly summaries of slipping projects, or flag risk based on patterns across sheets. Once the Smartsheet data is clean and in a proper model, Copilot in Power BI can answer questions over it, and you can build agents that read the same data to generate status reports. The foundation is the same though. Clean, consistent data in a well-designed model.

If you're trying to get project reporting out of Smartsheet and into something leadership can use, our Power BI consultants can help with the connector, model design and template clean-up. And if you're thinking about automating the reporting side, take a look at our work on AI for business operations or have a chat with us about Microsoft Data Factory pipelines for larger volumes.