How to connect Power BI to SmartSheet

ODBC PowerPack API Driver
1

Introduction

This guide will walk you through connecting Power BI to Smartsheet using the ZappySys ODBC API Driver for seamless data retrieval. The ZappySys SSIS Smartsheet Connector offers a powerful solution for integrating your Smartsheet data into Power BI, enabling easier management and deeper analysis of your campaigns and subscribers.

Prerequisites

  • ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.
  • Smartsheet Account: Ensure you can access your account and its API.

Steps

Create a Token to access Smartsheet REST API

The first step to accessing the Smartsheet API in SSIS is creating an API Token. We will use this API token later to read data from the Smartsheet Sheet.

  1. Login to your Smartsheet account

  2. Click on Account > Apps & Integrations > Click API Access > Click Generate Token
    Create Smartsheet API Token for REST API Access (Step 1)

  3. Once the Token is generated, copy that for later use (Make sure you treat this token as a password and do not share it with unauthorized users).
    Generate new Smartsheet token (Step 2)

Create a New Driver

  1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
    Open ODBC Data Source

  2. Go to the User DSN or System DSN tab to access the data source for yourself or other users. Add a new System DSN for SQL Server Integration by clicking the “Add” button.
    ZappySys ODBC Driver - Open UI

  3. Select ZappySys API Driverfrom the driver list, then choose Smartsheet in the connector list and press Continue.

    Select Smartsheet Connector
    Select Smartsheet Connector625×506 50.3 KB

Smartsheet Connector Configuration

  1. Fill in the connector and enter the token you generated before

    Smartsheet Connector
    Smartsheet Connector622×545 70.8 KB

  2. Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.

    Preview Data
    Preview Data668×681 56.1 KB

Read Smartsheet Data in Power BI Using ODBC

  1. Open Power BI Desktop and click Get Data to retrieve data from an ODBC source.

    image
    image1094×577 34.4 KB

  2. In the Get Data window, search for “ODBC” and select it to connect to the ODBC data source.
    image

  3. Select the ODBC Data Source Name (DSN) you created earlier, such as Smartsheet xDSN, and click OK.

    image
    image695×214 12.2 KB

  4. If prompted, authenticate using Windows Authentication or other credentials as required, then click Connect.

    image
    image695×364 18.1 KB

  5. Choose the Smartsheet table or view you want to load, then click Load to import the data into Power BI.

    image
    image871×521 35.7 KB

  6. Use the imported Smartsheet data to create Power BI reports, visualizing your marketing metrics.

    image
    image867×557 49.7 KB

Import Smartsheet Data Using SQL Queries

If you prefer to import Smartsheet data via SQL queries instead of selecting tables, you can do so by using Advanced Options in the import dialog:

  1. After selecting the DSN in the Get Data window, click Advanced Options to open the SQL Query editor.
    image

  2. Write the SQL query that targets the Smartsheet data you wish to extract.

  3. Once the query is executed, the data will be loaded directly into Power BI for further analysis.

Considerations

  • Credential Management: Save your Smartsheet API credentials in Power BI to avoid re-entering them each time you connect.
  • Data Refresh: Set up scheduled refreshes in Power BI to automatically update your Smartsheet data.

Video Tutorial

Conclusion

Following these steps, you can efficiently connect Power BI to Smartsheet using the ZappySys ODBC API Driver, enabling smooth data integration for reporting and analysis. If you encounter any issues or need further assistance, please contact the ZappySys support team.

References

For more information, refer to the Smartsheet connector article