[RPA] A story about automating login to Office Station and retrieving official documents
Introduction
This is another story about PAD (Power Automate Desktop).
Following the previous RPA automation for Kitera uploads, this time I have automated the login to Office Station and the batch retrieval of official documents.
RPA again
When I first started this note, I intended to write about VBA and Python.
Although I had been interested in RPA for years, I never had much success with it. For some reason, after I started posting on note, I began to succeed (thanks to AI), and now my posts are all about RPA. I will try to post about things other than RPA eventually.
This time, it is about logging into Office Station and retrieving official documents. Every time I log into Office Station, in addition to entering my login ID and password, there is a random number table authentication. This is a bit of a hassle... Every time I log in, I have to open the random number table in Excel, find the number at the specified coordinates, and enter it.
For retrieving official documents, I currently perform the tasks of navigating to the form screen, determining the procedure status (under review, review completed, procedure completed), checking the checkbox, and performing a batch download manually.
At the firm where I worked a few years ago, we purchased a pre-developed RPA to automate this task. Back then, AI wasn't used as much, so I couldn't have done it on my own, but I really wanted to build it myself... Since I held onto that feeling, I want to tell my past self about this success.
This time, I managed to complete it while getting caught in the traps of a triple-display environment, so I will introduce the flow.
Overview of the flow
This is broadly composed of two phases.
Login phase: Launch browser, enter ID/password, enter random number table
Official document retrieval phase: Check the target cases and output in a batch
Flow details
[1. Login phase ①] Launch browser to login
| 1 | Launch new browser
| 2 | Move window to main display (coordinates 0,0)
| 3 | Maximize window with keystroke (Win+↑)
| 4 | Enter login ID
| 5 | Enter password
| 6 | Click login button
Traps of a triple-display environment
I work from home and use a triple-display setup. In that environment, UI elements were sometimes not recognized correctly if the browser launched on a display other than the main one. I solved this by using the 'Move window' action to force it to coordinates (0,0) before operating. This seems useful for the future.
Also, since the window becomes small after moving, I maximize it by sending the `{LWin}{Up}` keystroke.
[1. Login phase ②] Random number table authentication phase
After logging in, the random number table authentication screen appears. Since two different coordinates (alphabet and number) are specified each time, I need to enter the corresponding numerical value. I created the random number table in Excel, loaded it, and used a method to enter the value that matches the displayed coordinates.
| 1 | Launch Excel (background)
| 2 | Store all worksheet data in variables
| 3 | Close Excel
| 4 | Get the first coordinate label on the screen
| 5 | Split into columns and rows by getting subtext
| 6 | Get the corresponding cell value from Excel
| 7 | Enter the value into the first input field
| 8 | Process the second coordinate in the same way
| 9 | Click the 'Next' button
Point: Making selectors dynamic
Since the random coordinates change every time, if you leave the UI elements captured as they are, they will be recognized as fixed values (e.g., 'B5'), which will cause errors in subsequent runs. By changing the condition at the end in the selector text editor to 'starts with', I was able to handle the coordinates that change every time. I think this is probably the kind of thing that trips up non-engineers like me.
[Handling pop-ups] Closing notifications
After logging in, a 'Notification' pop-up may appear. You know those 'notifications' that pop up occasionally in most software, right?
Since they don't always appear, I use the 'If web page contains' action to check for the existence of the 'Close' button and use conditional branching to click it only if it exists.
[Official document retrieval phase] Checking target cases and batch output
| 1 | Get the entire table using 'Extract data from web page'
| 2 | Initialize counter variable (Count) to 0
| 3 | Initialize row index variable (RowIndex) to 1
| 4 | Loop through all rows with 'For each'
| 5 | Check the checkbox for rows where the 'Status' column contains 'Finished'
| 6 | Increment Count by 1 and RowIndex by 1
| 7 | After the loop, if Count=0, display a 'No targets' message and finish
| 8 | Click the 'Batch output official documents/comments' button
Since 'Procedure Finished', 'Procedure Finished (Returned)', and 'Review Finished' all contain 'Finished', I handle them all together with one condition. 'Under Review' does not meet this criteria, so it is skipped.
Point: Dynamic specification of checkboxes
To dynamically specify the checkbox for each row, I replaced the row number part of the selector (the '1' in 'dataitem:eq(1)') with the RowIndex variable. This allows the target row to change automatically with each loop.
Using different browsers
I tried both Chrome and Edge for launching the web browser, and there were differences in how UI elements were retrieved.
Chrome: OK with UI automation 'Get details of UI element in window'
Edge: Must use browser automation 'Get details of element on web page' to be recognized
It was a bit of a stumbling block that the behavior changed depending on the browser even for the same operation.
Summary of common pitfalls
■ Problem where UI elements are not recognized
(Cause) Browser sometimes launches on a display other than the main display in a triple-display setup
(Solution) Add window move (X:0, Y:0)
■ Problem where random coordinates are recognized as fixed values
(Cause) Selector is fixed to a specific value
(Solution) Change selector to 'starts with *'
■ UI elements not recognized due to window launch state
(Cause) Window state is reset by moving
(Solution) Maximize with Send Keys (Win+↑)
Summary
Following the previous Kitera automation, I was able to successfully automate Office Station as well!
Dealing with the unique mechanism of random coordinate table authentication and making the selectors for UI elements that change every time dynamic were the keys to this automation. I had no idea what the cause of the fixed selector value problem was at first, but I was able to solve it by asking Claude repeatedly. I feel like I've gotten a little used to it while working through it, even if I had to ask for help.
Reducing daily login tasks to a single button press may seem minor, but it adds up to significant time savings. I am planning to write separate posts about unzipping the official documents retrieved from Office Station and the IE mode issues.
I hope to continue finding and taking on the challenge of automating tasks with RPA. I hope this serves as a reference for those working in similar environments.