RPA Challenge 'Input Forms'

System.GetEnvironmentVariable Name: 'USERNAME' Value=> User

Web.DownloadToFolder Url: 'http://rpachallenge.com/assets/downloadFiles/challenge.xlsx' \
DestinationFolder: 'C:\Users\\'+User+'\Downloads' ConnectionTimeout:30 \
FollowRedirection:True ClearCookies:False \
UserAgent:'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.21) Gecko/20100312 Firefox/3.6' \
Encoding:'' DownloadedFile=> DownloadedFile

Excel.LaunchAndOpen Path: DownloadedFile Visible:False Password:'' ReadOnly:False \
LoadAddInsAndMacros:False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 \
EndColumn: FirstFreeColumn-1 EndRow: FirstFreeRow-1 ReadAsText:False FirstLineIsHeader:True Value=> QueryResult
Excel.Close Instance: ExcelInstance

File.Delete Files: DownloadedFile

WebAutomation.LaunchFirefox Url: 'http://www.rpachallenge.com/' \
WindowState:WebAutomation.BrowserWindowState.Normal ClearCache:False \
ClearCookies:False BrowserInstance=> Browser
WebAutomation.ExecuteJavascript BrowserInstance: Browser \
Javascript:'function ExecuteScript() { document.querySelector(\'button\').click(); }' Result=> Result
loop i from 0 to QueryResult.rows.count-1 step 1
    WebAutomation.ExecuteJavascript BrowserInstance: Browser Javascript:'''function ExecuteScript() {
    document.querySelector(\'input[ng-reflect-name="labelRole"]\').value = \''''+QueryResult[i][3]+'''\';
    document.querySelector(\'input[ng-reflect-name="labelEmail"]\').value = \''''+QueryResult[i][5]+'''\';
    document.querySelector(\'input[ng-reflect-name="labelCompanyName"]\').value = \''''+QueryResult[i][2]+'''\';
    document.querySelector(\'input[ng-reflect-name="labelPhone"]\').value = \''''+QueryResult[i][6]+'''\';
    document.querySelector(\'input[ng-reflect-name="labelLastName"]\').value = \''''+QueryResult[i][1]+'''\';
    document.querySelector(\'input[ng-reflect-name="labelFirstName"]\').value = \''''+QueryResult[i][0]+'''\';
    document.querySelector(\'input[ng-reflect-name="labelAddress"]\').value = \''''+QueryResult[i][4]+'''\';
    document.querySelector(\'input[value="Submit"]\').click(); }''' Result=> Result
end
5 Likes

Excellent project @jokoum and a very good showcase of the .ExecuteJavascript action!

I took the liberty of formatting your script a bit and adding some comments in order to make it easier for newcomers to study when they copy paste it in their editor.

#Get the user’s name from the USENAME enviromnemt variable
System.GetEnvironmentVariable Name: ‘USERNAME’
Value=> User

#Download the .xlsx file from the “RPA Challenge - Input Forms” page
#and store it in the ‘Downloads’ folder of the PC
Web.DownloadToFolder Url: ‘http://rpachallenge.com/assets/downloadFiles/challenge.xlsx
DestinationFolder: ‘C:\Users\’+User+’\Downloads’ ConnectionTimeout:30
FollowRedirection:True
ClearCookies:False
UserAgent:‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.21) Gecko/20100312 Firefox/3.6’
Encoding:’’
DownloadedFile=> DownloadedFile

#Launch a new Excel instance and oped the downloaded .xlsx
Excel.LaunchAndOpen Path: DownloadedFile
Visible:False
Password:’’
ReadOnly:False
LoadAddInsAndMacros:False
Instance=> ExcelInstance

#Get the first free column/row
Excel.GetFirstFreeColumnRow Instance: ExcelInstance
FirstFreeColumn=> FirstFreeColumn
FirstFreeRow=> FirstFreeRow

#Get all the values from the Excel instance.
#In order to get only valuable info (populated cells) we can utilize the ‘FirstFreeColumn/Row’ variables from the previous step.
#The exact previous position (FirstFreeColumn-1 and FirstFreeRow-1) is where the outermost entry is located.
Excel.ReadCells Instance: ExcelInstance
StartColumn: 1
StartRow: 1
EndColumn: (FirstFreeColumn - 1)
EndRow: (FirstFreeRow - 1)
ReadAsText:False
FirstLineIsHeader:True
Value=> QueryResult

