REST API Calls and XML Parsing in Power Automate Desktop - Advanced Use Case

a rest API call is like a Google search you do a request and get a response back the request is made by putting in a URL here we use this UL in this video you can find the url in the video description below I strongly encourage you to do these exercises with me you will learn 10 times faster so we will use this URL for making the API call and the response we get back is in XML format you'll see it here these are by the way currencies from the Danish national bank so here you can see we have a root element in this XML file called exchange rates we have a child element called daily rates and that has an attribute with the ID of a date that is the date the currency data are fun then we have several child element called currencies and you can see each one of them has a code so we can refer to them for example let's say I want a British pound sterling I can refer to the code called gpp and then the L I might want to get the attribute called rate that one is over here let's see how we can do a rest API call and pass the XML afterwards my name is Anna Jensen let's learn some Microsoft power automate for desktop so I go into my Microsoft power automate desktop flow the first thing I want to do is to create a project folder for the entire project so I right click on my desktop say new folder I will call this XML project like this I also want to grab the path of this folder to do so I press shift n on my keyboard I repeat shift in on your keyboard and then right click you'll see the copy as path it only works when you press shift n so copy as path go back to Power domain for desktop let's create a variable for this path that is best practice in RPA development and power automate for desktop development so we'll call it something let's just call it project path like this and press enter then the value I can just press Ctrl V that will put in this path remember to move the double quotation mark in the end and in the start you can click save the clever thing now is that we can just refer to this path whenever we want to or to this variable whenever we want to use the path value that is clever because imagine that this project will get moved to a client's computer or college computer then we can just easily change this variable now let's make let's create the API call we'll do that by an invoke web service here drag it in here we need the URL and that was the URL that we talked about it's in the video description below Ctrl C card the URL in here and paste it in here the method we're using that is get we're getting something down we're getting some data down some other common methods that that is post where we post things to for example a web server or we can delete things there what we are using get we also want to go down to save response now we are saving it into a variable that's fine we will save it into a file in a little while the variables produced we have web service response headers that is headers after the response then we have the red web service response that will be our XML file and then we have a status code indicating if it was a success or not so I can click save here try to run the flow and you'll see that over here in web service response you can go in Hover your mouse over here double click there you go we now have our XML that we can work on it looks a little bit weird and we will need to apply a technique to get the data out we want from this but congratulations you made your API call what I want to do first is that I want to go in here and instead of getting the text into a variable click this drop down save to disk I also want to specify the full path so click this little drop down specify full path now I just give the full path where I want to save this XML to so I can work with it the first part we already created up here that was the project path so click this the lecture and then click project path then you'll say backward slash and then we can say qnc rates dot XML like this and I click save so now we just save it to a file and when I run it you'll see that living in this we have it out here in the project folder XML project qngrage.xml I can right click then I can open with notepad there you go we have now saved the XML file to our project folder that was easy wasn't it now we just need to pass these things out and let me just move back to Power automate for desktop so we need to read this XML thing so I find a read XML from file here and drag that one in the file path well we just use the file path up here so in order to make no mistakes at all I just say save that will give us narrow fine go into invoke web service just copy this path here you're welcome to write it but I always recommend copy pasting it will reduce the risk of Eros I don't always remember it myself but I try to do so here Ctrl V paste in this you can see that we're we're having read into a variable called XML document I can click save now we can start working with the attributes that we want to get out that was the for example the bridge pound rate so I go up here and I'll say get XML element attribute I can drag it in here we're reading from the Excel XML document that is fine that was the one that we created up here then we'll it'll say export query XPath is very easy to learn but I recommend spending a little bit of time on it and let me show you what it is so we can just say we want to go to a specific part in the XML tree so first we'll say I can refer to this root element I can do that by having a forward slash that means root elements start here but we can also do a little bit in a little bit easier thing I just want to get to a qnc element in that way I'll have two forward slashes so that means I'll start in a child element and here I'll just say shouldn't see like this and then I just want to say I want to get a currency with the code British pound sterling oh let me just copy here Ctrl C and drag it in here here I need some hard bracket then an F like you know in an email control V paste it in then instead of these double quotation mark make single quotation marks like this and then a hard bracket end so now we're saying we want the currency element with the code tbp that is this element here then I want to say I want the attribute called rate so I just copy this one here and paste it in here now we get the rate let's also rename it so it says XML attribute value rate like this and then I can click save now let's just see if we can get the British pound sterling rate which is we can either find it here or in the browser which is 823.86 if it hasn't changed since we ran it first there you go you can see it over here 823.86 we have passed it that's how easy it is to pass XML data similarly we can get the date out so I just drag this one in and let's see how we can get data out now I just want to look in Daily rates and the attribute call ID so what I can do here is that I can say I want a child element called daily rate the attribute called ID I want to store it in XML attribute value date and I do these things as best practice to rename my variables because in that way it's very easy to see what's going on later on it's just having random names there you go we have our names now let's just solve a case where we expand this a bit so I downloaded the Excel book here you can find that in the description below as well I urge you to do that because we will solve this case we will go to 3A magic here we have it here I want to make an API call for each one of these ISO codes that is qnc codes then I want to put in the rate of these ones here and remember that is in the Danish kronus DKK that is the base currency because it's from the Danish national bank so I want to get each one of them in the correct place and then I want a date in let's see hey what we can do here and that is uh quite easy so let's just fix our flow a bit that we already built so we don't need to rebuild it from scratch because we already have something each time the robot runs we save the file and right now we override it over and over I want this robot to for example run every day every week and then I want to save the XML file so I have some log data about the currency rate what I'll do here is that I'll say get shun date and time like this and drag it in right by right after the set variable this will just give us a date time variable of the current run that will be when the robot runs then I also want to convert this so I'll find a convert date time to text like this drag it in right after the get current date and time the date time to convert that is the queuing date and time so I just go up here I'll choose the custom format then I can specify the format myself that is easy just and this is another great skill to have a Snapchat available know how to work with date and times you will use that a lot so then I say hours hours minute minute if you want to get better in RPA development power to make for desktop development you should join my Discord that's free we have more than 4 000 developers helping each other networking upgrading our careers the link to my Discord is in the description below and it's totally free so here we have we saved this in a formatted date time and now we can use it in the invoke web service so I open this one here then I want to say I want to make this Dynamic where I use the date of the run of the robot so right before currency rates make sure your mouse is there click this little X here then take the form at a date time I also want an underscore between those two so I just do it like this and I'll copy everything Ctrl C I'll click save and then I'll change it in read XML from file so here I'll just paste I'll delete what's whatever was there before and then just paste it in I can click save let's see if this works that we create a unique file whenever we run the robot there you go we now have the unique file and let me just open it so you can see that we actually open it so we create a unique file with a timestamp on so that means that whenever I run the robot again it will just create a new file that will do it over and over pretty clever isn't it now let's read our Excel sheet with these rates in so what I want to do here is that I just want to grab this sheet name because imagine that we have opened another sheet then when the robot runs it will read from this so we need to make completely sure that we grab the correct sheet and it's also back best practice make your robots robust right click rename Ctrl C to copy this one out now we can just close it so what I want to do is to launch an Excel sheet so after I made this request then I want to launch Excel yes I find a launch Excel and drag it in here um I want to open up the following document so what document do I need to open well I need to open the project path and backwards less and we have the currency data and that one is let me just save it here that one was on my desktop so I'll just move it inside the XML project so now it's here so I'll just read it from the project path as well and let me go back here go in here we have the project path and then we'll say qnc data.xlsx here we should really I mean imagine that I misspelled this it we will have an error so what sorry what we can do here I will open it it's going great what I can do here is that I can just right click rename and then copy everything here to make complete issue that I don't misspell it of course we will just get an error well we spelled it right we'll just get an error so but I like to always copy paste when I remember it make instance visible untick that and there's nothing else to do we just click save now we can set the active Excel worksheet so a little bit laggy here so set active let me search for it there you go so here I need to open up the Excel sheet again because now we I have some other things in my clipboard so I'll just do this open up the currency data here I need to save the sheet name Ctrl C and copy the 3A magic over so here I'm just activating it by the name that's all I do there's also closed power to make for desktop has a terrible habit about locking our Excel books if we don't close them probably so I'll have a close Excel let's save it here and say let's save so now we're launching it setting the active Excel worksheet and closing it let's also read from it so we read the data that's in the sheet you'll do that with a read from Excel worksheet drag that one in before the close and after the set active like this we want to not retrieve the value from a single cell but from a range of cells and let's just see what range we want we open it again we want everything from we want it from A1 that is up here we also want the headers on and then we want to end in the end row end Row 8 and the column D we don't need the Danish Chronos because that is our base currencies we can't even find it in this XML so A1 to d8 that's where we want to move let me just close it again so if I go in here I'll say a and I'll say 1 the end column is D and then we'll say end row was eight now I go into advanced first line of range contains column names I can click save let's try to run it and see that it actually works we are launching the Excel and then we're setting the active sheet we're closing it again we we didn't do anything other than reading but if you go over here to Excel data you can see that we now have our data that we can work with now we need to iterate through each one of these rows and then we'll do the XML path so first one thing at a time let's find a reach yep drag it in after the read from Excel worksheet but before the close Excel the value to iterate that is the Excel data like this we can see we store it into a Q and item variable that is just a variable that we refer to that represents each one of the rows that we're iterating through so that is a data row you can see a data table as a collection of data rows so this refers to the current row that we're entering through so we'll use that in a little while so right now we got the XML file that was one file with all the currency rates we want to do one get XML attribute for each one of these currencies in the Excel sheet so go up here press Ctrl and Mark these two say Ctrl X like this press with your left Mouse the end here that's important then you can just press Ctrl V we paste them in here now um we need to to do a little bit of um we need to update this but first we will um before before the for each we'll need a helping variable that is to count where we add in our data table so first we add row zero let me just show you because in a data table the first row is name zero one two three four five six so I'll make a variable called Excel row index like this and let me just fix that one here so set variable like this and now um here I will say the variable that will be Excel row index like this and we will have it as zero then I can click save so now um we started at zero then we just need to add one to it in each one of the four edges so go up here and then we'll say the variable um that one will be Excel row index and if you like these videos please give it a thumbs up that will help me a lot thank you that is highly appreciate it if you want to give it a thumbs up so then I'll say Excel row index plus one we add one to it now so we get the date that is fine we'll update each one of the rows with this state but this get XML element attribute that is quite static you can see DBP and if I open up my Excel sheet yeah I want it to not be GDP but whatever is in the iso column that one was the second column so in our data table it will be the first column so what I can do here is that I can go back here and instead of this GBP I can just remove this and I can say current item yeah and then I can say I want to look in um whatever is in the first uh the one column that will be the second column so I'll just do like this and this one will give us a each one of them as day progresses so the Q a item that will be the Q data row and then we will look up whatever I show code is in there you can click save so now we can just update the data table and then what we'll do here is that we will find a update data table item yeah drag it in here so we need to update two things there um we need to update the the rate and the the date so the data table that we want to update that will be the Excel data the column that we want to update the rating and let me just here so the rate that will be in the fourth column that's number three and the date is in number two look like this let's go in here again so the column that will be column three the row well that will be the Excel row index so I just go up here like this and then the new value well that will be the rate that we just got up here XML attribute rate so we already got that one like this and then we click save similarly we have an update data table item like this so data table Excel data and then that column that was the two this one will be um this one will be the date so here so the row that will be the Excel row index and then the new value that will be the XML attribute called date like this then we can click save so we also need to save so now we updated the data table we read it up here but now we update it we also need to save it back to excel very very easy so find a right to Excel worksheet drag it in after the for each so we update the data table and after we're done with the forage we write it back to excel what value do we want right we want to write the Excel data like this we want to write it on a specified cell column a row 2. let's see if everything works make sure that my Excel sheet is closed and then I'll click run so this is a great case of API calls and XML passing you'll see that we get our rates here we are running it around eight times let's inspect our Excel sheet and see that we have everything right there you go we got the dates and rates click the video up here for the next Advanced power automate for desktop use case I'm Anna Jensen see you

Channel: Anders Jensen

Views: 5,034

Keywords: power automate desktop, microsoft power automae desktop, anders jensen, power automate desktop xml, power automate desktop api, power automate desktop rest api, power automate desktop api call, power automate desktop xml parsing, api call in power automate desktop, xml parsing in power automate desktop, power automate desktop advanced use case, power automate desktop use case, power automate desktop tutorial

Id: dDhsyfbPChw

Length: 23min 0sec (1380 seconds)

Published: Thu Sep 29 2022