async def fetch(channel: TextChannel): try: url = GameData.__get_url(channel) except BaseException as exception: await handle(None, exception) raise UsageException.directive_missing(channel, "games") try: sheets_fetcher = Sheets.from_files( "client_secrets.json", "oath_cache.json", ) sheets = sheets_fetcher.get(url) games: List[Game] = [] # Must be the first sheet # Skip headers for row in sheets._sheets[0]._values[1:]: try: games.append( Game( datetime.strptime(row[4], "%m/%d/%Y"), Team(json.loads(row[0]), row[1]), Team(json.loads(row[2]), row[3]), )) except BaseException as exception: await handle(None, exception) return GameData(games) except BaseException as exception: await handle(None, exception) raise UsageException.game_sheet_not_loaded(channel, url)
def get_tracked_assets_and_contacts(): sheets = Sheets.from_files("credentials.json") sheet = sheets[SHEET_NAME] thresholds = sheet.sheets[0] contacts = sheet.sheets[1] raw_thresholds = thresholds.values(False)[1:] thresholds = list() for thr in raw_thresholds: try: thresholds.append(AlertObject(*thr)) except TypeError: logger.warning("A threshold row had insufficient information") pass raw_contacts = contacts.values(False)[1:] contacts = list() for cont in raw_contacts: try: contacts.append(Contact(*cont)) except TypeError: logger.warning("A contact row had insufficient information") pass return thresholds, contacts
async def fetch(id: str, channel: TextChannel) -> List[Game]: url = f"https://docs.google.com/spreadsheets/d/{id}" try: sheets_fetcher = Sheets.from_files( "client_secrets.json", "oath_cache.json", ) sheets = sheets_fetcher.get(url) games: List[Game] = [] # Must be the first sheet # Skip headers for row in sheets._sheets[0]._values[1:]: try: games.append( Game( datetime.strptime(row[4], "%m/%d/%Y"), Team(json.loads(row[0]), row[1]), Team(json.loads(row[2]), row[3]), )) except BaseException as exception: await handle(None, exception) return games except BaseException as exception: await handle(None, exception) raise UsageException.game_sheet_not_loaded(channel, url)
def download_and_update_from_google_sheets(): sheets = Sheets.from_files('~/client_secrets.json', '~/storage.json') url = 'https://docs.google.com/spreadsheets/d/idnumberhere' s = sheets.get(url) s.sheets[2].to_csv( 'C:/Users/BReyes/Desktop/Bryans_Folder/Game/Ad Revenue/AdDailyRevenue2019.csv', encoding='utf-8', dialect='excel') xl_app = win32com.client.DispatchEx("Excel.Application") wb = xl_app.workbooks.open( 'C:/Users/BReyes/Desktop/Bryans_Folder/Analytics/Flash Reports/Current/Neopets Flash Report.xlsx' ) wb.RefreshAll() ws = wb.Worksheets[0] ws.Visible = 1 print_area = 'A1:K42' ws.PageSetup.Zoom = False ws.PageSetup.FitToPagesTall = 1 ws.PageSetup.FitToPagesWide = 1 ws.PageSetup.PrintArea = print_area time.sleep(15) wb.Save() ws.ExportAsFixedFormat( 0, 'C:/Users/BReyes/Desktop/Bryans_Folder/Analytics/Flash Reports/Current/Flash Report.pdf' ) xl_app.Quit() print( 'Google sheets file downloaded and saved! Flash report has been updated.' )
def get_sheet(): sheet = Sheets.from_files(secrets=PATH + '/client_secret-sheet.json', storage=PATH + '/storage.json') url = 'https://docs.google.com/spreadsheets/d/1oS7en09vBpXiwJjs9zto16US4tfU_bLxdy5EHk32Rsc/edit' # url = 'https://docs.google.com/document/d/1YBB75VQWERNKhvT-A9vUh0I-r_ZLPukAnVeXnj2e5SU/edit' s = sheet.get(url) sheet_list = [] for i in s: sheet_list.append(i) print(sheet_list[0]) sheet_list[0].to_csv(PATH + '/app_config_sicker2.csv')
def fetch_sheet_data(token_file, spreadsheet_id): """ We fetch the google sheets given spreadsheet id and sheet id and then dump it into csv https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheet_id Enable google drive API and Google Sheets API in google developer console. Check these APIs in OAuth consent screen """ sheets_object = Sheets.from_files(token_file, "storages.json") sheets = sheets_object[spreadsheet_id] return sheets
def sync(self): sheets = Sheets.from_files(self._config["gsheets_secrets"], self._config["gsheets_storage"]) s = sheets.get(self._config["url"]) path = os.path.abspath(self._config["path"]) for f in glob.glob(os.path.join(path, "*.csv")): os.remove(f) s.to_csv(make_filename=os.path.join(path, "%(sheet)s.csv")) return [os.path.join(path, f"{i}.csv") for i in s.sheets.titles()]
def update( samples, samplesetname, stype, bucket, refworkspace, name_col="index", values=['legacy_bam_filepath', 'legacy_bai_filepath'], filetypes=['bam', 'bai'], my_id='~/.client_secret.json', mystorage_id="~/.storage.json", creds='../.credentials.json', sampletrackername='ccle sample tracker', refsheet_url="https://docs.google.com/spreadsheets/d/1Pgb5fIClGnErEqzxpU7qqX6ULpGTDjvzWwDN8XUJKIY", ): # uploading to our bucket (now a new function) terra.changeToBucket(samples, bucket, name_col=name_col, values=values, filetypes=filetypes, catchdup=True, test=False) samplesetname sheets = Sheets.from_files(my_id, mystorage_id) ccle_refsamples = sheets.get(refsheet_url).sheets[0].to_frame(index_col=0) names = [] subccle_refsamples = ccle_refsamples[ccle_refsamples['datatype'] == stype] for k, val in samples.iterrows(): val = val["arxspan_id"] names.append(val) samples.loc[k, 'version'] = len(subccle_refsamples[ subccle_refsamples['arxspan_id'] == val]) + names.count(val) samples['version'] = samples['version'].astype(int) ccle_refsamples = ccle_refsamples.append(samples, sort=False) dfToSheet(ccle_refsamples, sampletrackername, secret=creds) #uploading new samples to mut refwm = dm.WorkspaceManager(refworkspace).disable_hound() refwm.upload_samples(samples) sam = refwm.get_samples() #creating a sample set refwm.update_sample_set(sample_set_id=samplesetname, sample_ids=samples.index) refwm.update_sample_set( sample_set_id='all', sample_ids=[i for i in sam.index.tolist() if i != 'nan'])
def pull_from_sheets(url, new_ratings_path): sheets = Sheets.from_files('~/client_secrets.json', '~/storage.json') s = sheets.get(url) p = s.sheets[0] df = p.to_frame() last_row = df.iloc[-1] new_df = pd.DataFrame(columns=['username', 'recipe_id', 'rating']) username = last_row['username'] for i, col in enumerate(df.columns): if col[1] == '[': new_df.loc[i] = [username, col[2:-1], last_row[col]] new_df.to_csv(new_ratings_path) # encoding='utf-8', dialect='excel') return 1
def download_csv(): # method to fetch and download spreadsheet # get sheets using credentials sheets = Sheets.from_files('~\Desktop\OFB_Data\client_secrets.json', '~\Desktop\OFB_Data\storage.json') # set url to url of spreadsheet that contains survey entries url = 'https://docs.google.com/spreadsheets/d/1TgNM_HA6y2IMD6vEjxNqFzaUmfRtTMqsiHM7jK-16Kc/edit?usp=sharing' # get sheet s = sheets.get(url) # dump first sheet in spreadsheet into csv file s.sheets[0].to_csv('ofb_data.csv', encoding='utf-8', dialect='excel')
def compareToCuratedGS(url, sample, samplesetname, sample_id='DepMap ID', clientsecret='~/.client_secret.json', storagepath='~/.storage.json', colname='CN New to internal', value='no data yet'): """ from a google spreadsheet, will check that we have all of the samples we should have in our sample set name (will parse NAME_additional for sample_id) Args: ----- url: str the url of the gsheet sample: list(str) the samples to check samplesetname: str the name of the sampleset in the googlesheet sample_id: str the name of the sample_id column in the google sheet clientsecret: str path to your secret google api account file storagepath: str path to your secret google api storage file colname: str if we need not to include some rows from the spreadsheet that have the value value value: str the value for which not to include the rows @gmiller """ sheets = Sheets.from_files(clientsecret, storagepath) # Cell Line Profiling Status google sheet gsheet = sheets.get(url).sheets[0].to_frame() gsheet.index = gsheet[sample_id] new_cn = gsheet[gsheet[colname] == samplesetname + 'tent'] if colname and value: data_not_ready_cn = gsheet[gsheet[colname] == value] print(data_not_ready_cn) # these are the "new" samples discovered by our function, createDatasetsFromNewCellLines sample_ids = [id.split('_')[0] for id in sample] print("We found data for " + str(len(sorted(sample))) + " samples.\n") print( "Sanity check: Since we have the tacked on number, we should only have 1 each per sample ID:\n" ) Counter(sample) in_sheet_not_found = set(new_cn.index.tolist()) - set(sample_ids) if len(in_sheet_not_found) > 0: print("We have not found " + str(len(in_sheet_not_found)) + " of the samples we're supposed to \ have this release:\n" + str(sorted(list(in_sheet_not_found)))) else: print( "We aren't missing any samples that we're supposed to have this release!" )
def get_saco(): ''' SACO is recorded in a separate Google Sheet as of 201905 ''' sheets = Sheets.from_files('./client_secret.json', './storage.json') fileId = saco_sheet url = 'https://docs.google.com/spreadsheets/d/' + fileId s = sheets.get(url) sheet_index = int(thisrun[-2:]) - 1 # sheet index should equal month s.sheets[sheet_index].to_csv(sacocsv, encoding='utf-8', dialect='excel') msg = 'SACO Google Sheet for %s saved to csv' % thisrun print(msg) logging.info(msg)
def get_expected_lines(sheets_url, merge_ibm_dmc=True): sheets_obj = Sheets.from_files('~/.client_secret.json', '~/.storage.json') sheets = sheets_obj.get(sheets_url).sheets release = sheets[0].to_frame(header=0, index_col=None) release.columns = release.columns.str.lower() if merge_ibm_dmc: cols = release.columns ibm = pd.DataFrame(set(release[['dmc', 'ibm']].stack().values), columns=['ibm']) # ibm = pd.concat([release['dmc'], release['ibm']] # ).dropna().drop_duplicates().to_frame('ibm') release = pd.concat([release[['internal', 'dmc', 'public']], ibm], axis=1) lines_to_release = release[cols] return release
def UpdateTables(): global GoogleDriveWorkbookName global ClassesWorksheetName global RacesWorksheetName global StatPriorityWorksheetName global PointBuyOptionsWorksheetName global StatBonusesWorksheetName global CRtoXPWorksheetName global LevelToXPWorksheetName global ClassTable global RaceTable global StatPriorityTable global PointBuyOptionsTable global StatBonusTable global CRtoXPTable global LevelToXPTable sheets = Sheets.from_files("./credentials.json", "./storage.json") book = sheets.find(GoogleDriveWorkbookName) # Classes table classSheet = book.find(ClassesWorksheetName) PopulateTable(classSheet, ClassTable) # Race table raceSheet = book.find(RacesWorksheetName) PopulateTable(raceSheet, RaceTable) # Stat priority table spSheet = book.find(StatPriorityWorksheetName) PopulateTable(spSheet, StatPriorityTable) # Point-buy options table pbSheet = book.find(PointBuyOptionsWorksheetName) PopulateTable(pbSheet, PointBuyOptionsTable) # Stat bonuses table sbSheet = book.find(StatBonusesWorksheetName) PopulateTable(sbSheet, StatBonusTable) # CR to XP table crSheet = book.find(CRtoXPWorksheetName) PopulateTable(crSheet, CRtoXPTable) # Levels to XP table levelSheet = book.find(LevelToXPWorksheetName) PopulateTable(levelSheet, LevelToXPTable)
def get_nafprod(): ''' This is the replacement for get_naco() ''' sheets = Sheets.from_files('./client_secret.json', './storage.json') fileId = nafprod_sheet url = 'https://docs.google.com/spreadsheets/d/' + fileId s = sheets.get(url) sheet_index = int( thisrun[-2:]) - 1 # sheet index (0 based) should equal month s.sheets[sheet_index].to_csv(nafcsv, encoding='utf-8', dialect='excel') msg = 'NAFProduction Google Sheet for %s saved to csv' % thisrun print(msg) logging.info(msg)
def main(): # print("Checking environment variable TIMESHEET_URL for spreadsheet URL...") timesheet_url = os.environ.get('TIMESHEET_URL', "").strip() if not timesheet_url: raise Exception("Please set the TIMESHEET_URL environment variable accordingly.") # print("Checking environment variable USER_FULL_NAME for spreadsheet URL...") user_full_name = os.environ.get('USER_FULL_NAME', "").strip() if not user_full_name: print("Warning: USER_FULL_NAME environment variable not set!") user_full_name = "Herman Toothrot" print("") print("Usage: python timesheet.py [command|date] [date]") print("Example: python timesheet.py stats 202011") print("Example: python timesheet.py 20201130") print("") print("Available commands:") print("- stats: show summed up hours and minutes for the given/current month") print(" use \"CSV=1 python timesheet.py stats\" to format the output") print(" as CSV") print("- daily: same as stats, except ready to email to HR") print("- csv: task breakdown for the month and time spend on each task") print("") print("""Tip: use "DEBUG=1 timesheet <parameter>" to enable debug output""") print("") print("Trying to load client-secrets.json file ...") secrets_file, cache_file = get_client_secret_filenames() sheets = Sheets.from_files(secrets_file, cache_file, no_webserver=False) print("Success.") date = None if len(sys.argv) < 3 else sys.argv[2].strip() arg = "read today" if len(sys.argv) < 2 else sys.argv[1].strip() if arg == "stats": calc_stats(sheets, timesheet_url, date or arrow.now().format('YYYYMM')) elif arg == "daily": calc_daily_hours_for_month(sheets, timesheet_url, date or arrow.now().format('YYYYMM')) elif arg == "csv": export_csv(sheets, timesheet_url, date or arrow.now().format('YYYYMM')) else: date_to_use = "read today" if arg == '' else arg load_sheet_and_read_data(sheets, timesheet_url, date_to_use, user_full_name) print("Done.")
def download_networks(): """ This function downloads curated network files (edges and nodes) from spreadsheets in Google Drive as csv files. """ # create dir: curation/data data_path = os.getcwd() + '/curation/data/' + version if not os.path.isdir(data_path): os.makedirs(data_path) # download to dir edges and nodes ### # url-sheet1=https://docs.google.com/spreadsheets/d/1ZF0cLyAN2_LPXbWNVss2yg2de7fvmHi21Zs7r1vFP54/edit#gid=0 # url-sheet2=https://docs.google.com/spreadsheets/d/1ZF0cLyAN2_LPXbWNVss2yg2de7fvmHi21Zs7r1vFP54/edit#gid=841865829 # INSTALL: https://pypi.python.org/pypi/gsheets/0.3 # https://pypi.python.org/pypi/pygsheets (more mature but under dev) # https://developers.google.com/sheets/api/quickstart/python ### # access to my drive files print( '\nConnecting Google Drive to start the download process of the curated networks..\n' ) driveFilesObject = Sheets.from_files('/home/nuria/client_secrets.json', '/home/nuria/storage.json') # create a list for curated network files spreadsheets (with Google Drive API?) spreadsheetsIds_dct = { '1pS3rT1hFShsCalu9bLGpAjQWp4y3_mgDxCtcHMAk2I8': 'ngly1_deficiency', '1z4PrO8AuNqyAOY3UMYxaMQ1JUcL5z-IbWsw54yQ8sYA': 'ngly1_human', '1thcKRGY1TnXepI8BJ6MYDhOgCayt3gEdPkOkFO4Nd4M': 'aqp1_human', '17kpta304URAxgd0NN4Uvyyu_qC1n2KObOrNAkFCQzRU': 'aqp1_mouse', '1ZF0cLyAN2_LPXbWNVss2yg2de7fvmHi21Zs7r1vFP54': 'glcnac_human', '17jXa5f_B74JaT8yuhExRNIozDRRNnPdcfM7yiV4BRtk': 'enns_2014', '1ZCfOdYtXn2mda2ybov8ibk0aXOtIBYDNw0RIoqkHldk': 'lam_2016' } spreadsheetIds_list = spreadsheetsIds_dct.keys() # download curated network files (edges and nodes) csv_name = lambda dct: '%s/%s.csv' % (data_path, dct.get('sheet')) #csv_name = lambda dct: '%s/%s-%s.csv' % (data_path, dct.get('title'), dct.get('sheet')) for sp_id in spreadsheetIds_list: driveFilesObject[sp_id].to_csv(make_filename=csv_name) return print('\nDownload process finished.\nFiles located at: {}.'.format( data_path))
def build_dict_from_sheet(): sheets = Sheets.from_files('credential.json', 'storage.json') url = 'https://docs.google.com/spreadsheets/d/1hg9505a4hmp93pssg3hnZkPBSEhoR0X2w3i_MApocZ8/edit#gid=1621532970' sheet = sheets.get(url) cols = ['B', 'D'] data = DataFrame() data = sheet.sheets[0].to_frame() data = data.rename(columns={'Every class you have taken: Format-> Course_Name(Grade/TA)':'Courses'}) data = data[['Name', 'Courses']] for index, row in data.iterrows(): courses = cleanup_data(row['Courses']) for c in courses: # print(c.split('(')) course_name, grade = c.split('(') if (course_name, row["Name"].lower()) not in added_set: added_set.add((course_name, row['Name'])) is_ta = grade[:-1].lower() == 'ta' grade = grade[:-1] if not is_ta else 'A' course_directory[course_name].append({'Name' : row['Name'], 'grade' : grade, 'ta' : is_ta}) print(course_directory)
def getSheets(s_id, s_key): global sheets_url, sheets_tab, sheets_tab_name try: sheets_url = os.environ['sheets_url'] except Exception as e: print("\"sheets_url\" not set as an environ, the default url \"" + str(sheets_url) + "\" will be used.") try: sheets_tab = int(os.environ['sheets_tab']) except Exception as e: print("\"sheets_tab\" not set as an environ, the default tab \"" + str(sheets_tab) + "\" will be used.") sheets = Sheets.from_developer_key(s_key) s = sheets.get(sheets_url) print("Sheet loaded: " + str(s)) print(s.sheets[sheets_tab]) print("monday sample: " + s.sheets[sheets_tab]['B' + str(sheet_split_num_start)]) sheets_tab_name = str(s.sheets[sheets_tab]) return s
def download_onlinesave(): ''' Download onlinesave for local parsing. ''' sheets = Sheets.from_files(conf_dir + 'client_secret.json', conf_dir + 'storage.json') fileId = online_save_id url = 'https://docs.google.com/spreadsheets/d/' + fileId s = sheets.get(url) sheet_index = 0 oscsv = 'OnlineSave%s.csv' % this_year s.sheets[sheet_index].to_csv(oscsv, encoding='utf-8', dialect='excel') msg = '= OnlineSave Google Sheet saved to csv' if verbose: print(msg) logging.info(msg)
def __init__(self, url, sh_name='Form Responses 1', credentials='from_drive_to_xlsx/config/credentials.json', storage_json='from_drive_to_xlsx/config/storage.json'): """ Parameters ---------- url: string URL to access the workbook sh_name: string, default 'BDD Missions' Name of the sheet name with all projects credentials: string, default 'config/credentials_gsheets.json' Path to access the JSON credentials file storage_json: string, default 'config/storage.json' Path to access the JSON storage file """ workbook = Sheets.from_files(credentials, storage_json).get(url) self.wb = workbook self.sh_name = sh_name self.ws = workbook.find(sh_name)
def convert_to_json(): global sheet, sheet_id sheets = Sheets.from_files('./google_api_client_keys/credentials.json') sheet = sheets[sheet_id] companies = [] for ws in sheet: for row in range(1, ws.nrows): companies_sub = { "Name": ws.at(row, 1), "Location": ws.at(row, 0) } companies.append(companies_sub) unique_keys = [] for i in range(0, len(companies)): if companies[i] not in companies[i+1:]: unique_keys.append(companies[i]) comp = json.dumps(list(unique_keys)) with open('./static/assets/companies.json', 'w') as fh: fh.write(comp)
from gsheets import Sheets from web3 import Web3, HTTPProvider, IPCProvider web3 = Web3(HTTPProvider('http://localhost:8545')) sheets = Sheets.from_files('./client_secret.json', './storage.json') s = sheets['1xKi8R1E1tlMRwxkuFxtj5o_6-0jA7tAcAkFvAGLai7E'] q_id = s.sheets[0]['H2']
def shareCCLEbams(users, samples, groups=[], raise_error=True, arg_max_length=100000, bamcols=["internal_bam_filepath", "internal_bai_filepath"], refsheet_url="https://docs.google.com/spreadsheets/d/1XkZypRuOEXzNLxVk9EOHeWRE98Z8_DBvL4PovyM01FE", privacy_sheeturl="https://docs.google.com/spreadsheets/d/115TUgA1t_mD32SnWAGpW9OKmJ2W5WYAOs3SuSdedpX4"): """ same as shareTerraBams but is completed to work with CCLE bams from the CCLE sample tracker You need to have gsheet installed and you '~/.client_secret.json', '~/.storage.json' set up Args: ---- users: list[str] of users' google accounts groups: list[str] of groups' google accounts samples list[str] of samples cds_ids for which you want to share data bamcols: list[str] list of column names where bams/bais are raise_error: whether or not to raise an error if we find blacklisted lines refsheet_url: the google spreadsheet where the samples are stored privacy_sheeturl: the google spreadsheet where the samples are stored Returns: -------- a list of the gs path we have been giving access to """ sheets = Sheets.from_files('~/.client_secret.json', '~/.storage.json') print("You need to have gsheet installed and you '~/.client_secret.json', '~/.storage.json' set up") privacy = sheets.get(privacy_sheeturl).sheets[6].to_frame() refdata = sheets.get(refsheet_url).sheets[0].to_frame(index_col=0) blacklist = [i for i in privacy['blacklist'].values.tolist() if i is not np.nan] blacklisted = set(blacklist) & set(samples) print("we have " + str(len(blacklist)) + ' blacklisted files') if len(blacklisted): print("these lines are blacklisted " + blacklisted) if raise_error: raise ValueError("blacklistedlines") if type(users) is str: users = [users] togiveaccess = np.ravel(refdata[bamcols].loc[samples].values) usrs = "" for group in groups: usrs += " -g " + group + ":R" for user in users: usrs += " -u " + user + ":R" cmd_prefix = "gsutil -m acl ch " + usrs cmd = cmd_prefix for n, filename in enumerate(togiveaccess): oldcmd = cmd cmd += ' ' + filename if (len(cmd) > arg_max_length) | (n==len(togiveaccess)-1): if n < len(togiveaccess)-1: cmd = oldcmd print('granting access to {:d} files'.format(n)) with open('/tmp/grantaccess{:d}.sh'.format(n), 'w') as f: f.write(cmd) code = os.system(cmd) cmd = cmd_prefix + ' ' + filename if code == signal.SIGINT: print('Awakened') return print('the files are stored here:\n\n' + refsheet_url) print('\n\njust install and use gsutil to copy them') print('https://cloud.google.com/storage/docs/gsutil_install') print('https://cloud.google.com/storage/docs/gsutil/commands/cp') return togiveaccess
try: if not os.path.exists(directory): os.makedirs(directory) except OSError: logging.info('Error: Creating directory. ' + directory) # Create CSV file folder locally if does not exist logging.info("Creating CSV folder " + downloadFolder + ".") createFolder(downloadFolder) # Authenticate Connection try: # See: https://pypi.org/project/gsheets/ # Initalize sheet object with authentication sheets = Sheets.from_files('~/client_secrets.json', '~/storage.json') # Uncomment and run for inital authentication # sheets #doctest: +ELLIPSIS except OSError: logging.info('Error: Could not authenticate. Check json files in path.') # Interate through sheet list and download to local CSV files for workbook in workbookList: dashboardID = workbook[0] # Create book object for Dashboard Sheet dashb = sheets[dashboardID] # Get workbook name title = dashb.title # Download all sheets in list 'sheetsToDownload' sheetsToDownload = workbook[1] for sheetToDownload in sheetsToDownload:
def __init__(self, credentials: str, sheet_id: str): self.sheet_id = sheet_id self.sheets = Sheets.from_files(credentials) self.sheet = self.sheets.get(self.sheet_id) self.metadata = self.get_metadata()
def updateCSV(self): # Sheets object self.sheets = Sheets.from_files('client_secrets.json', 'storage.json') # GoogleSheets URL self.url = 'https://docs.google.com/spreadsheets/d/1qz_1Fy-2Sda76fe8bIsD4Ci0aO25cmUTMJVCHAqO7NI/edit#gid=1542267929' # Get Poker History Sheets s = self.sheets.get(self.url) # Update CSV files s.sheets[0].to_csv('Game_History.csv', encoding='utf-8', dialect='excel') s.sheets[1].to_csv('Player_Stats.csv', encoding='utf-8', dialect='excel') s.sheets[2].to_csv('Player_Info.csv', encoding='utf-8', dialect='excel') # Load Game History with open(self.gameHistory, encoding='utf-8') as sheet: reader = csv.reader(sheet, delimiter=',') # Parse Games into games structure row_index = 0 gameNum = 0 date = 'date' game = [] for row in reader: if row_index == 0: row_index += 1 continue if row: if (row[0]): date = row[0] if (row[2] == 'Total'): columns = [row[2]] + [ round(float(num), 2) for num in row[4:6] ] else: if (row[7] == 'n'): self.outstanding.append( [date, row[2], round(float(row[6]), 2)]) columns = [row[2]] + [row[3]] + [ round(float(num), 2) for num in row[4:7] ] + [row[7]] game.append(columns) else: self.games.append(game) game = [] gameNum += 1 row_index += 1 # Last Game needs to be added self.games.append(game) self.numGames = gameNum print(self.outstanding) # Load Player Stats with open(self.playerStats, encoding='utf-8') as sheet: reader = csv.reader(sheet, delimiter=',') row_index = 0 for row in reader: if row_index == 0: row_index += 1 continue columns = [row[0], row[1] ] + [round(float(num), 2) for num in row[2:7]] self.rank.append(columns) self.totaloutstanding.append([row[1], round(float(row[5]), 2)])
def __init__(self, credentials) -> None: self.credentials = credentials self.sheets = Sheets.from_files(credentials)
key='035f240c26134eae472e', secret='b0f4c7015ee1638bcbcf', cluster='ap1', ssl=True) ] config = { "apiKey": "AIzaSyAFQWHKQcfdZ0GmxwFzohjPjRm6vwcbREM", "authDomain": "mael-c2ed5.firebaseapp.com", "databaseURL": "https://mael-c2ed5.firebaseio.com", "storageBucket": "mael-c2ed5.appspot.com" } firebase = pyrebase.initialize_app(config) database = firebase.database() sheets = Sheets.from_files('credentials.json') url = 'https://docs.google.com/spreadsheets/d/1v_qSadYXZzS0TFuQ-vbmQKR95kKzFxiAvt-DQLwXeX8' def error(): print("ERROR") try: database.child("Error").set(1) except: print("DATABASE ERROR (CHECK INTERNET)") def get_client(): Id = database.child('Id').get().val() Terminate = database.child('Terminate').get().val() if Terminate == 1:
#Mail Package import smtplib from email.message import EmailMessage #google sheet package from gsheets import Sheets #Authorizing the api sheets = Sheets.from_files('FD4GS.json','FD4GS_cache.json') #Fetching information from owners database vi1 = sheets.get('1URQBjLmkRkPxI1RGQFpp_ZxUa1xMcoMth3qmWH9DK2Y') # Vehicle Information vi1_form1_ws = vi1.sheets[0] entries1 = vi1_form1_ws.values()[1:] entries1 = [(i[2],i[6]) for i in entries1] #Fetching information from search database vi2 = sheets.get('1i2_N7yqcJQQ7cpBJqR8KnxpUOPh3TgNvY8oC1mCSv0U') # Search Database vi2_form1_ws = vi2.sheets[0] entries2 = vi2_form1_ws.values()[1:] entries2 = [(j[1],j[2]) for j in entries2]