check_accounts.py

# 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()