def main(): # User Information ss2 = ezsheets.Spreadsheet('1Kbqh0S5Ohnm1JycrDVSYLhq7379eLLHvq5tl7WXp8Vo') dashboard = ss2[0] # first sheet # Formatted User List ss3 = ezsheets.Spreadsheet('1kQJnM2-1c18cb0r3cY18adcU5-mQohrk7ar8Bf7llAA') user_info = ss3[0] add_new_users(dashboard, user_info)
def g_sheet(): # region hide import ezsheets # define google sheet file ss = ezsheets.Spreadsheet('17IuuYN-v7bzRYyJ8V4IDZVFYhEIUcTAPmGrhoFaLoKE') sheet = ss['ah lim ecu claim'] # Declare variables x = 0 result = [] # result[][] to insert to db (list of lists) for i in range(1, 50): x += 1 result.append(tuple(sheet.getRow(i)[:10])) new_result = [] new_string = "" for x in result: if x[0] != "": if '9.1.1' in str(x[4]): new_string = x[4].replace('9.1.1', 'ecu 9.1.1') new_result.append(new_string) print(new_result)
async def daily(self, ctx): author = ctx.message.author ss = ezsheets.Spreadsheet( 'a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] user = author.id user = str(user) #If the user exists if user in sheet.getColumn(1): location = sheet.getColumn(1).index(user) location += 1 credits = sheet[5, location] credits = int(credits) #If the user doesn't exist yet else: await ctx.send('Please register first using `!register`.') credits += DAILY_AMOUNT sheet[5, location] = credits embed = discord.Embed(color=3066993) embed.add_field(name=f'You got {DAILY_AMOUNT:,d} credits', value=f'You have {credits:,d} credits', inline=False) """ #Cooldown function for viewing cooldowns global cooldowns now = datetime.datetime.now()#This is the current time future = now + datetime.timedelta(seconds=30)#Add 24 hours to the time, getting time next value can be completed cooldowns[str(ctx.author.id)] = future """ await ctx.send(embed=embed)
def get_form_data(fileName): """ Summary: Collects e-mail adresses from a Google Sheet containing the responses of a form. Args: fileName (str): Google spreadsheet name or ID Returns: List of e-mail Adresses """ ss = ezsheets.Spreadsheet(fileName) sheet = ss[0] emailCol = 0 emailList = [] # Find row number for 'Adresse e-mail'. for i, value in enumerate(sheet.getRow(1)): # Sheet is in French, change 'Adresse e-mail' if Sheet is in English if value == 'Adresse e-mail': emailCol = i + 1 # Append emails to emailList. for value in sheet.getColumn(emailCol)[1:-1]: if value != '': emailList.append(value) print(emailList) return emailList
def level_checker(user): ss = ezsheets.Spreadsheet('id') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] location = sheet.getColumn(1).index(user) location += 1 name = str(sheet[3, location]) gamesPlayed = int(sheet[10, location]) if gamesPlayed % 30 == 0: print('Level up') level = int(gamesPlayed / 30) if level > 50: print('Max Level achieved') return False else: sheet[8, location] = level embed = discord.Embed( color=0xAC6AFF, description=f'{name} has leveled up to Level {level}!') return embed else: return False response = str(number) return response
def download_revision_data(): GOOGLE_SHEET_ID = "114LWy42iuyzIfKFrg-egp2o6jT-2XrnkzrYFSqmweOE" google_sheet = ezsheets.Spreadsheet(GOOGLE_SHEET_ID) # Get the first sheet that contains the form responses for Quran Review data input_sheet = google_sheet[0] return input_sheet.getRows(2, input_sheet.rowCount - +1)
def get18s(sheet, all_standings=False): ss = ezsheets.Spreadsheet(sheet) print(ss.title) print(ss.sheetTitles) players = ss['Player Results'].getColumn('I') points = ss['Player Results'].getColumn('J') listOfLists = [players, points] players = [x for x in players if x] points = [x for x in points if x] players.pop(0) points.pop(0) print(players, "\n", points) some_tuples = list(zip(players, points)) df_results = pd.DataFrame(some_tuples, columns=['Players', 'Points']) df_results['Results'] = pd.to_numeric(df_results['Points']) for i in range(len(df_results['Points'])): df_results['Points'][i] = int(df_results['Points'][i]) df_18_plus = df_results[df_results['Points'] > 18] # print(type(df_results['Points'][0])) players_18_plus = list(df_18_plus['Players']) # print(players_18_plus) if all_standings is True: return df_results else: return players_18_plus
async def register(self, ctx): author = ctx.message.author user = author.id user = str(user) ss = ezsheets.Spreadsheet( 'a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] #If the user exists if user in sheet.getColumn(1): await ctx.send('You have already been registered.') #If the user doesn't exist yet else: empty = sheet.getColumn(1).index('') location = empty + 1 sheet[1, location] = user sheet[3, location] = author.name sheet[5, location] = STARTING_CREDITS sheet[6, location] = STARTING_CRATES sheet[7, location] = STARTING_EXP sheet[8, location] = STARTING_LEVEL sheet[9, location] = 0 sheet[10, location] = 0 sheet[11, location] = 0 sheet[12, location] = 0 await ctx.send('You have successfully been registered!')
async def name(self, ctx, new=None): ss = ezsheets.Spreadsheet( 'a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] if new == None: #Help men embed = discord.Embed(color=discord.Colour.orange()) embed.add_field(name='Help', value='Changes the name that the bot calls you.', inline=False) embed.add_field(name='Usage', value='**!name <name>**', inline=False) else: if str(ctx.author.id) in sheet.getColumn(1): location = sheet.getColumn(1).index(str(ctx.author.id)) location += 1 credits = sheet[5, location] credits = int(credits) #If the user doesn't exist yet else: await ctx.send('Please register first using `!register`.') return sheet[3, location] = new embed = discord.Embed( color=discord.Colour.orange(), description= f'Your name has been changed in the bots records to {new}.') await ctx.send(embed=embed)
async def search(self, ctx): ss = ezsheets.Spreadsheet( 'a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] author = ctx.message.author user = author.id user = str(user) #If the user exists if user in sheet.getColumn(1): location = sheet.getColumn(1).index(user) location += 1 credits = sheet[5, location] credits = int(credits) #If the user doesn't exist yet else: await ctx.send('Please register first using `!register`.') print(credits) if credits <= 25: rand = random.randint(25, 100) credits += rand sheet[5, location] = credits embed = discord.Embed(color=3066993) embed.add_field(name=f'You have found {rand} credits.', value=f'You have {credits} credits.') else: embed = discord.Embed( color=0xFF0000, description='You can only search with 25 credits or less.') await ctx.send(embed=embed)
def __get_trade_journal__(self): spreadsheets = ezsheets.listSpreadsheets() for key, value in spreadsheets.items(): if value == self.title: return ezsheets.Spreadsheet(key) return self.__create_trade_journal__()
def sheetinit(): ss = ezsheets.Spreadsheet(config['spreadsheet']) # grab the google spreadsheet id ss.downloadAsCSV() sheetfilecsv = open('CFC_Classes_Sign_Up_.csv') sheetdatacsv = csv.reader(sheetfilecsv) dataList = list(sheetdatacsv) dataList.pop(0) return dataList
def copiarPlantilla(titulo): ss = ezsheets.Spreadsheet(PLANTILLA) ss2 = ezsheets.createSpreadsheet(titulo) ss[0].copyTo(ss2) ss2[0].delete() return ss2
def get_anomalies_constraints( creds_dir='../credentials', gbook_url='https://docs.google.com/spreadsheets/d/1r9v4lJHvMYxMZpwdwdlb6nkTe9cQrpLlHEmfJbQ4kjw/edit#gid=141178862', gsheet_name='smallcensusb'): """ This function parses a given xls worksheet to copy the anomalies and warnings defined in it. Args: creds_dir: String indicating location of credentials folder gbook_url: String indicating the url to the google sheets workbook. gsheet_name: [Optional] String indicating the label for the worksheet to be parsed. Returns: Pandas dataframe """ this_dir = os.getcwd() os.chdir(creds_dir) s = ezsheets.Spreadsheet(gbook_url) s.downloadAsExcel() x = pd.read_excel(gsheet_name + '.xlsx') os.chdir(this_dir) xlsdata = x tbl = [] for idx, entry in xlsdata.iterrows(): tblrow = {'row': idx} if type(entry['constraint']) != float: tblrow.update({ 'constraint': entry['constraint'], 'constraint_message::English': entry['constraint_message::English'], 'constraint_message::Swahili': entry['constraint_message::Swahili'], 'constraint_message::Portuguese': entry['constraint_message::Portuguese'], 'constraint_condition': entry['calculation'] }) if entry['type'] == 'note': tblrow.update({ 'warning_name': entry['name'], 'warning_label::English': entry['label::English'], 'warning_label::Swahili': entry['label::Swahili'], 'warning_label::Portuguese': entry['label::Portuguese'], 'warning_hint::English': entry['hint::English'], 'warning_hint::Swahili': entry['hint::Swahili'] }) if len(tblrow) > 1: tbl.append(tblrow) return pd.DataFrame(tbl)
def find_mistakes(spreadsheet_id): """Find Mistakes in a Spreadsheet.""" spreadsheet = ezsheets.Spreadsheet(spreadsheet_id) row = 2 while spreadsheet[0].getRow(row)[0]: if int(spreadsheet[0].getRow(row)[0]) * int(spreadsheet[0].getRow(row)[1]) \ != int(spreadsheet[0].getRow(row)[2]): print(f'Row {row} has mistake') row += 1
def get_gsheet(spreadsheet_id): # change ss_id to be the id of your google sheet ss_id = spreadsheet_id # instantiate Spreadsheet object ss = ez.Spreadsheet(ss_id) sheet = ss[0] # index for rows makes sure column title don't get processed rows = sheet.getRows()[1:] return sheet
async def resetTilted(ctx): ss = ezsheets.Spreadsheet('a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] sheet.updateColumn(2, ['MMR']) #Updates all of column 2 to only contain Title embed = discord.Embed(color=0x607d8b, description='All discord tilt MMRs have been reset') await ctx.send(embed=embed)
def mtgmeleeresults(sheet): ss = ezsheets.Spreadsheet(sheet) # print(ss.title) # print(ss.sheetTitles) # ss_deck_breakdown = ss.sheets[1] # ss_results = ss.sheets[2] # print(ss_deck_breakdown) # players = ss.sheets[1].getColumn(1) # deck_lists = ss.sheets[1].getColumn(2) tournamentIdList = ss.sheets[2].getColumn(1) roundList = ss.sheets[2].getColumn(2) playerOneList = ss.sheets[2].getColumn(3) playerTwoList = ss.sheets[2].getColumn(4) resultsList = ss.sheets[2].getColumn(5) listOfLists = [ tournamentIdList, roundList, playerOneList, playerTwoList, resultsList ] ### Delete Empty Lists for l in listOfLists: print("List", l) l[:] = [x for x in l if x] l.pop(0) resultType = [-9 for x in range(len(resultsList))] # print(resultType) ###Troubleshooting tournamentIdList = [593 for x in range(len(resultsList))] for i in range(len(resultsList)): if (playerOneList[i]) in resultsList[i]: resultType[i] = 1 if (playerTwoList[i]) in resultsList[i]: resultType[i] = 2 if ('0-0-3') in resultsList[i]: resultType[i] = 3 if 'was awarded' in resultsList[i]: resultType[i] = 4 ### Bye is code 4 # print(resultsList) # print("Player 1 Wins: ", resultType.count(1), "\nPlayer 2 Wins: ", resultType.count(2), "\nDraws: ", resultType.count(3)) # print("Byes: ", resultType.count(4), "\nLeft Over: ", resultType.count(-9)) results_df = pd.DataFrame(list( zip(tournamentIdList, roundList, playerOneList, playerTwoList, resultsList, resultType)), columns=[ 'Tournament Id', 'Round', 'Player 1', 'Player 2', 'Result', 'Result Code' ])
def send_email(email_contents, date_time): ss3 = ezsheets.Spreadsheet('1kQJnM2-1c18cb0r3cY18adcU5-mQohrk7ar8Bf7llAA') user_info = ss3[0] i = 0 while (user_info['A' + str(i + 2)] != ""): email_addresses.append(user_info['A' + str(i + 2)]) i += 1 email_contents.append(" \n ") yag = yagmail.SMTP('*****@*****.**', 'PASSWORD') email_contents.append( 'Check the dashboard for links and more information: https://tinyurl.com/vaxxtracknj' ) yag.send(email_addresses, date_time + ' Vaccine Website Update', email_contents)
async def loserboard(self, ctx): ss = ezsheets.Spreadsheet( 'a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] response = '' name = '' names = [] creditList = sheet.getColumn(5) creditList[0] = 0 removal = creditList.index('') removal -= 1 del creditList[0] del creditList[ removal:] #Removes all of the blank credits from the list print(creditList) creditList = list(map( int, creditList)) #Converts all items in the list to integer for i in range(5): amount = min(creditList) index = creditList.index(amount) print(f'Index: {index}') for r in range(200): #row = sheet.getRow(r+1) row = sheet[5, (r + 2)] print(row) if row in str(amount): name = sheet[3, (r + 2)] print(f'Row: {r + 2}') if name in names: continue else: names.append(name) break response += f'{i+1}. {name:<10} - {amount:>10,d} credits\n' index = creditList.index(amount) del creditList[index] embed = discord.Embed(color=0xffff00) embed.set_author(name=response) await ctx.send(embed=embed)
def Check_Error(ID_): print('Checking for Erros...') ss = ezsheets.Spreadsheet(ID_) sheet = ss[0] try: for int_ in range(2, len(sheet.getRows())): formula = int(sheet.getRow(int_)[0]) * int( sheet.getRow(int_)[1]) == int(sheet.getRow(int_)[2]) if formula == False: print((sheet.getRow(int_)[0]) + ' * ' + (sheet.getRow(int_)[1]) + ' = ' + (sheet.getRow(int_)[2])) except: print('Finished!')
def check_sum_table(first_column, second_column, sum_column, spreadsheet): """Check sum of 2 values from 2 columns.""" ss = ezsheets.Spreadsheet(spreadsheet) number_of_rows = ss[0].rowCount for row in range(2, number_of_rows+1): if ss[0].getRow(row)[0] == '': print(f"no more rows to check!\nlast checked row: {row}") break if not int(ss[0].getRow(row)[first_column]) * int(ss[0].getRow(row)[second_column]) == int(ss[0].getRow(row)[sum_column]): print( f"wrong sum in row: {row}. " f"{int(ss[0].getRow(row)[sum_column])} is not equal: " f"{int(ss[0].getRow(row)[first_column])} * " f"{int(ss[0].getRow(row)[second_column])}")
def write_output_due(due_pages_list, summary_by_page): output_spreadsheet = ezsheets.Spreadsheet( "1rHQ5WmyFz79sG8W2pNZmr6AJDuLuQEeOjSahPyPcb5o") # First write the due pages sheet output_sheet = output_spreadsheet["Due"] print("Writing sheet - Due") # empty the sheet for i in range(7): output_sheet.updateColumn(i + 1, []) row = 1 column_names = ["page", "due", "interval", "revision#", "score", "rating"] output_sheet.updateRow(row, column_names) for due_page in sorted( due_pages_list, key=lambda page: int(page) # ( # round( # summary_by_page[page]["7.scheduled_interval"] # / summary_by_page[page]["1.revision_number"], # 0, # ), # int(page), # ), ): summary = summary_by_page[due_page] columns = [ due_page, (summary["8.scheduled_due_date"].date() - datetime.date.today()).days, summary["7.scheduled_interval"], summary["1.revision_number"], summary["3.score"], summary["7.scheduled_interval"] / summary["1.revision_number"], ] row += 1 output_sheet.updateRow(row, columns) output_sheet.updateRow( row + 1, [ "Done", "", "", "", "", "", datetime.datetime.now().strftime("%d-%m %H:%M") ], )
def main(): # Dashboard ss = ezsheets.Spreadsheet("SS") dashboard = ss[0] # User Information ss2 = ezsheets.Spreadsheet('SS') user_info = ss2[0] # first sheet # Keyword checker ss3 = ezsheets.Spreadsheet('SS') keywords_sheet = ss3[0] # Get URLS urls = get_urls(dashboard) #3get_old_emails(user_info) #welcome_email(email_addresses) #welcome_text(phone_numbers) #send_email() # Check the website and if neccesary, update the spreadsheet check_websites(dashboard, urls, keywords_sheet)
def init(): global FIXED_SPREADSHEET ezsheets.init() #FIXED_SPREADSHEET = ezsheets.createSpreadsheet(title='Delete Me') # Create a new spreadsheet # Use an existing spreadsheet: FIXED_SPREADSHEET = ezsheets.Spreadsheet( 'https://docs.google.com/spreadsheets/d/1lRyPHuaLIgqYwkCTJYexbZUO1dcWeunm69B0L7L4ZQ8/edit#gid=0' ) tempName = 'temp_%s' % (random.sample('abcdefg' * 10, 10)) FIXED_SPREADSHEET.addSheet(tempName, index=0) while len(FIXED_SPREADSHEET) > 1: del FIXED_SPREADSHEET[1] FIXED_SPREADSHEET.addSheet('Sheet1', index=0) del FIXED_SPREADSHEET[tempName] checkIfSpreadsheetInOriginalState()
def get_email_addresses(sheet_id): spreadsheet = ezsheets.Spreadsheet(sheet_id) logging.debug(spreadsheet.title) # Working with the 'active' sheet as the spreadsheet generated by the form will only have 1 sheet sheet = spreadsheet[0] columns = sheet.getColumns() # Loop through every cell in every column looking for '@' to find e-mail addresses. # This could be more specific depending on the criteria of the form used, in the case of the form I used for this excercise # the e-mail address input is the only input that should contain a '@' symbol. email_addresses = [ cell for column, cells in enumerate(columns) for cell in cells if "@" in cell ] if email_addresses == []: raise Exception("There are no e-mail addresses in this spreadsheet.") logging.debug(email_addresses) return email_addresses
def get_spreadsheet(ss_name): # Make sure file exists if Path(ss_name).absolute().exists() == False: raise Exception( f"{ss_name} was not found in current working directory.") sheet_list = ezsheets.listSpreadsheets() # Strip extension off ss_name ss_no_ext = re.sub(r"(\..*)", "", ss_name) # Check if spreadsheet is already in Google Drive, if not upload it. if ss_no_ext not in sheet_list.values(): ss = ezsheets.upload(f"{ss_name}") return ss # If so, get the key and open it. else: for key, value in sheet_list.items(): if ss_no_ext == value: ss = ezsheets.Spreadsheet(key) return ss
def finding_mistakes_in_a_spreadsheet(): """Find values that not fit expected output of the formula.""" ss = ezsheets.Spreadsheet('1jDZEd' 'vSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg') errors_location = [] sheet = ss[0] number_of_rows = sheet.rowCount for row in range(2, number_of_rows): if sheet.getRow(row)[0] == "": print( f"Data are missing, or table contains empty row at line {row}") break elif int(sheet.getRow(row)[0]) * int(sheet.getRow(row)[1]) != int( sheet.getRow(row)[2]): errors_location.append(row) print(f"You have some errors in row/s {errors_location}")
def get_list_of_decklists(sheet): ''' :param sheet: Google sheet that has the decklists Need to get hyperlinks and then strip out numbers :return: list of decklist numbers ''' ss = ezsheets.Spreadsheet(sheet) # Import Sheet From EZSheets decklist_numbers = ss.sheets[-1].getColumn( 'M') # Stripped Out Decklist numbers - need this for the loop ### Get rid of blanks decklist_numbers[:] = [x for x in decklist_numbers if x] ### Deal with Excel Errors exclude_nas = ['#N/A', 'N/A', 'NA', 'ERROR'] decklist_numbers = [x for x in decklist_numbers if x not in exclude_nas] return decklist_numbers
async def credits(self, ctx): author = ctx.message.author ss = ezsheets.Spreadsheet( 'a') #Opens up the google spreadsheets 'Tilted' sheet = ss['output'] user = author.id user = str(user) #If the user exists if user in sheet.getColumn(1): location = sheet.getColumn(1).index(user) location += 1 credits = sheet[5, location] credits = int(credits) #If the user doesn't exist yet else: await ctx.send('Please register first using `!register`.') await ctx.send(author.mention + f', you have {credits:,d} credits.')