Bring a specified window of an app to the front (if there are more than one window)

OK, I was finally able to reproduce your behavior.
I have revised the complete script here:
SCRIPT: Activate Google Chrome Tab for Specified URL

But it is just adding one command here;

  if not found then
    make new tab at window 1 with properties {URL:targetURL}
  end if
  
  activate # <--- ADD THIS
  
end tell

Let me know if this fixes the issue for you.