SmartSheet Summary Reports don't import into Power BI

[ChristopherS]

Hi,

I've been importing data from SmartSheet reports into Power BI for a while now. It provide great details of the projects we have in progress. SmartSheet just released a feature call Summary Sheet Fields. You can create a report of these fields and group data from many SmartSheets together. The issue I have is that when I try to import the data into Power BI I get the same error repeatedly. Whereas normal reports will import correctly, without issue, the summary reports won't. The error I get is :

DataSource.Error: Web.Contents failed to get contents from 'https://api.smartsheet.com/2.0/reports/XXXXXXXXXXXXXX?pageSize=500' (400): Bad Request
Details:
DataSourceKind=Smartsheet
DataSourcePath=Smartsheet
Url=https://api.smartsheet.com/2.0/reports/XXXXXXXXXXXXXX?pageSize=500

where XXXXXXXXXXXXXX is the unique report id used by SmartSheet.

I've spoken to SmartSheet and they say it's a connector provided by Microsoft and they can't help. Has anyone else had a similar issue?

[kiconnor]

The solution suggested here does not solve the problem. 

[ibarrau · Accepted solution]

Hi. I don't know the smartsheet api but I have connected several times. Why don't you try to connect with the official connector?

https://powerbi.microsoft.com/en-us/blog/visualize-your-smartsheet-data-in-power-bi/

Regards,

[ChristopherS]

Hi,

I am using the official connector. This is what is causing the issue. It ties into and uses the SmartSheet api in the background.

The workaround I have in place is very manual as I have to extract the file to Excel and save it to a particular location.