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.')
