Value based iterative Web querying to append cumulative results

[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:

1. automate the  SharePoint list export to XLS with Web Flow (say, on each "add new item" overwrite initial file)
2.  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)
3. 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 🙂

[Anonymous]

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.

[MarioOne]

@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:

[Anonymous]

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.

[MarioOne]

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? 

[Anonymous]

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.

[MarioOne]

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.