Graphed LogoGraphed
Login

How to Connect Smartsheet to Power BI

Cody Schneider•

Connecting Smartsheet to Power BI is a fantastic way to transform your project data and operational sheets into powerful, interactive visualizations. Smartsheet is a great tool for managing work, but Power BI opens up a new level of business intelligence. This guide will walk you through the two primary methods for making this connection and show you how to start building insightful reports today.

Why Connect Smartsheet to Power BI?

While Smartsheet has its own dashboarding features, pairing it with Power BI offers several key advantages that can take your data analysis to the next level.

  • Advanced Interactivity: Move beyond the static widgets of Smartsheet dashboards. Power BI allows users to click, filter, and drill down into data across multiple charts simultaneously, making data exploration more dynamic and intuitive.

  • Combine Data Sources: Your project data in Smartsheet is powerful, but it’s even more powerful when combined with data from other business systems. Power BI can unify data from Smartsheet, your CRM (like Salesforce), financial software (like QuickBooks), and web analytics (like Google Analytics) into a single, comprehensive report.

  • Powerful Data Modeling: Power BI’s data modeling capabilities and DAX (Data Analysis Expressions) language allow you to create complex calculations and custom metrics that are difficult or impossible to build in Smartsheet alone. You can define relationships between different tables and build sophisticated business logic.

  • Automated Data Refreshes: Once you set up the connection, you can schedule automatic refreshes in the Power BI Service. This ensures your reports are always up-to-date with the latest data from Smartsheet, eliminating the need for manual CSV exports and report updates.

Before You Begin: What You'll Need

To ensure a smooth connection process, make sure you have the following ready to go:

  • A Smartsheet Account: You’ll need credentials for a Smartsheet account. At a minimum, you must have Viewer permissions on the sheets, reports, or dashboards you intend to connect to.

  • A Power BI Account: While you can build reports with a free Power BI account using Power BI Desktop, you’ll need a Power BI Pro or Premium license to publish, share, and automatically refresh your reports in the Power BI Service.

  • Power BI Desktop: This is the free application from Microsoft where you will build your reports. Make sure you have the latest version installed on your computer.

Method 1: Using the Official Smartsheet Connector in Power BI

For most users, the built-in Smartsheet connector in Power BI Desktop is the quickest and easiest way to get started.

Step 1: Open Power BI Desktop and Get Data

First, open Power BI Desktop on your computer. Once open, navigate to the Home ribbon and click "Get Data." This will open a menu with a list of available data connectors.

Step 2: Search for the Smartsheet Connector

In the Get Data window, you’ll see a long list of connectors organized by category. Use the search bar in the top-left corner, type “Smartsheet,” and the connector will appear. Select it and click "Connect."

Step 3: Sign In to Your Smartsheet Account

This is the authentication step. Power BI needs permission to access your Smartsheet data. Click "Sign In" and enter your Smartsheet credentials. Once authenticated, click "Connect" to proceed.

Step 4: Navigate and Select Your Smartsheet Data

After you've connected, a Navigator window will appear. Here you will see all your available Smartsheet sheets, reports, and workspaces. Check the boxes next to the data you wish to import and click "Load" or “Transform Data” if you need to prepare the data further in Power Query.

Method 2: Using the Smartsheet ODBC Driver for Advanced Users

The native connector is convenient, but for those who need more flexibility, using the ODBC (Open Database Connectivity) driver can provide more control. This method is useful if you need to write custom SQL statements, work with unsupported features, or adhere to company policies that don’t allow the native connector.

Steps for Using the Smartsheet ODBC Driver

Before starting in Power BI, you need to install the Smartsheet ODBC driver on your computer. Download it from the Smartsheet website and follow the installation instructions.

  1. Install the Smartsheet ODBC Driver: Follow the prompts to install the driver on your system.

  2. Configure a Data Source Name (DSN): After installation, set up a new DSN for Smartsheet ODBC driver in your computer’s ODBC Data Source Administrator tool.

  3. Use SQL Statements: Connect to your newly created DSN within Power BI Desktop, and use SQL to query your Smartsheet data.

Setting Up an Automatic Data Refresh

Creating your report in Power BI Desktop is just the first step. The real power comes from publishing it to the Power BI Service and setting up a schedule to automatically refresh the data from Smartsheet. This keeps your stakeholders looking at the most current information without any manual intervention.

  1. Publish to Power BI Service: In Power BI Desktop, go to the Home tab and click Publish. Choose a workspace to publish your report to.

  2. Navigate to Dataset Settings: Go to app.powerbi.com and find the workspace where you published your report. Find the dataset (it will have the same name as your file) and click the three dots (...), then select Settings.

  3. Configure Credentials: In the settings, expand the Data source credentials section. Click Edit credentials for your Smartsheet connection and re-authenticate using your Smartsheet account. This allows the Power BI cloud service to access your data securely.

  4. Schedule Refresh: Expand the Scheduled refresh section. Toggle it on and choose a refresh frequency (e.g., Daily, Weekly), a time zone, and the specific times you want the data to update. You can add up to eight daily refresh slots on a Pro license.

With that configured, Power BI will now automatically pull the latest data from Smartsheet on the schedule you defined, ensuring your reports are always current.

Troubleshooting Common Connection Issues

Sometimes you may run into a hiccup when connecting Smartsheet to Power BI. Here are a few common problems and how to solve them:

  • Authentication Errors: If you get an error message about credentials, the first step is to clear your existing permissions in Power BI. Go to File > Options and settings > Data source settings. Find the Smartsheet entry, select it, and click Clear Permissions. Then try connecting again, ensuring you are using the correct Smartsheet login.

  • Data Type Mismatches: Sometimes, a column of numbers or dates in Smartsheet might import into Power BI as text. This often happens if there are mixed data types (like text notes) in the same column. The best place to fix this is in Power Query by right-clicking the column header and selecting Change Type to the correct format.

  • Refresh Failures: If your automatic refresh isn’t working in the Power BI Service, it’s often due to authentication issues. Ensure you have re-authenticated your Smartsheet connection in the dataset settings.

Final Thoughts

Connecting Smartsheet to Power BI is a straightforward way to unlock a new level of analysis. Whether you choose the native connector for ease of use or the ODBC for advanced capabilities, these steps will help you get the most out of your data.

We built Graphed to make the process of data integration and analysis as simple as possible. Use it to manage your connections and schedule data updates with ease.