How To Connect Orchestrator With Excel Using Odata Feed?

News Knowledge Base
1

How to connect Orchestrator with Excel using Odata Feed?

Setup the API account: Consider creating a specific account for the API access. Because the password will be hard-coded in the query in a later step, it would be a good to have an account specifically for the API access. For example, a role called API is created that has read-only access:

1.png

  • Create a local account (non-AD account) and assign it the API role.

2.png

Use any local account that has a password ( use an AD authenticated account). But creating a special account for the API access with just the permissions would be the best practices.

Post creating the account, remember to login at least once to change the password, as it is required to change the password after the first login. Post changing the password, take note of the new password for the next steps.

Power Query: Post having a local account and have the password, it is time to use Power Query to authenticate and connect to the data source.

Authenticating:

Authenticating to the API requires the use of a bearer token as documented in the Orchestrator Reference guide 83. It is needed to craft a query that retrieves the bearer token and then passes the bearer token to the API request.

  • To do that, first create two queries:
  1. One to store our base URL and one to store our credential information. Click on New Source → Other Sources → Blank Query. Rename this query as BaseUrl (note, Power Query is case-sensitive) then open the Advanced Editor. The query here will be simple:

let

Source = "https://cloud.uipath.com"

in

Source

  1. Replace the URL with the URL for the Orchestrator instance. At the end, have a query that looks something like this,

3.png

  1. Click Done
  2. Next, create a record to store the credential information. Create a new blank query and name it Credentials. In the Advanced Editor type in,

let

Source = [

tenancyName = "Default",

usernameOrEmailAddress = "api",

password = "password"

]

in

Source

  1. Change the values to match the tenant, username, and password.

Create API request:

  1. To make an API request, create a new blank query and use this basic template:

let

Path = "/odata/Assets",

Auth = Json.Document(Web.Contents(BaseUrl, [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(Credentials), RelativePath="/api/account/authenticate"])),

Token = Auth[result],

Source = Json.Document(Web.Contents(BaseUrl, [Headers=[Accept="application/json", #"Authorization"="Bearer " & Token], RelativePath=Path]))

in

Source

  1. Replace the Path variable with any API call as documented in the https://docs.uipath.com/orchestrator/reference/read-me guide. The other lines need not be modified as long as the BaseUrl is setup and Credentials queries as specified above.
  2. At this point, a pop-up message is obtained asking to specify how to connect. Click on Edit Credentials and then select Anonymous_ access. Then might get another popup about data privacy. Depending on the use case, select Public or Organizational.

If everything is correctly performed, see the results as something like this:

4.png

  • At this point, expand the list, convert it to a table, and perform any other Power BI data transformation to get the data wanted. For example:

5.png


2

Could someone repost the images in this post?

3

Hello Jeppe,

Please use the below link to access the KB.

https://uipath-survey.secure.force.com/CaseView/articles/Knowledge/How-to-connect-orchestrator-with-Excel-using-Odata-Feed?lang=en_US

Thanks ,
UiPath Support