Author: Valentin Gasenko, Senior Power Platform Developer
For this article, I will explain how I used Power Automate Desktop to pull specific data from web pages to an Excel spreadsheet. This data is used as part of a custom report for personal purposes. Before walking through the build process, I want to make a disclaimer by saying that this is my first more or less serious Power Automate Desktop flow and, though this flow works, I am sure there are more efficient ways to build this. I look forward to your comments, which I will use in future articles.
Additionally, please note that I will build this flow manually. Power Automate Desktop does provide the user the ability to record his actions through Web Recorder and Desktop recorder. I believe these tools are better explained by video than in writing.
Also, if you are already familiar with this tool and/or you just crave technical nuances, then I strongly recommend going straight to the technical part (to the list of Tools or PAD Action), otherwise, you may find the intro boring and dragging.
A little lyrical digression (I warned you 🙂).
Many friends ask me – what do you do? I use to answer briefly – programming, and, as a rule, most of my friends accept such an abstract answer and move on to a new topic, but some of the more meticulous ones continue to ask counter-questions: what exactly do you program? What kind of platform? What programming languages do you use and so on.
And I noticed that when I talk about the Power Platform, despite my glowing eyes and stories full of fantastic beasts and other charms – my friends, who are far from the platform, have a look of bewilderment. They understand that this is cool and useful stuff, but do not fully realize why an ordinary person needs it all and how it could, for example, personally simplify their life.So, thanks to Power Automate Desktop Flow I can now easily inform my friends about the convenience of the platform and even invent a case, which makes everyone absolutely delighted =)
I am a fan of Apple products. Not that I have the money to furnish my whole house with it, but I have bought some items and generally enjoy it. The big downside, though, is that writing code for my favorite Dynamics 365/Power Platform is a real pain, (especially if I want to use XRMToolBox and other goodies), but that will be a separate article about that (maybe next time).
So, with the release of the iPhone 13 Pro, I wanted to get this model. Not Pro Max, but exactly Pro, the middle version at 256 gigabytes.
In Kyiv, two stores sell Apple products, which are “fighting” for my attention to their prices. I could say that they are competitors: when one is dumping, the other is dumping almost immediately.
To be honest, I’m tired of checking the prices of iPhones in these stores almost every day (or even a couple of times a day, because prices change almost every hour), so I came up with an idea to make life easier: Power Automate Desktop Flow!
The task is as follows: in the background, every N hours the sites of my favorite stores should be opened, the information should be extracted from them, inserted into an Excel file, the file should present the price trends, and I should also see which of the competing stores currently wins in terms of the lowest price.
Tools:
- Dynamics Dev subscription (free);
- Power Automate Desktop Flow (free);
- On-Premise Data Gateway (free);
- Desktop Connectors (there are nuances, but generally free);
- Power Automate plugin for browser (free).
As announced in March 2021, Power Automate Desktop is now available for free to all Windows 10 users. To get started go to https://flow.microsoft.com/en-us/desktop/
Since we will be pulling data from defined web pages we will also need to install the Power Automate extension for our web browser. For reference, the Microsoft Power Automate extension is the add-on for enabling web automates – automate things like web scripting, data extraction, web testing. The Power Automate extension supports the four most popular browsers: Microsoft Edge, Internet Explorer, Google Chrome, and Mozilla Firefox. These browsers need additional configuration or/and browser extensions to work.
I use Microsoft Edge as my browser. Please follow the steps below to install:
- Open an instance of Microsoft Edge;
- In the upper right-hand corner click on the ellipse (…);
- Click on Extensions;
- Click on Get extensions for Microsoft Edge;
- Search for Microsoft Power Automate and install.
When the Power Automate icon is highlighted (in blue) it means that Power Automate can “communicate” / “listening” with the specific website. If the icon is gray then Power Automate Desktop is not able to “talk” to the website.
( ! ) Please note that PAD must access the Dynamics Environment, otherwise no manipulation will be possible. You must have a subscription to use this service.
I don’t think I need to explain how to get a Dev subscription but I’ll leave the link here, just in case:
So, we have an environment, which is the access key to the PAD. Now let’s launch Power Automate Desktop (PAD) through your Windows Search bar and click Open.
With the Power Automate Desktop (PAD) launch page open click on +NewFlow (upper left-hand corner) to start a new flow.
When Create is clicked two things happen. The new flow is added to your My Flows list. Also, the Power Automate Desktop flow designer Flow canvas is created. There is an Actions column, workspace (center of page), and a Variables column.
Note the incredible number of actions PAD offers us. However, as you will find out later, even this is not enough, for there are nuances.
Let’s determine for ourselves the order of our actions. What should be done and in what sequence?
- Launch the browser;
- Go to the desired link;
- Take the information about the price;
- If the information is in text form – convert the text to a number, so that you can then build the dynamics;
- Start Excel;
- Record the data in Excel;
- Save the Excel;
- Close the browser.
It turns out that PAD provides tools as close to our native language as possible. And most of the commands even have the same name as the action we want. This makes our work a lot easier.
In the search box, enter Launch. Here we are offered several browsers, as well as Excel and Outlook.
Let’s go in order. Launch the browser.
For example, if you put a plugin in Edge, it makes sense that we would run Edge as well.
In startup mode, we can choose to create a new instance, or to start in an existing one (if there is one).
In the URL field, we write the link to the site from which we will get the data. The rest we leave by default. Plus, pay attention to the Browser variable, it will come in handy.
Next comes the most interesting part of our flow – getting the item from the web page.
Let’s run this command. As the web browser, we type in %Browser% – the same constant variable we created in the previous step.
Next, the UI element. To determine it, we need to click Add UI element and go to the page from which we draw data.
A child window opens to provide you selection the element. Using the control key and the left mouse button select the element that contains the information we need.
We see that one item has been added to the Tracking Session. We click the Done button.
Now we have the following situation:
The Advanced setting is the default – taking the text of the element.
But the Variable can be changed as you want. This variable in my case is called Price.
That’s it, the first indicator is stored in the variable Price. If you, like me, need to collect information from multiple sites – use action Go to web page:
Using the browser you already have open, you can navigate to other sites, and just as we did in the previous step, you can get from them everything you need. It’s important to remember to give each following variable a unique and self-explanatory name.
A task for your own practice: if you need to convert text into number and get rid of unnecessary characters, try to play with commands Get subtext, replace text, convert text to number.
Next, we have all variables, which store values from different sites. Let’s write their values to Excel.
To do this, we call the action with the same name.
If you already have a file (on your desktop, for example), specify the path to it explicitly. Give the variable you’re going to use for the instance an intelligible name.
Next, use a cool command to help us build the trends and write the data one by one – get first free column/row.
Here I’m writing an excel variable, and I also want to get a variable from this action, which will contain the first free column. I will have the explicit string, so I don’t need this variable.
And finally, we do what we wanted to do – we write the values to Excel.
Try playing around with variables and dropdowns in different commands. It’s quite possible you’ll find something interesting.
After the values are written to Excel, we clean up after ourselves. Close Excel and close the browser.
I ended up with a relatively compact 23-step flow (half of the steps involved duplicate actions for different sites – that’s why there are so many of them):
Try your flow in debug mode. Check to see if everything is assembled correctly.
The next point is more interesting.How do we automate this?
So that our flow doesn’t run manually but on a schedule.
This is where we need the cloud, and to connect our workplace to the cloud we have to download this little thing:
My big mistake was to change the default settings here in order to create a correct connection. I strongly recommend that you do not touch anything hereafter installation. Just log in to your MS profile and close this application.
Open the browser, go to our environment, and list of flows.
To make everything nice and correct, let’s set up the connection to the desktop flow:
Next, when creating a connection, we can choose between two options – to talk directly to the machine or via the data gateway.
If you want to access the machine directly, then you have to open the Power Automate Machine Runtime configuration on your local computer and configure your computer name and access to it:
As a last resort, use the configured Gateway. Use your MS account when specifying a username and password. Next, create a recurrence flow. I have created a flow that would run every 6 hours.
Select the Run a flow PAD step.
If the connection to the desktop flow has been set up correctly, the configuration will pick up automatically:
I save the flow and wait for it to start. We can activate it manually and check how it all happens.
If everything is set up correctly, it feels like magic. Windows start up by themselves, something is written somewhere, something happens without our intervention. It is a bit scary, but terribly interesting.
And, of course, the results are gratifying. In particular, the speed of execution:
And there is a feeling of complete satisfaction when you look at the Excel results:
Of course, I had to build the graph on my own, but I only put it on the data that comes into my excel every day, and then the result still comes out dynamically.
As we can see – prices are going down! Maybe by Black Friday the prices will collapse completely. In the worst-case – they won’t =)
Good luck with your accomplishments!
