Spaces:
Sleeping
Sleeping
| # 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) | |