Power BI - dataflow not refreshing data

razvan_mihai

Hello,

I have had the same dataflow connected to a Smartsheet report for months and suddenly it is not refreshing anymore.

Error: Error: DataSource.Error: Web.Contents failed to get contents from 'https://api.smartsheet.com/2.0/reports/874834112419?pageSize=500&page=460' (500): Internal Server Error Request ID: 7bb7ade0-aa0f-80d3-348d-2e3c6c9f2ed7 Activity ID: 92a5870d-07a0-463f-91d9-b32482ea1d82

This only happens when I try to connect the dataflow with Reports from Smartsheet and not to the Worksheets.

Curious is that when I try to build the dataflow and perform ETL everything runs perfectly, data is being retrieved. But when I save the dataflow and refresh it, it faills and I receive the afore mentioned error.

In few words, only the blue icons work but not the orange ones.

cindy_chan

I'm experiencing the same error - no issues with refreshing for the past year and started getting this error since Monday

Bassam Khalil

Hi @razvan_mihai,

The error you're encountering with your Smartsheet dataflow in Power BI, particularly the "500: Internal Server Error," typically indicates a problem on the server side of Smartsheet's API. This kind of error can be triggered by various issues, such as temporary server downtime, changes in the API endpoints, or issues with the specific report you're trying to access. Given the details of your issue, here's a structured approach to troubleshoot and potentially resolve the problem:

1. Check Smartsheet API Status and Updates

• First, ensure that there are no ongoing issues with the Smartsheet API by checking their official status page for any service disruptions or maintenance that could affect API performance.
• Review any recent changes to the Smartsheet API documentation to see if there have been updates or deprecations that affect your dataflow.

2. Validate the Report URL and Permissions

• Double-check the report URL (https://api.smartsheet.com/2.0/reports/874834112419) to ensure it is correct and that the report ID hasn't changed.
• Verify that your API token still has the necessary permissions to access the report. Permissions might have changed, especially if you're relying on shared access.

3. Pagination and API Limits

• You mentioned the issue occurs at pageSize=500&page=460. This is a large dataset, and the error might be related to pagination limits or a timeout due to the size of the data being fetched. Try reducing the pageSize or accessing a smaller range of pages to see if the issue persists.
• Check if there have been any changes to rate limits or data caps for the Smartsheet API that might affect large data fetches.

4. ETL Process Verification

• Since the ETL process works fine but the refresh fails, compare the configurations and permissions between these two processes. There might be differences in how they authenticate or handle data that could clue you into the problem.
• Ensure that any transformations or queries applied during the ETL process aren't causing the issue when the data is refreshed.

5. Test with a Different Report

• To isolate the issue, try connecting to a different Smartsheet report with a similar size or data structure. If the new report works, the problem might be specific to the original report.

6. Log Analysis and Support

• The Request ID and Activity ID provided in the error message are key to diagnosing the issue. Contact Smartsheet support with these IDs, as they can use them to look up the specifics of your error in their logs.
• Consider reaching out to Power BI support as well, as they might have insights or suggestions specific to the integration.

7. Workarounds

• As a temporary measure, consider manually downloading the report data from Smartsheet and uploading it to Power BI until the issue is resolved. This isn't ideal for large datasets or frequent updates but can serve as a stopgap solution.