Internal Server Error w/ API PowerBI connection.

gzimmer2

I am migrating to using a direct Power Query API connection which works right now. It's not the most secure option to hardcode your api-key into your M code script but it works and that's the best we got right now:

NOTE: You have to make sure you are connecting in "Anonymous" mode which allows you to use the API Key in the Web.Contents function. Look in your Data Source settings → Credentials. Don't use the "Web API" method that pops up, it's not useful for most APIs since it only works when the API key is in the query, but most APIs (Smartsheet included) put the API key in the header.

let
    // Smartsheet API Base URL (replace sheets with reports if that what you need)
    BaseUrl = "https://api.smartsheet.com/2.0/sheets/",
    
    // Replace {sheetId} with your actual Smartsheet sheet ID
    SheetId = "{sheetId}",
    
    // Your Smartsheet API access token, replace with actual key
    AccessToken = "{api_key}",
    
    // Full URL for the specific sheet
    FullUrl = BaseUrl & SheetId,
    
    // Web request with authentication headers
    WebRequest = Web.Contents(FullUrl, [
        Headers = [
            #"Authorization" = "Bearer " & AccessToken,
            #"Accept" = "application/vnd.ms-excel"
        ]
    ]),
    
    // Convert the binary content to an Excel file
    BinaryContent = WebRequest,
    ExcelWorkbook = Excel.Workbook(BinaryContent)
in
    ExcelWorkbook 

jroob

https://community.smartsheet.com/discussion/comment/478360#Comment_478360

Thanks for sending this. I tried to imitate this in M code but get the following error on the web request query step.

"Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referrer"

Any insight into what I'm doing wrong? Thanks!

Screenshot 2025-03-27 135619.png

gzimmer2

https://community.smartsheet.com/discussion/comment/478378#Comment_478378

The answer is in the error you got. You have to make sure you are connecting in "Anonymous" mode which allows you to use the API Key in the Web.Contents function. Look in your Data Source settings → Credentials

truyenh

SmartSheet api endpoint /home seems to be working now. Anyone see the problem resolved?

gzimmer2

https://community.smartsheet.com/discussion/comment/478381#Comment_478381

Still getting error:
Details: "Failed after multiple retry attempts."
when I use the PowerBI Smartsheet Beta Connector with the US region

jroob

https://community.smartsheet.com/discussion/comment/478380#Comment_478380

It is set to Anonymous setting in the data source creds. I tried it on my existing pbix file and from a brand new blank pbix file with no other credentials in it, just to make sure there wasn't a possibility of cached/existing credentials causing issues. I'm not sure what it is that I'm doing incorrect.

Javier Cuesta_UPL

https://community.smartsheet.com/discussion/comment/478295#Comment_478295

Hi, I got the temporary fix and it's working now.

MS2104

@Javier Cuesta_UPL , how did you get Smartsheet to apply the fix for you? They offered me the fix too and asked me to confirm the email addresses of affected users. After I did so, I got an auto reply that said the email address they used to write me was no longer valid? It made me think I got phished for a minute. It's one thing to have a technical error, but it's another thing to leave users in limbo like this. It's affecting reporting, personal reputations, and the wisdom of storing data in Smartsheet. I love Smartsheet, but it's nearly impossible for me to continue to advocate for Smartsheet at my company after seven days of this issue.

tyip

@Javier Cuesta_UPL what did you do for the temporary fix? I cant seem to get anything to work and its been almost 2 weeks for my stakeholders since this issue and not being able refresh the data. This is getting more aggravating each day this passes with no solution.

Randy Saad

Who else is still having an issue? I am.

Nelizemarie Cloete

I am receiving this error in Power BI when tryin to refresh my Smartsheet data and I am not completely sure why. It has been happening for a few days now.

DataSource.Error: Web.Contents failed to get contents from 'https://api.smartsheet.com/2.0/home' (500): Internal Server Error
Details:
DataSourceKind=Smartsheet
DataSourcePath=Smartsheet
Url=https://api.smartsheet.com/2.0/home

ao12

I had the same issue last week, opened a ticket. It did resolve later that day, but here we are again, with the same error, and no explanation. Incredibly frustrating!

Randy Saad

I've had my case escalated and am still not any closer to a resolution. For those still experiencing the issue, did you connect your Power BI report to Smartsheet via the "Smartsheet (Legacy)" connector? I haven't been able to get my existing reports working and I get the same "Web.Contents failed" error when trying to create a new report.

leonastomenio

I temporarily used the web to connect to Smartsheet. The required link is the published version. However, the refresh rate is very slow, and there's an error whenever changes are made to the grid. I have submitted a request (case number 08090706) and am hoping for a fast resolution.

Randy Saad

For those still having problems with the Power BI connector…

1. Do you have access to a large number of Smartsheet assets/workspaces?
2. Can you use the "Smartsheet (Beta)" connector in Power BI?
3. When the Navigator window loads after you've connected to the Smartsheet (Beta) connector in Power BI, is there a number next to the "US" folder that increases by increments of 1000?
4. If so, does it stop at 10000?