Forum Discussion
Connecting Multiple Smartsheets
Hello.
I have a Power Bi workbook which connects to a single Smartsheet. Due to its' 20,000 row limitation, there will be a need to connect to multiple sheets as time goes by. I assume the only way we can bring in additional Smartsheets into Bi, is to connect to them manually and either append to the current data source, or use DAX to combine them.
Another possibility is that the user exports the data to Excel and places them in a shared folder on the network and then use FOLDER connection to combine them all. Each Excel file that would be added, would seamlessly be imported into the workbook when refreshed.
My question: Is there a way to have multiple Smartsheets saved in a folder (within it's own environment) and then combine them in Bi? I do not see a connection available for this.
If you have any other ideas, I would be most grateful if you could share them.
Many thanks. 🙂
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 youHi 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
4 Replies
Hello Power_It_Up
Unfortunately, no , I did work lot with Smartsheet and integration with Power BI but you have to select single from folder means it will work for selection then you have appended multiple sheets.
Other way within Smartsheet you can combine all sheet from folder to single sheet and connect that to Power BI , But there is low limitation again
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
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 youHi 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 youThank you! The best approach for my current set-up, is to import the files as and when needed, then use this Power Query to connect them all up:-
= Table.FromPartitions("Origin",
{
{"V1", #"List V1"},
{"V2", #"List V2"}
}
)Thanks again!
