Created
January 29, 2022 14:42
-
-
Save JeffreyKozik/0d71714e8a926f680f51d82bc07ea4df to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# selenium dependencies | |
from selenium import webdriver | |
from webdriver_manager.chrome import ChromeDriverManager | |
from selenium.webdriver.support.ui import WebDriverWait | |
from selenium.webdriver.support import expected_conditions as EC | |
from selenium.webdriver.common.by import By | |
# excel dependencies | |
import openpyxl | |
from pathlib import Path | |
from os.path import exists | |
# gui dependencies | |
import tkinter as tk | |
# https://realpython.com/python-gui-tkinter/ | |
window = tk.Tk() | |
file_address_label = tk.Label(text="Excel File") | |
file_address_entry = tk.Entry() | |
file_address_label.pack() | |
file_address_entry.pack() | |
email_starting_row_label = tk.Label(text="Email Starting Row") | |
email_starting_row_entry = tk.Entry() | |
email_starting_row_label.pack() | |
email_starting_row_entry.pack() | |
email_ending_row_label = tk.Label(text="Email Ending Row") | |
email_ending_row_entry = tk.Entry() | |
email_ending_row_label.pack() | |
email_ending_row_entry.pack() | |
email_column_label = tk.Label(text="Email Column") | |
email_column_entry = tk.Entry() | |
email_column_label.pack() | |
email_column_entry.pack() | |
account_exists_1_starting_row_label = tk.Label(text="Account Exists 1 Starting Row") | |
account_exists_1_starting_row_entry = tk.Entry() | |
account_exists_1_starting_row_label.pack() | |
account_exists_1_starting_row_entry.pack() | |
account_exists_1_ending_row_label = tk.Label(text="Account Exists 1 Ending Row") | |
account_exists_1_ending_row_entry = tk.Entry() | |
account_exists_1_ending_row_label.pack() | |
account_exists_1_ending_row_entry.pack() | |
account_exists_1_column_label = tk.Label(text="Account Exists 1 Column") | |
account_exists_1_column_entry = tk.Entry() | |
account_exists_1_column_label.pack() | |
account_exists_1_column_entry.pack() | |
account_exists_2_starting_row_label = tk.Label(text="Account Exists 2 Starting Row") | |
account_exists_2_starting_row_entry = tk.Entry() | |
account_exists_2_starting_row_label.pack() | |
account_exists_2_starting_row_entry.pack() | |
account_exists_2_ending_row_label = tk.Label(text="Account Exists 2 Ending Row") | |
account_exists_2_ending_row_entry = tk.Entry() | |
account_exists_2_ending_row_label.pack() | |
account_exists_2_ending_row_entry.pack() | |
account_exists_2_column_label = tk.Label(text="Account Exists 2 Column") | |
account_exists_2_column_entry = tk.Entry() | |
account_exists_2_column_label.pack() | |
account_exists_2_column_entry.pack() | |
def handle_click(event): | |
xlsx_file = file_address_entry.get() | |
starting_input_row = email_starting_row_entry.get() | |
ending_input_row = email_ending_row_entry.get() | |
input_column = email_column_entry.get() | |
starting_output1_row = account_exists_1_starting_row_entry.get() | |
ending_output1_row = account_exists_1_ending_row_entry.get() | |
output1_column = account_exists_1_column_entry.get() | |
starting_output2_row = account_exists_2_starting_row_entry.get() | |
ending_output2_row = account_exists_2_ending_row_entry.get() | |
output2_column = account_exists_2_column_entry.get() | |
defaults = [] | |
if (exists("check_accounts_saved_defaults.txt")): | |
with open('check_accounts_saved_defaults.txt') as f: | |
defaults = f.readlines() | |
i = 0 | |
while (i < len(defaults)): | |
defaults[i] = defaults[i][:-1] | |
print(defaults[i]) | |
i+=1 | |
def set_to_default(variable, num): | |
if variable == "": | |
return defaults[num] | |
else: | |
return variable | |
xlsx_file = set_to_default(xlsx_file, 0) | |
starting_input_row = set_to_default(starting_input_row, 1) | |
ending_input_row = set_to_default(ending_input_row, 2) | |
input_column = set_to_default(input_column, 3) | |
starting_output1_row = set_to_default(starting_output1_row, 4) | |
ending_output1_row = set_to_default(ending_output1_row, 5) | |
output1_column = set_to_default(output1_column, 6) | |
starting_output2_row = set_to_default(starting_output2_row, 7) | |
ending_output2_row = set_to_default(ending_output2_row, 8) | |
output2_column = set_to_default(output2_column, 9) | |
starting_input_row = int(starting_input_row) | |
ending_input_row = int(ending_input_row) | |
starting_output1_row = int(starting_output1_row) | |
ending_output1_row = int(ending_output1_row) | |
starting_output2_row = int(starting_output2_row) | |
ending_output2_row = int(ending_output2_row) | |
f = open("check_accounts_saved_defaults.txt", "w", encoding='utf-8') | |
f.write(xlsx_file + "\n") | |
f.write(str(starting_input_row) + "\n") | |
f.write(str(ending_input_row) + "\n") | |
f.write(input_column + "\n") | |
f.write(str(starting_output1_row) + "\n") | |
f.write(str(ending_output1_row) + "\n") | |
f.write(output1_column + "\n") | |
f.write(str(starting_output2_row) + "\n") | |
f.write(str(ending_output2_row) + "\n") | |
f.write(output2_column + "\n") | |
f.close() | |
# https://www.marsja.se/your-guide-to-reading-excel-xlsx-files-in-python/ | |
workbook_object = openpyxl.load_workbook(xlsx_file) | |
sheet = workbook_object.active | |
current_row = starting_input_row | |
emails = [] | |
while current_row <= ending_input_row: | |
current_cell = input_column + str(current_row) | |
current_email = sheet[current_cell].value | |
emails.append(current_email) | |
current_row += 1 | |
url1 = "https://nhsscot.service-now.com" | |
url2 = "https://nhsnss.service-now.com" | |
accounts_exist1 = [] | |
accounts_exist2 = [] | |
driver = webdriver.Chrome(ChromeDriverManager().install()) | |
waiting_time = 1000 | |
def check_exists(url, email, accounts_exist): | |
driver.get(url + "/$pwd_reset.do?sysparm_url=ss_default") | |
username_input = WebDriverWait(driver, waiting_time).until( | |
EC.element_to_be_clickable((By.ID, "sysparm_user_id_0"))) | |
username_input.send_keys(email) | |
next_button = WebDriverWait(driver, waiting_time).until( | |
EC.element_to_be_clickable((By.ID, "sysverb_pwd_reset"))) | |
next_button.click() | |
account_exists_url = url + "/$pwd_verify.do" | |
account_notexists_url = url + "/$pwd_error.do" | |
# https://stackoverflow.com/questions/36316465/what-is-the-best-way-to-check-url-change-with-selenium-in-python | |
WebDriverWait(driver, waiting_time).until( | |
lambda driver: (driver.current_url == account_exists_url or driver.current_url == account_notexists_url) | |
) | |
if (driver.current_url == account_exists_url): | |
accounts_exist.append("Y") | |
elif (driver.current_url == account_notexists_url): | |
accounts_exist.append("N") | |
for email in emails: | |
check_exists(url1, email, accounts_exist1) | |
check_exists(url2, email, accounts_exist2) | |
workbook_object = openpyxl.load_workbook(xlsx_file) | |
sheet = workbook_object.active | |
current_row = starting_output1_row | |
count = 0 | |
while current_row <= ending_output1_row: | |
current_cell = output1_column + str(current_row) | |
sheet[current_cell].value = accounts_exist1[count] | |
count += 1 | |
current_row += 1 | |
current_row = starting_output2_row | |
count = 0 | |
while current_row <= ending_output2_row: | |
current_cell = output2_column + str(current_row) | |
sheet[current_cell] = accounts_exist2[count] | |
count += 1 | |
current_row += 1 | |
# https://www.geeksforgeeks.org/change-value-in-excel-using-python/ | |
workbook_object.save(filename=xlsx_file) | |
driver.quit() | |
quit() | |
button = tk.Button(text="Determine Which Accounts Exist", bg="black", fg="white") | |
button.bind("<Button-1>", handle_click) | |
button.pack() | |
window.mainloop() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment