# AUTOGENERATED! DO NOT EDIT! File to edit: slack_selenium_solution.ipynb. # %% auto 0 __all__ = ['iface', 'SlackAnalytics', 'GoogleSheets', 'run'] # %% slack_selenium_solution.ipynb 0 from selenium import webdriver from selenium.webdriver.chrome.service import Service as ChromeService from selenium.webdriver.chrome.options import Options from webdriver_manager.chrome import ChromeDriverManager from selenium.webdriver.common.by import By from selenium.webdriver import ActionChains import time import os import gspread import pandas as pd from pathlib import Path import gradio as gr # %% slack_selenium_solution.ipynb 1 class SlackAnalytics(): def __init__(self, signin_url = 'https://thriveprojectgroup.slack.com/sign_in_with_password', members_stas_url = 'https://thriveprojectgroup.slack.com/admin/stats#members', slackbot_url= 'https://app.slack.com/client/T01BD3PFRU4/D04GAGYT34Z'): self.signin_url = signin_url self.members_stas_url = members_stas_url self.slackbot_url = slackbot_url # self.path = Path.home()/'Downloads' self.path = Path('.') options = Options() options.add_argument('--incognito') options.add_argument("--headless") options.add_experimental_option("prefs", {"download.default_directory": str(self.path)}) service = ChromeService(ChromeDriverManager().install()) self.driver = webdriver.Chrome(options=options, service=service) self.driver.maximize_window() def export_csv(self, account, last_active=True, all_columns=False, all_time=True): # Login Slack with eamil and password email, password = open(account, 'r').read().splitlines() self.driver.get(url=self.signin_url) self.driver.implicitly_wait(30) # input email self.driver.find_element(By.CSS_SELECTOR, "input[data-qa='login_email']").send_keys(email) self.driver.find_element(By.CSS_SELECTOR, "input[data-qa='login_password']").send_keys(password) self.driver.find_element(By.CSS_SELECTOR, "button[data-qa='signin_button']").click() # Export csv file from Slack Analytics self.driver.get(url=self.members_stas_url) self.driver.find_element(By.CSS_SELECTOR, "button[data-qa='data_table_header_edit_columns-header-action']").click() # select column 'last active' if last_active: self.driver.find_element(By.CSS_SELECTOR, "input[data-qa='data_table_header__data_table_edit_columns_modal__checkbox_date_last_active']").click() # select all columns if all_columns: self.driver.find_element(By.CSS_SELECTOR, "input[data-qa='data_table_header__data_table_edit_columns_modal__checkbox_date_last_active']").click() self.driver.find_element(By.XPATH, '//button[contains(text(), "Close")]').click() # select all time if all_time: self.driver.find_element(By.CSS_SELECTOR, "div[id='data_table_header-filter_button-option']").click() self.driver.find_element(By.CSS_SELECTOR, "span[data-qa='all']").click() self.driver.find_element(By.CSS_SELECTOR, "button[data-qa='analytics_members_csv-header-action']").click() # download and delete the csv file self.driver.get(url=self.slackbot_url) time.sleep(10) # wait until the file is loaded self.driver.find_element(By.XPATH, '//span[contains(text(), "THRIVE Project Member Analytics All time")]').click() time.sleep(2) self.driver.find_element(By.CSS_SELECTOR, "button[data-qa='more_file_actions']").click() time.sleep(2) self.driver.find_element(By.XPATH, '//div[contains(text(), "Delete file")]').click() time.sleep(2) self.driver.find_element(By.XPATH, '//button[contains(text(), "Yes, Delete This File")]').click() # Close the browser windows and ends the WebDriver session time.sleep(2) self.driver.quit() # %% slack_selenium_solution.ipynb 2 class GoogleSheets(): def __init__(self, creds_file='slack-analytics-creds.json', sh_file='gs_info.txt'): self.creds = creds_file self.sh = sh_file # self.path = Path.home()/'Downloads' self.path = Path('.') def get_sheet_info(self): sh_id, wk_name = open(self.sh, 'r').read().splitlines() return sh_id, wk_name def update_worksheet(self): # Setup service account sh_id, wk_name = self.get_sheet_info() gc = gspread.service_account(filename=self.creds) # Open a sheet from a spreadsheet wk = gc.open_by_key(sh_id).worksheet(wk_name) # Clear the worksheet wk.clear() # Read and remove the downoaded csv file and update the data to Google Sheets for fn in os.listdir(self.path): if fn.find('THRIVE Project Member Analytics All time') != -1: file = os.path.join(self.path, fn) df = pd.read_csv(file, low_memory=False) df = df.fillna('') os.remove(file) # update the new data to Google Sheets wk.update([df.columns.values.tolist()] + df.values.tolist()) return df[['Name', 'Messages posted', 'Last active (UTC)']].head() # %% slack_selenium_solution.ipynb 6 def run(account, creds_file, sh_file): SlackAnalytics().export_csv(account.name) df_sub = GoogleSheets(creds_file.name, sh_file.name).update_worksheet() return ('Slack Analytics data update Google Sheets completed!', df_sub) # %% slack_selenium_solution.ipynb 7 iface = gr.Interface(fn=run, inputs=[gr.File(label='Slack Account File'), gr.File(label='Google Credentials Json File'), gr.File(label='Googlesheet Key File')], outputs=[gr.Text(), gr.DataFrame(headers=['Name', 'Messages posted', 'Last active (UTC)'], datatype=['str', 'number', 'str']) ], allow_flagging='never', title='Slack Analytics Members Data Getter', description='Download Slack analytics data From Slack and upload it to Google Sheets') iface.launch(height=450, width=500)