Power Platform Community Forum Thread Details

I am working on a bot that will scan a website and extract lead information. The website has new leads added to it constantly. So the bot should scan the site for new numbers and message the ones that have not been contacted. I have managed to scan the website with the bot. It actually downloads the excel version of from the website so the new leads are in a dynamic excel sheet. But I am stuck at the part where the bot determines if any particular phone number in the New Leads excel sheet has already been contacted.. I know it SHOULD compare the current phone number against a list of phone numbers in a different excel sheet (the ones already contacted). If the current number is in the list, it does nothing. If the current number Is NOT on the list, It sends a message/email. The excel list will be constantly growing because at the end, after the message is sent, the bot should add the current phone number to the excel list to ensure it never gets contacted again. How can I have power automate compare the dunamic current website phone numbers against the dynamic excel list? I would like a detailed explanation with the specific set up in power automate desktop (conditionals, etc). If you provide me a solution that works I might venmo you $10

Categories:

Power Automate Desktop

Hope you meant two Workbooks and not Sheets.

These are the two files - one coming from the Website and one Local list that you are maintaining for previously sent emails.

After running the process it creates two lists

Below records were NOT found in the website list.

You can now simply use a "For each" and loop through each of the above Lists.

At the end these two lines of code will go and write the Not Found list to the local file.

Copy Paste the below code into a blank editor of Power Automate Desktop and run the flow and test.

Make sure to change the Excel path of both the files.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Compare Excel\\WebsiteList.xlsx''' Visible: True ReadOnly: False Instance=> WebExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: WebExcelInstance ReadAsText: True FirstLineIsHeader: True RangeValue=> WebExcelData
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Compare Excel\\LocalList.xlsx''' Visible: True ReadOnly: False Instance=> LocalExcelInstance
Variables.CreateNewList List=> NotFoundList
Variables.CreateNewList List=> FoundList
LOOP FOREACH CurrentItem IN WebExcelData
 Excel.FindAndReplace.FindAll Instance: LocalExcelInstance TextToFind: CurrentItem MatchCase: False MatchEntireCellContents: True SearchBy: Excel.SearchOrder.Rows Cells=> Cells
 IF Cells.RowsCount = 0 THEN
 Variables.AddItemToList Item: CurrentItem[0] List: NotFoundList NewList=> NotFoundList
 ELSE
 Variables.AddItemToList Item: CurrentItem[0] List: FoundList NewList=> FoundList
 END
END
Excel.GetFirstFreeColumnRow Instance: LocalExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.WriteToExcel.WriteCell Instance: LocalExcelInstance Value: NotFoundList Column: FirstFreeColumn - 1 Row: FirstFreeRow
# Modify below as needed
Excel.CloseExcel.Close Instance: WebExcelInstance
Excel.CloseExcel.CloseAndSave Instance: LocalExcelInstance

# [ControlRepository][PowerAutomateDesktop]

{
 "ControlRepositorySymbols": [],
 "ImageRepositorySymbol": {
 "Name": "imgrepo",
 "ImportMetadata": {},
 "Repository": "{\r\n \"Folders\": [],\r\n \"Images\": [],\r\n \"Version\": 1\r\n}"
 }
}