Answered

Extracting to Spreadsheet from Web in a For Each Loop

(0) Share

Report

Posted on by QuestC

Hello All,

I have a loop that goes through different product numbers on Amazon's search and clicks on the first result of each one. Once it does this, I would like it to extract some data from the page and put it into a spreadsheet. I also want there to be a blank row if there is no search result for that product number.

I am having trouble writing data to the spreadsheet inside my loop. I am not able to get anything to write.

Is Amazon's website too inconsistent to write the same information from different products or am I doing something wrong?

My loop looks like this...

1. Launch Amazon.com in Chrome

2. Launch Excel spreadsheet with product numbers down the A column

3. Launch blank Excel (Instance 2)

4. Read Excel instance 1 for all Row A

5. For Each Loop: CurrentItem in ExcelData

6. Trim Text (to encrypt CurrentItem)

7. Populat text field on web page (Search bar with CurrentItem)

8. Press search button on page

9. Press first URL result

10. Extract data from web page (Into Variable) (I tried to specify it correctly but I believe that it may not be picking up the right info)

11. Write to Excel worksheet from DataFromWebPage to Excel Instance 2

12. Navigate back to Amazon.com

13. End For Each Loop

14. Done

Thanks for any help!!

Categories:

Power Automate Desktop

All responses (5)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Can you provide an example of which data from search result you want to retrieve?
QuestC 26 on at

Like
a
(0)

Report
Copy link

Link copied!

https://www.amazon.com/Vestil-FJB-150-Basement-Floor-Jack/dp/B0052PLEGM/ref=sr_1_1?dchild=1&keywords=vestil&qid=1625590929&sr=8-1&th=1

Sure, basically I want to know if
1. its buyable (if there is a buy now button)
2. the Ships From: (whoever) (right below buy now)
3. the Sold By: (whoever) (right below buy now)
QuestC 26 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous I have modified it a little bit so that it writes to a specific spot on the spreadsheet (Column A and Row %ExcelRow% variable) and I have ExcelRow increasing by 1 for each loop. But I am still not able to write any data to the spreadsheet. It always comes up empty at the end of the scan?
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!

This flow tested well:
1) For each CurrentItem in ExcelData
2) Set variable Ship as blank (%’’%)
3) Set variable Sold as blank (%’’%)
4) Trim text CurrentItem to TrimmedText
5) Write Trimmed Text to Excel worksheet in column = 1, row = ExcelRow
6) Populate Trimmed Text to amazon’s Search field
7) Press Search button
😎 Click in first Search result link
9) ‘If web page contains’: if doesn’t contain text ‘Temporarily out of stock’ then
10) ‘Write to excel worksheet’: ‘Buyable’ in column = 2, row = ExcelRow
11) ‘Else’
12) ‘Write to excel worksheet’: ‘Out of Stock’ in column = 2, row = ExcelRow
13) ‘End’ (of If statement)
14) ‘Get details of element on web page’, UI element = capture the info of ‘Ships from’ (here it’s span[Id="tabular-buybox-truncate-0"] > span[Class="a-truncate-cut"] > span[Class="tabular-buybox-text"]), attribute = Own text. Name result variable as %Ship%
15) ‘Get details of element on web page’, UI element = capture the info of ‘Sold by’ (here it’s span[Id="tabular-buybox-truncate-1"] > span[Class="a-truncate-cut"] > span[Class="tabular-buybox-text"]), attribute = Own text. Name result variable as %Sold%
16) ‘Write to excel worksheet’: write %Ship% in column = 3 row = ExcelRow
17) ‘Write to excel workheet’: write %Sold% in column = 4, row = ExcelRow
18) ‘Increase variable’ ExcelRow by 1
19) ‘End’ (of For each)
QuestC 26 on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous
Thanks so much man this works great! You dont know how much you saved me and I learned a lot!