#Close the Excel instance.
Excel.Close Instance: ExcelInstance

#Delete the now redundant download file.
File.Delete Files: DownloadedFile

#Launch Firefox and visit rpachallenge.com
WebAutomation.LaunchFirefox Url: ‘http://www.rpachallenge.com/
WindowState:WebAutomation.BrowserWindowState.Normal
ClearCache:False
ClearCookies:False
BrowserInstance=> Browser

#Execute Javascript code to click the START button.
WebAutomation.ExecuteJavascript BrowserInstance: Browser
Javascript:‘function ExecuteScript() { document.querySelector(‘button’).click(); }’
Result=> Result

#Execute the following Javascript function for each row in the downloaded excel file
#Note that we know the contents of each column, for example the FirstName values are in the first column of the .xlsx file,
#the Last Name values are in the second etc.
#Each row contains the information of a different individual.
#In each iteration we are getting the entries per row.
#By utilizing Javascript we are explicitly telling the script to input each value in a ng-reflect-name attribute of the inputs in the screen.
#This way we don’t care about the position on the screen, since the inputs change position every time the page refreshes.
#Finally when every input has been populated, we click on the “SUBMIT” button.
loop i from 0 to QueryResult.rows.count-1 step 1
WebAutomation.ExecuteJavascript BrowserInstance: Browser
Javascript: ‘’’
function ExecuteScript() {
document.querySelector(‘input[ng-reflect-name=“labelFirstName”]’).value = ‘’’’ + QueryResult[i][0] + ‘’’’;
document.querySelector(‘input[ng-reflect-name=“labelLastName”]’).value = ‘’’’ + QueryResult[i][1] + ‘’’’;
document.querySelector(‘input[ng-reflect-name=“labelCompanyName”]’).value = ‘’’’ + QueryResult[i][2] + ‘’’’;
document.querySelector(‘input[ng-reflect-name=“labelRole”]’).value = ‘’’’ + QueryResult[i][3] + ‘’’’;
document.querySelector(‘input[ng-reflect-name=“labelAddress”]’).value = ‘’’’ + QueryResult[i][4] + ‘’’’;
document.querySelector(‘input[ng-reflect-name=“labelEmail”]’).value = ‘’’’ + QueryResult[i][5] + ‘’’’;
document.querySelector(‘input[ng-reflect-name=“labelPhone”]’).value = ‘’’’ + QueryResult[i][6] + ‘’’’;

                                                document.querySelector(\'input[value="Submit"]\').click(); 
                                            }
                                            ''' \
                                Result=> Result

end

The final script will look like this:


Again, great job! :slight_smile:

Best regards,
J.


James Papadimitriou
Technical Evangelist

2 Likes

Very instructive for me, @jokoum, for many aspects of Robin. As @jpap said, especially the .ExecuteJavascript action, with proper use of quotes and escaping them.

Result below on my ancient Win7 machine:
Capture

:smiley:
Regards,
burque505

2 Likes

You can find the .robin file here.

Best regards,
J.


James Papadimitriou
Technical Evangelist

2 Likes

This is a Fantastic contribution!!! Congrats!!!

3 Likes

This is one of the best posts so far.
Thanks, @jokoum and @jpap!
Regards,
burque505

2 Likes

Can the same be done using UI spy, since every form field will have its own unique ID. I tried doing this using UI spy but whenever I select any form field like ‘FIRST_NAME’ etc, its giving me an error saying that the ‘form field with selector (id…) cannot be found’. Is this an alternate way of doing or is it possible to do it using javascript only? If possible using UI spy, can you send its code too?

@Yash_bitla99 indeed each element has its own unique id, but that id changes from iteration to iteration. Hence you cannot base on the id property of the element to effectively automate the challenge.

Please change the UISpy captured selectors using the ‘Custom’ option and enter a selector that is stable and never changes from iteration to iteration.

I have tested two potential selectors here for the ‘First Name’ input field:

input[ng-reflect-name="labelFirstName"]

label:contains("First Name") + input

Feel free to adjust the captured selectors, and update the community with your version :grinning: !

3 Likes

@jokoum Thank you for the help. I was able to do the ‘Input Forms’ challenge using UI Spy. Here is my code :

3 Likes

Nice work @Yash_bitla99!
Feel free to post it as a separate thread!
Keep up the great work! :slight_smile:

Best regards,
J.

3 Likes

@Yash_bitla99, very nice, thank you. Well done!
Regards,
burque505

2 Likes