Connecting Multiple Smartsheets

Hi Power_It_Up ,

Thank you for reaching out to the Microsoft Community Forum.

Power BI's Smartsheet connector is limited, it only connects to one sheet at a time and there's no native folder-style connection like there is for Excel or CSV files stored locally or in OneDrive/SharePoint.

Please try below options.

Option 1: Manually connect and append multiple Smartsheet sheets

Use Power BI’s Smartsheet connector to bring in each sheet individually. Keeps data live and connected to Smartsheet.

Note: When the number of Smartsheets is small and manageable.

Option 2: Export to Excel and store in a network or cloud folder

Users export their Smartsheets as Excel files and drop them in a shared folder. Power BI then uses a Folder connection to automatically combine all files. Scalable, easy to automate with Power Query.

Note: High volume of sheets and when near real-time data isn't required.

Option 3: Use Power Automate to export Smartsheet data to a common storage

You can automate the export process using Power Automate or Smartsheet API: Send each sheet's data on a schedule to an Excel/CSV in a SharePoint folder, OneDrive folder, or Azure blob storage. Then, connect Power BI to that folder using a Folder connector (which supports SharePoint and OneDrive too).

Question: Is there a way to have multiple Smartsheets saved in a folder (within it's own environment) and then combine them in Bi?

No, Smartsheet doesn’t expose a "folder" of sheets as a structured data source that Power BI can consume like a folder of Excel files. Each sheet is an individual API endpoint or connection.

Recommended Approach:

1.Use Power Automate to extract Smartsheet data regularly and write to Excel/CSV in SharePoint or OneDrive.

2.In Power BI, use SharePoint Folder or OneDrive Folder connector.

3.Use Power Query to transform and combine the files as needed.

Please refer below community thread and documents.

Connect to Smartsheet with Power BI - Power BI | Microsoft Learn

Visualize your Smartsheet data in Power BI | Microsoft Power BI Blog | Microsoft Power BI

Solved: Get data from multiple files in Smartsheet - Microsoft Fabric Community

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

Hi Power_It_Up ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you