Value based iterative Web querying to append cumulative results

(0) Share

Report

Posted on by MarioOne

Howdy Folks!

I am looking for the solution to collect updates on PC guarantee.

Ideally I would prefer to have a web automated flows that starts on add new item to the list.

This however seem to be available only with premium connector.

The way I think of it is:

automate the SharePoint list export to XLS with Web Flow (say, on each "add new item" overwrite initial file)
Use manual trigger from PowerAutomate Desktop to:
1. Open XLS File
2. Read Serial Number
3. Open a WebPage to query SN
4. Copy the result and paste to XLS record
5. Query Page again for the next item in the column
6. Repeat until the last item (only if information is missing)
Append XLS values to the source SharePoint List on file change

I am not able to execute the iteration which means I have stucked on step 4 as depicted here:

Will you be able to suggest how to overcome this step? Those loops I will be using on other small processes therefore the iterations are key for me manage properly. Happy with your help 🙂

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @MarioOne

It seems like there is nothing copied in your clipboard yet. This is why the paste action failed.
You should use action "Write to Excel worksheet" rather than "Paste cells to Excel worksheet".

Thank you.

Was this reply helpful? Yes No
MarioOne 67 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous thanks for that tip! Write to Excel worksheet worked out! 🙂
Checked that and now the flow executes adding a desired text to xls cell.
I have added a step to push the button to reset the search window.
The logic fails after first item added (Warranty) which must be my lack of expertise.
I am also affraid, that the data I have captured from the Webpage was the text selected - which will not automatically match with next result value.
Will you be so kind help me out understand on how to build the "on each" logic the way it checks and adds on the next item from Column A and fulfill correctly column E?

This is my High-Level process I am trying to apply:

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @MarioOne

Here is my sample implementation for part of your requirement.

Screenshot of entire flow:

Code (including UI element without captured images):

System.TerminateProcessByName ProcessName: $'''EXCEL''' Excel.LaunchAndOpen Path: $'''C:\\Users\\User01\\Documents\\HP Check Warranty.xlsx''' Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance Excel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData WebAutomation.LaunchEdge Url: $'''about:blank''' WindowState: WebAutomation.BrowserWindowState.Normal ClearCache: False ClearCookies: False Timeout: 60 BrowserInstance=> Browser LOOP LoopIndex FROM 0 TO ExcelData.RowsCount - 1 STEP 1 IF IsEmpty(ExcelData[LoopIndex]['Warranty']) THEN WebAutomation.GoToWebPage BrowserInstance: Browser Url: $'''https://support.hp.com/us-en/checkwarranty''' WebAutomation.FormFilling.PopulateTextField BrowserInstance: Browser Control: appmask['HP Support Web Page']['Textbox - Serial Number'] Text: ExcelData[LoopIndex]['Serial'] EmulateTyping: True UnfocusAfterPopulate: False Mode: WebAutomation.PopulateTextMode.Replace WebAutomation.FormFilling.PressButton BrowserInstance: Browser Control: appmask['HP Support Web Page']['Button - Check Warranty'] WebAutomation.DataExtraction.ExtractHandPickedValues BrowserInstance: Browser ExtractionParameters: { ^['Css Selector', 'Attribute', 'Regex', 'Column Name'], ['html > body > div:eq(2) > div:eq(0) > portlet\\:defineobjects > div:eq(3) > div > div:eq(2) > div > div:eq(2) > div > div > div:eq(0) > section > div:eq(1) > section > div > div:eq(1) > h2', 'Own Text', '', 'Model'], ['html > body > div:eq(2) > div:eq(0) > portlet\\:defineobjects > div:eq(3) > div > div:eq(2) > div > div:eq(2) > div > div > div:eq(1) > section > div:eq(1) > section > div:eq(3) > div:eq(0) > div:eq(3) > div:eq(1) > div > div > div:eq(0) > div:eq(0) > div:eq(4) > div:eq(1)', 'Own Text', '', 'EndDate'] } ExtractedData=> OutputData Excel.WriteCell Instance: ExcelInstance Value: OutputData[0]['Model'] Column: 2 Row: LoopIndex + 2 Excel.WriteCell Instance: ExcelInstance Value: OutputData[0]['EndDate'] Column: 5 Row: LoopIndex + 2 END END WebAutomation.CloseWebBrowser BrowserInstance: Browser Excel.CloseAndSave Instance: ExcelInstance # [ControlRepository][PowerAutomateDesktop] { "ApplicationInfo": { "Name": "ClipboardControlRepository", "Version": "1.0" }, "Screens": [ { "Controls": [ { "AutomationProtocol": null, "ScreenShot": null, "ElementTypeName": "input", "InstanceId": "a33a8e68-4818-41e3-b7e6-7a5f6eb199c3", "Name": "Textbox - Serial Number", "SelectorCount": 1, "Selectors": [ { "CustomSelector": "html > body > div:eq(2) > div:eq(0) > portlet\\:defineobjects > div:eq(3) > div > div:eq(2) > div > div:eq(1) > div:eq(2) > div > div > div:eq(0) > div > div > div > section > div:eq(1) > div:eq(1) > div:eq(1) > input", "Elements": [], "Ignore": false, "IsCustom": true, "IsWindowsInstance": false, "Order": 0 } ], "Tag": "input" }, { "AutomationProtocol": null, "ScreenShot": null, "ElementTypeName": "input", "InstanceId": "18bb1a78-0223-41e8-acb7-0a4b8e9412b6", "Name": "Button - Check Warranty", "SelectorCount": 1, "Selectors": [ { "CustomSelector": "html > body > div:eq(2) > div:eq(0) > portlet\\:defineobjects > div:eq(3) > div > div:eq(2) > div > div:eq(1) > div:eq(2) > div > div > div:eq(0) > div > div > div > section > div:eq(1) > div:eq(1) > div:eq(5) > input", "Elements": [], "Ignore": false, "IsCustom": true, "IsWindowsInstance": false, "Order": 0 } ], "Tag": "input" } ], "ScreenShot": null, "ElementTypeName": "Web Page", "InstanceId": "2de2242e-5e3b-4ce4-bd26-a99536b580ed", "Name": "HP Support Web Page", "SelectorCount": 1, "Selectors": [ { "CustomSelector": null, "Elements": [ { "Attributes": [], "CustomValue": null, "Ignore": false, "Name": "Web Page", "Tag": "domcontainer" } ], "Ignore": false, "IsCustom": false, "IsWindowsInstance": false, "Order": 0 } ], "Tag": "domcontainer" } ], "Version": 1 }

You can copy & paste the code above into your Flow Designer of PAD app to look around the detailed structure.

I experimented with writing text extracted from a Web page into an Excel cell, and I've verified that a date string in the format "October 15, 2021" is converted to date-type (serial value) data when being entered into the cell.

If you have any questions, please reply. 🙂
Thank you.

Was this reply helpful? Yes No
MarioOne 67 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for reply and appreciate all your effort. I am quite new to PAD and having lots of questions including those simple one such as "where to paste the code" 😉 so I tried to follow step by step, but then I can not manage the blue values in "bubbles" as lacking understanding of the semantics here. Will be digging further on it. BTW any tip where to start learning the value entry logic?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @MarioOne

You would copy the code from my previous post and paste it to the red rectangle area of Flow Designer window as below:

If you have enough time to learn, there are lots of tutorial videos in YouTube.
Power Automate Desktop Web Form - Bing video

Thank you.

Was this reply helpful? Yes No
MarioOne 67 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, I am trying to apply those videos but with little luck.
For some reason I can not paste the code. Still, thanks for replies.

Was this reply helpful? Yes No