In our previous articles we have covered features of Microsoft’s low code platform “Power Automate”. In this article we will be covering a basic end-to-end design for a flow on Power Automate.
We will be taking the following example. Where we must automate input data on a google form and have to copy this data from Excel.
This is the data:
We must input this on this google form:
This form requires filling all the data from the excel file.
Now that we have gotten well versed on the task. We will start working on Power Automate.
Getting Started
First step is to open Power Automate and click on the “New Flow” button:
Give it a name according to your preference, we will name it as “Demo”:
As soon as you click on “Create” it will redirect you to the “My flows” page:
Double click on your flow to edit and it will allow you to edit your flow according to your preference:
Now all actions added in this flow will be performed by Power Automate automatically.
Setting Variables:
Just like any other coding assignment, we will start by assigning two variables:
- Firstly, one for the PATH of the excel file.
- Secondly the link of the google form.
This will be done by searching for “Set variable” on the left panels search box under actions:
Drag and drop the “Set variable” action and it will prompt you by asking the variable name and the value of the variable:
Save it and now you can use this “Path” anywhere in your flow.
We will do same for the google form:
Launching Excel:
Next step is to launch Excel so that the copying of data can be done. Simply search for Launch Excel and similarly drag and drop it on to your flow as the third step:
From here choose the variable of Path as the “open the following document” when excel is launched:
Reading Excel Data:
Now that excel is open with the desired excel file. We will be reading data from it. And to do that search for “Read from excel worksheet”:
And again drop it to your flow, and it will ask you the following prompt:
The excel instance is the one that we have launched in our previous action. Users can launch several excels and conduct reading across different instances. Replicating the act of switching tabs by humans.
Now since we have to copy all the data and use it, we will ask it to read from all columns and store it in a variable, and then use specific data through arrays.
Make sure to change retrieve to “Values from range of cells” and then define range you want to copy:
Now all our data is stored in ExcelData variable, and we will use it to fill the form. Since our work on excel is done we will close this particular excel instance using close excel action:
This is how our flow looks like right now:
We have completed the first step that is copying data from excel.
Launching Browser:
Now we have to paste it on the google form. Similar to launching excel we will open our browser:
Search for launch new chrome, users can choose according to their preferred browser. We will drag and drop this action and add the form link as the page to launch:
If we run our flow until this point this is how it will pan out:
This form is opened automatically. The only human intervention was to start running the flow. Next we will fill the first text box labelled as “First Name” on the form.
Automating Form Filling:
Since we already have our data copied, we have to populate data in this specific text box. Hence we will go back to our power automate and under the actions tab search for “Populate text field on web page”:
We will drop this on our flow:
The first step will be choosing which specific UI element we want to populate. Click on UI element and it will ask you to “Add UI element.”
Click on it:
And this is how you will select the element you want to populate. These elements are taken from the HTML CSS of the webpage. Now suppose we want to fill for the First name we will hover over it, and press “CTRL+LEFT CLICK” and it will add that UI element:
Here we can see how it identifies it as “Input text.”
This particular UI element has now been added to our flow:
Next step is what text to populate. And thar we will bring from our excel data array. If we visualize our excel data, the problem might get clearer:
ExcelData[0] will output the first row, 1 will output second and so on. But we want individual column values of each row hence we will use a for loop and for each column we will iterate through the rows of excel data. We will firstly add a for loop:
This is how we will set our for loop:
Now the text to populate will be CurrentItem[0]:
Similarly, we add UI elements for rest of boxes:
We will populate this with the last name, that is second value in current item:
All these values are added automatically. We will do the same for all other values by adding their UI elements and then adding their corresponding values.
Submitting the Form:
We will skip the rest for this tutorial and submit the form with these two values.
To submit we have to click on the submit button and this will be done by using the action “Click link on webpage”:
We will add this as the button to be clicked and now we have completed our whole submission flow. This will continue to submit all our values from excel, and we can stop by closing our chrome session:
This will complete our entire flow and automate submissions to the form.
This is just a surface level application of this tool, and it can do so much more.
