Connect Smartsheet Resource Management API to PBI

[RKK]

Hi Team, 

I’m trying to connect Smartsheet Resource Management to Power BI. While I don’t have direct access to Smartsheet, I do have an API key that I’d like to use for the connection.

I’m familiar with the standard Smartsheet API endpoint https://api.smartsheet.com/2.0/, but I’m not sure what the correct endpoint is for accessing Resource Management data. Could someone guide me on which API endpoint to use specifically for Smartsheet Resource Management, and how to set up the connection in Power BI?

I’ve tried using the following endpoints and encountered errors:

https://app.smartsheet.com/r/rm - getting below screen 

https://api.smartsheet.com/rm/v1/projects - Web.Contents failed to get contents from 'https://api.smartsheet.com/rm/v1/projects' (404): Not Found

https://api.smartsheet.com/rm/v1/users/me - Web.Contents failed to get contents from 'https://api.smartsheet.com/rm/v1/users/me' (404): Not Found

https://resource-management.smartsheet.com/api/v1/users/me - The remote name could not be resolved: 'resource-management.smartsheet.com

https://api.smartsheet.com/rm/v1/users - Web.Contents failed to get contents from 'https://api.smartsheet.com/rm/v1/users' (404): Not Found

https://resource.smartsheet.com/api/v1/users/me - The remote name could not be resolved: 'resource.smartsheet.com'

Any help would be greatly appreciated!

[v-hashadapu]

Hello @RKK , Just getting back to see if the shared details answered your question. If so, marking it as "Accept as Solution" would be greatly appreciated to guide others in the community. Feel free to reach out with any additional questions!

[v-hashadapu]

Hi @RKK , I wanted to check with you and see if the information provided by @HCA was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!

[HCA · Accepted solution]

Hi @RKK , Smartsheet support has confirmed through multiple threads and tickets that there is no officially supported connector for RM to Power BI. Even with a valid API key, authentication fails or endpoints return 404/500 errors when accessed via Power BI.

Currently, the only workaround is to manually export data from RM (CSV/Excel) and import it into Power BI. Smartsheet has introduced Advanced Reports in RM, which can export data to Smartsheet core. If your organization uses this, you can then use the Smartsheet (Beta) or SmartsheetGlobal connector in Power BI to access that data.

If you must use the API, you will need to build a custom connector using Power Query SDK or use an intermediate service (like Azure Functions or Power Automate) to fetch RM data and store it in a format Power BI can consume.

For reference: How can I pull reports data from Resource Management connect to Power BI? — Smartsheet Community

If you found this useful, Please mark it “Accept as Solution” to help others and give a ‘Kudos’.

[v-hashadapu]

Hi @RKK ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[v-hashadapu]

Hi @RKK , Thank you for reaching out to the Microsoft Community Forum.

First, make sure your API key is from Resource Management, not Smartsheet. To get one, log into Resource Management, go to My Profile -> API Access and generate a key. If you don't have access, ask your admin. Then to test it, In Power BI, go to Get Data -> Blank Query, Open Advanced Editor and paste:

let

    url = "https://api.10000ft.com/api/v1/users/me",

    response = Web.Contents(

        url,

        [

            Headers = [

                #"Authorization" = "Bearer YOUR_API_KEY",

                #"Content-Type" = "application/json"

            ]

        ]

    ),

    json = Json.Document(response)

in

    json

If this loads your user info, the key is valid. Now pull your project data:

let

    url = "https://api.10000ft.com/api/v1/projects",

    response = Web.Contents(

        url,

        [

            Headers = [

                #"Authorization" = "Bearer YOUR_API_KEY",

                #"Content-Type" = "application/json"

            ]

        ]

    ),

    json = Json.Document(response)

in

    json

Once it loads, click To Table, then expand the columns to see your project details.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[RKK]

Hi @v-hashadapu 

Thank you for your guidance.

I followed your instructions and used the following query:

let

    url = "https://api.10000ft.com/api/v1/users/me",

    response = Web.Contents(

        url,

        [

            Headers = [

                #"Authorization" = "Bearer YOUR_API_KEY",

                #"Content-Type" = "application/json"

            ]

        ]

    ),

    json = Json.Document(response)

in

    json

When prompted with "Edit Credentials," I selected Anonymous, but encountered the following error.

Error in loading the Project as well:

My Admin confirms that, API key is from Resource Management not for Smartsheet. 

[RKK]

Hi @v-hashadapu 

Please find the below result for https://api.smartsheet.com/ppm/v1/projects.

Thanks

[v-hashadapu]

Hi @RKK , Thank you for reaching out to the Microsoft Community Forum.

In Power BI, go to Get Data -> Blank Query, open the Advanced Editor and paste the below code.

let

    url = "https://api.10000ft.com/v1/projects",

    response = Web.Contents(url,

        [

            Headers = [

                #"Authorization" = "Bearer YOUR_API_KEY",

                #"Content-Type" = "application/json"

            ]

        ]

    ),

    json = Json.Document(response)

in

    json

This retrieves a list of projects. You can change the endpoint to others like /v1/users or /v1/users/me depending on what data you need. Power BI will let you transform the JSON response into a table for analysis.nIf you get a 404, double-check the endpoint. A 401 usually means your API key is incorrect or doesn’t have the right access. Make sure it’s created specifically for Resource Management, not just core Smartsheet.

That Compatibility Mode warning in the screenshot relates to Internet Explorer, not the API issue, but it’s best to use a modern browser like Chrome or Edge when working with Smartsheet in general.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[RKK]

Hi @v-hashadapu 

Thanks for your response.

I tried using the M code provided above, but encountered the below error. Could you please review it and let me know what might be going wrong.

Thank you

[v-hashadapu]

Hi @RKK , Please modify the M Code by changing URL to 

https://api.smartsheet.com/ppm/v1/projects. then try again and share your results. 

Thank you.

[pankajnamekar25]

Hello @RKK 

Use blank query and paste this M code adjust as per your API Key

let

    url = "https://resource.smartsheet.com/api/v1/projects",

    response = Web.Contents(url,

        [

            Headers = [

                #"Authorization" = "Bearer YOUR_API_KEY",

                #"Content-Type" = "application/json"

            ]

        ]

    ),

    json = Json.Document(response)

in

    json

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[RKK]

Hi @pankajnamekar25 

Thanks for your response.

I tried using the M code provided above, but encountered the below error. Could you please review it and let me know what might be going wrong.

Thank you!