cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarioOne
Frequent Visitor

Value based iterative Web querying to append cumulative results

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:

 

MarioOne_0-1634157723667.png

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 🙂

6 REPLIES 6
shindomo
Responsive Resident
Responsive Resident

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".

 

shindomo_0-1634160333505.png

 

Thank you.

@shindomo thanks for that tip! Write to Excel worksheet worked out! 🙂

Checked that and now the flow executes adding a desired text to xls cell.

MarioOne_1-1634231867989.png

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.

MarioOne_0-1634231481677.png

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:

MarioOne_0-1634243807143.png

 

shindomo
Responsive Resident
Responsive Resident

Hello @MarioOne 

 

Here is my sample implementation for part of your requirement.


Screenshot of entire flow:

shindomo_8-1634255435930.png

shindomo_9-1634255444775.png

 

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.

 

shindomo_5-1634253466707.png

 

If you have any questions, please reply. 🙂

Thank you.

MarioOne
Frequent Visitor

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? 

shindomo
Responsive Resident
Responsive Resident

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:

 

shindomo_0-1633319938351.png

 

If you have enough time to learn, there are lots of tutorial videos in YouTube.

Power Automate Desktop Web Form - Bing video

 

Thank you.

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.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,281)