def sync_table(target_map):
update_cells = []
target_text_map = build_target_map()
colnumber = 33 #if direct_column else 34
valid_target_dict = utils.get_valid_target_map(worksheets["BIA to SDG mapping"], colnumber)
write_row_num = title_count
for row in worksheets["BIA to SDG Target Mapping"]:
writetarget = row[0]
targets = []
if writetarget in valid_target_dict:
targets = valid_target_dict[writetarget]
padding = [''] * (20 - len(targets))
targets.extend(padding)
write_row_num += 1
for n in range(len(targets)):
if targets[n] == '':
update_cells.append(gspread.Cell(write_row_num, 12 + n, ''))
else:
update_cells.append(gspread.Cell(write_row_num, 12 + n, target_text_map[targets[n]]))
# Update all cells
writesheet.update_cells(update_cells)
def overwrite_sheet(self, spreadsheet_id, table, sheet_index=0):
"""
Replace the data in a Google sheet with a Parsons table, using the table's columns as the
first row.
`Args:`
spreadsheet_id: str
The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table: obj
Parsons table
sheet_index: int (optional)
The index of the desired worksheet
"""
sheet = self._get_sheet(spreadsheet_id, sheet_index)
sheet.clear()
# Add header row
sheet.append_row(table.columns)
cells = []
for row_num, row in enumerate(table.data):
for col_num, cell in enumerate(row):
# We start at row #2 to keep room for the header row we added above
cells.append(
gspread.Cell(row_num + 2, col_num + 1, row[col_num]))
# Update the data in one batch
sheet.update_cells(cells)
async def cmd_xproll(self, *args, member, channel, **_):
target = member
try:
wsh = gc.open_by_key(CHAR_SHEET[str(target.id)]).sheet1
except:
raise BotError("Impossible d'ouvrir la fiche de personnage du membre")
ll = wsh.get_all_values() # type: List[List[str]]
if len(ll[0]) < 13:
raise BotError("La fiche de personnage doit au moins avoir une colonne 'M'")
recap = []
to_update = {}
for i, line in enumerate(ll):
if line[12] == 'TRUE': # if column M == TRUE
last_rec = xp_roll(line)
recap.append(last_rec)
if last_rec['success']:
to_update[i + 1] = last_rec['new_xp']
if not recap:
return await channel.send("Vous n'avez aucun jet d'XP à faire ...")
await channel.send("```diff\n{}```".format('\n'.join(
[( f"{'+' if d['success'] else '-'} {d['comp_name'][:16]:<16} {d['roll']:^3}/{d['old_total']:>3}"
+ ' | ' + (f"{d['old_total']:^3}->{d['new_total']:^3} (+{d['xp_won']}){' CRITIQUE' if d['crits'] else ''}" if d['success'] else 'Échoué'))
for d in recap]
)))
up = wsh.range(f"I1:I{len(ll)}")
up = [gspread.Cell(cell.row, cell.col, to_update[cell.row]) for cell in up if cell.row in to_update]
if up:
wsh.update_cells(up)
up = wsh.range(f"M1:M{len(ll)}")
for cell in up:
if cell.value == 'FALSE' or cell.value == 'TRUE':
cell.value = False
if up:
wsh.update_cells(up)
def append_to_sheet(self, spreadsheet_id, table, sheet_index=0):
"""
Append data from a Parsons table to a Google sheet. Note that the table's columns are
ignored, as we'll be keeping whatever header row already exists in the Google sheet.
`Args:`
spreadsheet_id: str
The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table: obj
Parsons table
sheet_index: int (optional)
The index of the desired worksheet
"""
sheet = self._get_sheet(spreadsheet_id, sheet_index)
# Grab the existing data, so we can figure out where to start adding new data as a batch.
# TODO Figure out a way to do a batch append without having to read the whole sheet first.
# Maybe use gspread's low-level batch_update().
existing_table = self.read_sheet(spreadsheet_id, sheet_index)
cells = []
for row_num, row in enumerate(table.data):
for col_num, cell in enumerate(row):
# Add 2 to allow for the header row, and for google sheets indexing starting at 1
sheet_row_num = existing_table.num_rows + row_num + 2
cells.append(
gspread.Cell(sheet_row_num, col_num + 1, row[col_num]))
# Update the data in one batch
sheet.update_cells(cells)
def unmap(livesheet, worksheets, title_report_count):
"""Transpose mapped and unmapped indicators column in the report sheet"""
def getmax_count(wks, col):
maxcount = 0
for x in wks:
l = [a.strip() for a in x[col].splitlines()]
maxcount = max(maxcount, len(l))
return maxcount
sheetname = "Unmapped Indicators"
map_col = 14
unmap_col = 15
max_mapped_count = getmax_count(worksheets[sheetname], map_col)
max_unmapped_count = getmax_count(worksheets[sheetname], unmap_col)
title_count = title_report_count[sheetname]
start_map_col = map_col + 8
start_unmap_col = start_map_col + max_mapped_count
writesheet = livesheet.worksheet(sheetname)
update_cells = []
write_row_num = title_count
# Write Titles
for n in range(max_mapped_count):
update_cells.append(gspread.Cell(write_row_num, start_map_col + n, "Mapped_{}".format(n+1)))
for n in range(max_unmapped_count):
update_cells.append(gspread.Cell(write_row_num, start_unmap_col + n, "Unmapped_{}".format(n+1)))
# Write Values
for n, row in enumerate(worksheets[sheetname]):
map_vals = [a.strip() for a in row[map_col].splitlines()]
padding = [''] * (max_mapped_count - len(map_vals))
map_vals.extend(padding)
unmap_vals = [a.strip() for a in row[unmap_col].splitlines()]
padding = [''] * (max_unmapped_count - len(unmap_vals))
unmap_vals.extend(padding)
write_row_num += 1
for num in range(max_mapped_count):
update_cells.append(gspread.Cell(write_row_num, start_map_col + num, map_vals[num]))
for num in range(max_unmapped_count):
update_cells.append(gspread.Cell(write_row_num, start_unmap_col + num, unmap_vals[num]))
writesheet.update_cells(update_cells)
def updateSpreadsheet(rows): # Updates the entire Google Spreadsheet
cells = []
for row_num, row in enumerate(rows):
for col_num, cell in enumerate(row):
cell = cell
cells.append(
gspread.Cell(row_num + 1, col_num + 1, rows[row_num][col_num]))
spreadsheet.worksheet.update_cells(cells)
def get_range(worksheet_data, start_row, start_col, end_row, end_col):
result = []
for row_no in range(start_row - 1, end_row):
row = worksheet_data[row_no] if row_no < len(worksheet_data) else []
for col_no in range(start_col - 1, end_col):
result.append(
gspread.Cell(row_no + 1, col_no + 1,
row[col_no] if col_no < len(row) else ""))
return result
def create_cells(data, header):
cells = []
for column in header:
cells.append(gspread.Cell(1, header.index(column) + 1, column))
for row_num, row in enumerate(data):
for col_num, cell in enumerate(row):
if str(data[row_num][col_num]).lower() == 'true' or str(
data[row_num][col_num]).lower() == 'false':
cells.append(
gspread.Cell(row_num + 2, col_num + 1,
str(data[row_num][col_num]).lower()))
else:
cells.append(
gspread.Cell(row_num + 2, col_num + 1,
str(data[row_num][col_num])))
#TODO: Formating of integers is sketchy: if there's a row with NULL value, integers become decimals.
#cells.append(gspread.Cell(row_num + 2, col_num + 1, gspread.numericise(str(df[row_num][col_num]))))
return cells
def append_to_sheet(self,
spreadsheet_id,
table,
worksheet=0,
user_entered_value=False,
**kwargs):
"""
Append data from a Parsons table to a Google sheet. Note that the table's columns are
ignored, as we'll be keeping whatever header row already exists in the Google sheet.
`Args:`
spreadsheet_id: str
The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table: obj
Parsons table
worksheet: str or int
The index or the title of the worksheet. The index begins with
0.
user_entered_value: bool (optional)
If True, will submit cell values as entered (required for entering formulas).
Otherwise, values will be entered as strings or numbers only.
"""
# This is in here to ensure backwards compatibility with previous versions of Parsons.
if 'sheet_index' in kwargs:
worksheet = kwargs['sheet_index']
logger.warning('Argument deprecated. Use worksheet instead.')
sheet = self._get_worksheet(spreadsheet_id, worksheet)
# Grab the existing data, so we can figure out where to start adding new data as a batch.
# TODO Figure out a way to do a batch append without having to read the whole sheet first.
# Maybe use gspread's low-level batch_update().
existing_table = self.get_worksheet(spreadsheet_id, worksheet)
# If the existing sheet is blank, then just overwrite the table.
if existing_table.num_rows == 0:
return self.overwrite_sheet(spreadsheet_id, table, worksheet,
user_entered_value)
cells = []
for row_num, row in enumerate(table.data):
for col_num, cell in enumerate(row):
# Add 2 to allow for the header row, and for google sheets indexing starting at 1
sheet_row_num = existing_table.num_rows + row_num + 2
cells.append(
gspread.Cell(sheet_row_num, col_num + 1, row[col_num]))
value_input_option = 'RAW'
if user_entered_value:
value_input_option = 'USER_ENTERED'
# Update the data in one batch
sheet.update_cells(cells, value_input_option=value_input_option)
logger.info(f'Appended {table.num_rows} rows to worksheet.')
def writeToSheet(sheet, header, data):
if len(data) > 0:
client.login()
s = client.open("Indeed v2").worksheet(sheet)
book = client.open("Indeed v2")
book.values_clear(sheet + "!A1:U10000")
data.insert(0, header)
cells = []
for row_num, row in enumerate(data):
for col_num, cell in enumerate(row):
cells.append(
gspread.Cell(row_num + 1, col_num + 1,
data[row_num][col_num]))
s.update_cells(cells)
def _cell_feed(
self,
row=None,
max_row=None,
further_rows=False, # XXX: REFACTOR
col=None,
max_col=None,
further_cols=False,
return_empty=False):
# Fetches cell data for a given row, and all following rows if
# further_rows is True. If no row is given, all cells are returned.
params = {}
if row is not None:
params['min-row'] = str(row)
if max_row is not None:
params['max-row'] = str(max_row)
elif not further_rows:
params['max-row'] = str(row)
if col is not None:
params['min-col'] = str(col)
if max_col is not None:
params['max-col'] = str(max_col)
elif not further_cols:
params['max-col'] = str(col)
if (params['min-col'] == params['max-col']
and params['min-col'] == '0'):
return []
if return_empty:
params['return-empty'] = "true"
logger.info("getting cell feed")
try:
feed = self.gspread_client.get_cells_feed(self.worksheet,
params=params)
# Bit of a hack to rip out Gspread's xml parsing.
cfeed = [
gspread.Cell(self, elem)
for elem in feed.findall(gspread.client._ns('entry'))
]
except Exception as e:
logger.exception("gspread error. %s", e)
raise e
return cfeed
def overwrite_sheet(self,
spreadsheet_id,
table,
worksheet=0,
user_entered_value=False,
**kwargs):
"""
Replace the data in a Google sheet with a Parsons table, using the table's columns as the
first row.
`Args:`
spreadsheet_id: str
The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table: obj
Parsons table
worksheet: str or int
The index or the title of the worksheet. The index begins with
0.
user_entered_value: bool (optional)
If True, will submit cell values as entered (required for entering formulas).
Otherwise, values will be entered as strings or numbers only.
"""
# This is in here to ensure backwards compatibility with previous versions of Parsons.
if 'sheet_index' in kwargs:
worksheet = kwargs['sheet_index']
logger.warning('Argument deprecated. Use worksheet instead.')
sheet = self._get_worksheet(spreadsheet_id, worksheet)
sheet.clear()
value_input_option = 'RAW'
if user_entered_value:
value_input_option = 'USER_ENTERED'
# Add header row
sheet.append_row(table.columns, value_input_option=value_input_option)
cells = []
for row_num, row in enumerate(table.data):
for col_num, cell in enumerate(row):
# We start at row #2 to keep room for the header row we added above
cells.append(
gspread.Cell(row_num + 2, col_num + 1, row[col_num]))
# Update the data in one batch
sheet.update_cells(cells, value_input_option=value_input_option)
logger.info('Overwrote worksheet.')
def update_private(user, userindex):
print(user)
print(user['pk'], user['username'], user['full_name'])
cells.append(
gspread.Cell(userindex, 1,
'=IMAGE("%s",4,50,50)' % user['profile_pic_url']))
cells.append(
gspread.Cell(
userindex, 2, '=HYPERLINK("https://www.instagram.com/%s/","%s")' %
(user['username'], user['username'])))
cells.append(gspread.Cell(userindex, 3, '%s' % user['full_name']))
cells.append(gspread.Cell(userindex, 4, '%s' % 'PRIVATE'))
cells.append(gspread.Cell(userindex, 5, '%s' % 'PRIVATE'))
cells.append(gspread.Cell(userindex, 6, '%s' % 'PRIVATE'))
def append_to_sheet(self,
spreadsheet_id,
table,
sheet_index=0,
user_entered_value=False):
"""
Append data from a Parsons table to a Google sheet. Note that the table's columns are
ignored, as we'll be keeping whatever header row already exists in the Google sheet.
`Args:`
spreadsheet_id: str
The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table: obj
Parsons table
sheet_index: int (optional)
The index of the desired worksheet
user_entered_value: bool (optional)
If True, will submit cell values as entered (required for entering formulas).
Otherwise, values will be entered as strings or numbers only.
"""
sheet = self._get_sheet(spreadsheet_id, sheet_index)
# Grab the existing data, so we can figure out where to start adding new data as a batch.
# TODO Figure out a way to do a batch append without having to read the whole sheet first.
# Maybe use gspread's low-level batch_update().
existing_table = self.read_sheet(spreadsheet_id, sheet_index)
cells = []
for row_num, row in enumerate(table.data):
for col_num, cell in enumerate(row):
# Add 2 to allow for the header row, and for google sheets indexing starting at 1
sheet_row_num = existing_table.num_rows + row_num + 2
cells.append(
gspread.Cell(sheet_row_num, col_num + 1, row[col_num]))
value_input_option = 'RAW'
if user_entered_value:
value_input_option = 'USER_ENTERED'
# Update the data in one batch
sheet.update_cells(cells, value_input_option=value_input_option)
def overwrite_sheet(self,
spreadsheet_id,
table,
sheet_index=0,
user_entered_value=False):
"""
Replace the data in a Google sheet with a Parsons table, using the table's columns as the
first row.
`Args:`
spreadsheet_id: str
The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table: obj
Parsons table
sheet_index: int (optional)
The index of the desired worksheet
user_entered_value: bool (optional)
If True, will submit cell values as entered (required for entering formulas).
Otherwise, values will be entered as strings or numbers only.
"""
sheet = self._get_sheet(spreadsheet_id, sheet_index)
sheet.clear()
value_input_option = 'RAW'
if user_entered_value:
value_input_option = 'USER_ENTERED'
# Add header row
sheet.append_row(table.columns, value_input_option=value_input_option)
cells = []
for row_num, row in enumerate(table.data):
for col_num, cell in enumerate(row):
# We start at row #2 to keep room for the header row we added above
cells.append(
gspread.Cell(row_num + 2, col_num + 1, row[col_num]))
# Update the data in one batch
sheet.update_cells(cells, value_input_option=value_input_option)
def update(user, userindex):
totalposts = len(igapi.getTotalUserFeed('%d' % user['pk']))
totalfollowers = len(igapi.getTotalFollowers('%d' % user['pk']))
totalfollowing = len(igapi.getTotalFollowings('%d' % user['pk']))
print(user)
print(user['pk'], user['username'], user['full_name'], totalposts,
totalfollowers, totalfollowing)
cells.append(
gspread.Cell(userindex, 1,
'=IMAGE("%s",4,50,50)' % user['profile_pic_url']))
cells.append(
gspread.Cell(
userindex, 2, '=HYPERLINK("https://www.instagram.com/%s/","%s")' %
(user['username'], user['username'])))
cells.append(gspread.Cell(userindex, 3, '%s' % user['full_name']))
cells.append(gspread.Cell(userindex, 4, '%s' % totalposts))
cells.append(gspread.Cell(userindex, 5, '%s' % totalfollowers))
cells.append(gspread.Cell(userindex, 6, '%s' % totalfollowing))
async def schedule(ctx, game="", date="", time="", name=""):
#Check if the command was called from someone with a "Gamemaster" role
server_roles = ctx.message.guild.roles
gamemaster = None
for i in server_roles:
if (i.name == "Gamemaster"): #If there is a role called "Gamemaster"
gamemaster = i #Get the gamemaster role
print("Gamemaster role available.")
if (gamemaster == None): #If "Gamemaster" role was not found
# create embed
embed_fail = discord.Embed(
title=
"Gamemaster role does not exist. Contact server staff to ask them to make this role. (case-sensitive)",
color=RED)
embed_fail.set_author(name="Gamemaster not available", icon_url=ERROR)
# -
await ctx.message.channel.send(embed=embed_fail)
return
#If the message author is in the list of people that have the "Gamemaster" role
if (ctx.message.author in gamemaster.members):
#Let them schedule the event
print("Able to schedule.")
else:
# create embed
embed_fail = discord.Embed(
title=
'Only those with the "Gamemaster" role can schedule game nights',
color=RED)
embed_fail.set_author(name="Permission denied", icon_url=ERROR)
# -
await ctx.message.channel.send(embed=embed_fail)
return
#After all that permissions checking, time to catch incomplete information!
#Game, date and time are absolutely mandatory, while name is optional
if (game == "" or date == "" or time == ""):
# create embed
embed_fail = discord.Embed(
title=
"Missing information required for scheduling. See help command for details.",
color=RED)
embed_fail.set_author(name="Incomplete command", icon_url=ERROR)
# -
await ctx.message.channel.send(embed=embed_fail)
return
game = check_default_alias(game, ctx)
spread = client.open(str(ctx.guild.id))
sheet = spread.worksheet(game)
try:
var_date = None
var_time = None
print(str(sheet.cell(2, 2)), str(sheet.cell(2, 3)))
#Try to find others cell with the same data
var_date = sheet.find(str(date))
var_time = sheet.find(str(time))
if (date == var_date.value):
print("Date has been found.")
if (time == var_time.value):
print("Time has been found.")
# create embed
embed_fail = discord.Embed(
title="This event is already scheduled for that game.",
color=RED)
embed_fail.set_author(name="Unavailable slot", icon_url=ERROR)
# -
await ctx.message.channel.send(embed=embed_fail)
return
#No other cells are found
except gspread.exceptions.CellNotFound:
print("No dupes found. Proceed to scheduling event.")
except gspread.exceptions.WorksheetNotFound:
# create embed
embed_fail = discord.Embed(
title=
"Game does not exist. Make sure arguments are in Game, Date, Time order and try again. If that doesn't work, see the addgame command.",
color=RED)
embed_fail.set_author(name="Game not found", icon_url=ERROR)
# -
await ctx.message.channel.send(embed=embed_fail)
print(sys.exc_info()[0])
"""
Can finally start the actual scheduling code. Yay.
Searches for each individual element in the specified game,
if it doesn't exist, create it
"""
try:
sheet = spread.worksheet(game)
neweventid = game[0:2] + game[-2:] + "-" + str(
randint(10000000, 99999999))
cell = sheet.find("Player Name")
for i in range(cell.row,
sheet.row_count): #Looping through all the rows
cell = sheet.cell(i, cell.col)
if (cell.value == ""
): #Get the first cell in that column that's blank
sheet.update_cell(i, cell.col + 1, date)
time_cell = sheet.update_cell(i, cell.col + 2, time)
if (name != ""):
name_cell = sheet.update_cell(i, cell.col + 2, name)
sheet.update_cell(
sheet.find("Event Id").row + i - 1,
sheet.find("Event Id").col, neweventid)
sheet.update_cell(
sheet.find("Num Players").row + i - 1,
sheet.find("Num Players").col, 0)
# create embed
embed_finish = discord.Embed(
title=
"New players can join using the event using the Event ID.",
description="Command to join is {}join [game] [event id]".
format(BOT_PREFIX),
color=GREEN)
embed_finish.add_field(name="Event ID",
value=neweventid,
inline=True)
embed_finish.add_field(name="Game", value=game, inline=True)
embed_finish.set_author(name="Schedule Success",
icon_url=SUCCESS)
# -
await ctx.message.channel.send(embed=embed_finish)
return
date_cell = sheet.find("Date")
time_cell = sheet.find("Time")
print(date_cell, time_cell)
cells_changed = []
for i in range(
date_cell.row,
sheet.row_count): #Looping through all the rows, i is an int
cell = sheet.cell(i, date_cell.col)
print(str(cell))
if (cell.value == ""
): #Get the first cell in the date column that's blank
#Update cells with Date and Time
cells_changed.append([(i, cell.col), (i, time_cell.col)])
print("\n{}".format("cells_changed: {}".format(cells_changed)))
if (name != ""):
name_cell = sheet.find("Name")
cells_changed.append((i, name_cell.col))
print("\n{}".format(cells_changed))
cells = []
for pair, value in cells_changed:
new_cell = gspread.Cell(pair[0], pair[1])
new_cell.value = str(date)
cells.append(new_cell)
new_cell = gspread.Cell(value[0], value[1])
new_cell.value = str(time)
cells.append(new_cell)
print(type(pair))
print("Pair: {}, Value: ".format(pair))
print("Cells: {}".format(cells))
sheet.update_cells(cells, "RAW")
# create embed
embed_info = discord.Embed(
title="Scheduled game night successfully!", color=GREEN)
embed_info.set_author(name="Schedule Success",
icon_url=SUCCESS)
# -
await ctx.message.channel.send(embed=embed_info)
return
except gspread.exceptions.WorksheetNotFound:
# create embed
embed_fail = discord.Embed(
title=
"Game does not exist. Make sure arguments are in Game, Date, Time order and try again. If that doesn't work, see the addgame command.",
color=RED)
embed_fail.set_author(name="Game not found", icon_url=ERROR)
# -
await ctx.message.channel.send(embed=embed_fail)
def main():
parser = argparse.ArgumentParser()
parser.add_argument("--json", required=True)
parser.add_argument("--sheet", required=True)
parser.add_argument("--credentials", default="credentials.json")
parser.add_argument("--start_col", default="1", type=int)
args = parser.parse_args()
credentials = ServiceAccountCredentials.from_json_keyfile_name(
args.credentials, SCOPES)
gcloud = gspread.authorize(credentials)
spreadsheet = gcloud.open_by_url(args.sheet)
with open(args.json) as json_file:
test_results = json.load(json_file)
try:
worksheet = spreadsheet.worksheet(test_results["suite"])
except gspread.exceptions.WorksheetNotFound:
print(" * ERROR: Worksheet {} not found".format(test_results["suite"]))
# could add_worksheet?
sys.exit(1)
worksheet_data = worksheet.get_all_values()
populated_rows = len(worksheet_data)
if populated_rows == 0:
# Blank spreadsheet, row 1 will be for column titles
current_row = 2
else:
current_row = populated_rows + 1
# Columns before start_col reserved for manually entered details
start_col = max(1, args.start_col)
# Columns for Filename, URLs Tested, Timestamp, Test Suite
metadata_cols = 4
# Columns for counts of Tests, and each Test Status
state_cols = 1 + len(TEST_STATES)
# First results column
results_col = start_col + metadata_cols + state_cols
# Column after last results
if populated_rows == 0:
# Blank spreadsheet
next_col = results_col
else:
next_col = max(results_col, len(worksheet_data[0]) + 1)
# Test Names
start_cell_addr = gspread.utils.rowcol_to_a1(1, 1)
end_cell_addr = gspread.utils.rowcol_to_a1(1, next_col)
cell_list_names = worksheet.range("{}:{}".format(start_cell_addr,
end_cell_addr))[:-1]
# Results
start_cell_addr = gspread.utils.rowcol_to_a1(current_row, 1)
end_cell_addr = gspread.utils.rowcol_to_a1(current_row, next_col)
cell_list_results = worksheet.range("{}:{}".format(start_cell_addr,
end_cell_addr))[:-1]
# Columns for Filename, URLs Tested, Timestamp, Test Suite
current_index = start_col - 1 # list is 0-indexed whereas rows/cols are 1-indexed
cell_list_names[current_index].value = "Filename"
cell_list_results[current_index].value = args.json
current_index += 1
cell_list_names[current_index].value = "URLs Tested"
try:
urls_tested = []
for endpoint in test_results["endpoints"]:
urls_tested.append("{}:{} ({})".format(endpoint["host"],
endpoint["port"],
endpoint["version"]))
cell_list_results[current_index].value = ", ".join(urls_tested)
except Exception:
print(" * WARNING: JSON file does not include endpoints")
cell_list_results[current_index].value = test_results["url"]
current_index += 1
cell_list_names[current_index].value = "Timestamp"
cell_list_results[current_index].value = (
datetime.datetime.utcfromtimestamp(
test_results["timestamp"]).strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3] +
'Z')
current_index += 1
cell_list_names[current_index].value = "Test Suite"
try:
cell_list_results[current_index].value = "{} ({})".format(
test_results["suite"], test_results["config"]["VERSION"])
except Exception:
print(" * WARNING: JSON file does not include test suite version")
cell_list_results[current_index].value = test_results["suite"]
# Columns for counts of Tests and each Test Status
results_addr = "{}:{}".format(
gspread.utils.rowcol_to_a1(current_row, results_col),
gspread.utils.rowcol_to_a1(current_row, 1)[1:])
current_index += 1
cell_list_names[current_index].value = "Tests"
# count non-empty cells on rest of this row
cell_list_results[current_index].value = "=COUNTIF({}, \"?*\")".format(
results_addr)
for state in TEST_STATES:
current_index += 1
cell_list_names[current_index].value = state
# count cells on the rest of this row that match this column's status
current_col_addr = gspread.utils.rowcol_to_a1(
1, cell_list_names[current_index].col)
cell_list_results[current_index].value = "=COUNTIF({}, CONCAT({},\"*\"))" \
.format(results_addr, current_col_addr)
# Columns for the Results
for result in test_results["results"]:
cell_contents = result["state"]
if result["detail"] != "":
cell_contents += " (" + result["detail"] + ")"
col = next(
(cell.col
for cell in cell_list_names if cell.value == result["name"]),
None)
if col:
index = col - 1 # list is 0-indexed whereas rows/cols are 1-indexed
cell_list_results[index].value = cell_contents
else:
# Test name not found, append column (since gspread doesn't make it easy to insert one)
col = cell_list_names[-1].col + 1 # = cell_list_results[-1].col+1
cell_list_names.append(gspread.Cell(1, col, result["name"]))
cell_list_results.append(
gspread.Cell(current_row, col, cell_contents))
worksheet.update_cells(cell_list_names)
# 'USER_ENTERED' allows formulae to be used
worksheet.update_cells(cell_list_results,
value_input_option='USER_ENTERED')
def update_worksheet(worksheet, lines_range=(2,20)):
"""
Reads the lines in range, and query wikipedia + wikidata for the entity (fixed) name, update the rest of the fields in the row.
Batch spreadsheet update (instead of a row-by-row.)
"""
from read_wiki import fix_entry_name_options
print("starting to update obudget_wikipedia_categories_table")
update_obudget_wikipedia_categories_table() # update the relevant wikipedia categories lists per obudget 'he_kind'
print("done.")
# define the range in the spreadsheet to update
start_row = lines_range[0]
end_row = lines_range[1]
print(f"start row: {start_row} end row: {end_row}")
header = worksheet.row_values(1) # get the header line keys
all_cells = []
summaries = []
urls = []
retrieved = {}
number_of_rows = end_row - start_row
for row_index in range(start_row, end_row):
row_values = worksheet.row_values(row_index)
entity_data = {}
for cell_index in range(len(header)):
try:
cell_value = row_values[cell_index]
except:
cell_value = ''
cell_type = header[cell_index]
#print(f"row index {row_index} cell index {cell_index}, cell type {cell_type}")
if cell_type =='id':
pass
elif cell_type =='kind_he':
if cell_value == '' or cell_value == None:
obudget_category = None
else:
obudget_category = cell_value
elif cell_type =='name':
name = cell_value
if name != '' and name != None:
if name in retrieved.keys(): # check if we alrady retrieved this entitiy details
entity_data = retrieved[name]
# print(f"{name} detailes were alrady available locally")
else:
#print(f"searching {name}, with obudget category {obudget_category}")
entity_data = search_wikipedia(name, obudget_category)
# print(f"{name} detailes retrieved")
else:
entity_data = {}
else:
if cell_type in entity_data:
cell_value = str(entity_data[cell_type]) # update the cell value with the retrieved data
#print(f"{name} {cell_type}: {cell_value}")
else:
#print(f"no {cell_type} value for {name}")
cell_value = ''
try:
all_cells.append(gspread.Cell(row_index, cell_index+1, cell_value))
except Exception as e:
print(f"Error while updating row no. {row_index} column {cell_index} for {name}: {e}")
if "wiki_title" in entity_data:
print(f"{row_index}. {name} --> {entity_data['wiki_title']}")
else:
print(f"{row_index}. {name} ---> no wiki entry")
try:
worksheet.update_cells(all_cells) # batch saving on google spreadsheet
print(f"updated google spreadsheet")
except Exception as e:
print(f" Error trying to update google spreadsheet")
return retrieved
def gsheets_import(test_results,
worksheet,
filename,
start_col=1,
insert=False):
"""Upload results data to spreadsheet"""
worksheet_data = worksheet.get_all_values()
populated_rows = len(worksheet_data)
# Columns before start_col reserved for manually entered details
start_col = max(1, start_col)
# Columns for Filename, URLs Tested, Timestamp, Test Suite
metadata_cols = 4
# Columns for counts of Tests, and each Test Status
state_cols = 1 + len(TEST_STATES)
# First results column
results_col = start_col + metadata_cols + state_cols
# Column after last results
if populated_rows == 0:
# Blank spreadsheet
next_col = results_col
else:
next_col = max(results_col, len(worksheet_data[0]) + 1)
# Test Names
cell_list_names = get_range(worksheet_data, 1, 1, 1, next_col - 1)
original_cell_list_names = copy.deepcopy(cell_list_names)
# Results
cell_list_results = [""] * len(cell_list_names)
# Columns for Filename, URLs Tested, Timestamp, Test Suite
current_index = start_col - 1 # list is 0-indexed whereas rows/cols are 1-indexed
cell_list_names[current_index].value = "Filename"
cell_list_results[current_index] = filename
current_index += 1
cell_list_names[current_index].value = "URLs Tested"
try:
urls_tested = []
for endpoint in test_results["endpoints"]:
urls_tested.append("{}:{} ({})".format(endpoint["host"],
endpoint["port"],
endpoint["version"]))
cell_list_results[current_index] = ", ".join(urls_tested)
except Exception:
print(" * WARNING: JSON file does not include endpoints")
cell_list_results[current_index] = test_results["url"]
current_index += 1
cell_list_names[current_index].value = "Timestamp"
cell_list_results[current_index] = (datetime.datetime.utcfromtimestamp(
test_results["timestamp"]).strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3] + 'Z')
current_index += 1
cell_list_names[current_index].value = "Test Suite"
try:
cell_list_results[current_index] = "{} ({})".format(
test_results["suite"], test_results["config"]["VERSION"])
except Exception:
print(" * WARNING: JSON file does not include test suite version")
cell_list_results[current_index] = test_results["suite"]
# Columns for counts of Tests and each Test Status
result_col_name = gspread.utils.rowcol_to_a1(1, results_col)[0:-1]
results_addr = 'INDIRECT("${}"&ROW()&":"&ROW())'.format(result_col_name)
current_index += 1
cell_list_names[current_index].value = "Tests"
# count non-empty cells on rest of this row
cell_list_results[current_index] = "=COUNTIF({}, \"?*\")".format(
results_addr)
for state in TEST_STATES:
current_index += 1
cell_list_names[current_index].value = state
# count cells on the rest of this row that match this column's status
current_col_name = gspread.utils.rowcol_to_a1(
1, cell_list_names[current_index].col)[0:-1]
cell_list_results[current_index] = "=COUNTIF({}, CONCAT({}$1,\"*\"))" \
.format(results_addr, current_col_name)
# Columns for the Results
for result in test_results["results"]:
cell_contents = result["state"]
if result["detail"] != "":
cell_contents += " (" + result["detail"] + ")"
col = next(
(cell.col
for cell in cell_list_names if cell.value == result["name"]),
None)
if col:
index = col - 1 # list is 0-indexed whereas rows/cols are 1-indexed
cell_list_results[index] = cell_contents
else:
# Test name not found, append column (since gspread doesn't make it easy to insert one)
col = cell_list_names[-1].col + 1 # = cell_list_results[-1].col+1
cell_list_names.append(gspread.Cell(1, col, result["name"]))
cell_list_results.append(cell_contents)
if not ranges_equal(original_cell_list_names, cell_list_names):
worksheet.update_cells(cell_list_names)
if insert:
insert_row(worksheet, cell_list_results, 1)
else:
append_row(worksheet, cell_list_results)
select results.brevet_date, distance, title, rider_names, result from results
join riders on results.rider_id = riders.rider_id
join brevets on brevets.brevet_date = results.brevet_date
order by results.brevet_date""")
cells = []
riders = set()
for row_num, row in enumerate(query, 1):
col_num = 0
newbie = 0
for col_num, cell in enumerate(row, 1):
if col_num == 4:
cell = cell.split('|')[-1].split(' ')
cell = cell[0] + ' ' + cell[1].title()
if cell in riders:
newbie = 0
else:
newbie = 1
riders.add(cell)
cells.append(gspread.Cell(row_num, col_num, cell))
cells.append(gspread.Cell(row_num, col_num + 1, newbie))
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open("vssr").worksheet('brm')
sheet.clear()
sheet.update_cells(cells)
async def order(self, ctx, order, *, comment):
"""
Places an order with the GreenSwarm jf service. Format: o.order <evepraisal link> your comments go here.
"""
try:
scope = [
'https://www.googleapis.com/auth/spreadsheets',
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'/home/zoro/OtterBot/google_secret.json', scope)
gc = gspread.authorize(creds)
except APIError as response:
print(response)
try:
shop = gc.open_by_key(
'16dYWGvfwNOkx3Pf27rrp7yJyX6D3zOtkmm2uOj3Nnis').sheet1
except APIError as response:
print(response)
def next_available_row(shop):
row_filter = list(filter(None, shop.col_values(1)))
row_num = len(row_filter) + 1
return row_num
if re.match('^.*evepraisal.com\/a\/.*$', order):
jOrder = order + '.json'
with urllib.request.urlopen(f'{jOrder}') as url:
orderData = json.loads(url.read().decode())
itemData = orderData['items']
purchaserID = ctx.message.author.display_name
item_cell = []
row_num = next_available_row(shop)
for item in itemData:
item_cell.append(gspread.Cell(row_num, 1, item['name']))
item_cell.append(gspread.Cell(row_num, 2,
item['quantity']))
item_cell.append(
gspread.Cell(row_num, 3, item['prices']['all']['avg']))
item_cell.append(gspread.Cell(row_num, 4, purchaserID))
item_cell.append(gspread.Cell(row_num, 5, f'{comment}'))
row_num += 1
try:
shop.update_cells(item_cell)
order_gap = []
order_gap.append(
gspread.Cell(next_available_row(shop), 1, '-'))
shop.update_cells(order_gap)
except Exception as err:
print(err)
return await ctx.message.add_reaction(chr(0x1f44d))
elif re.match('^.*zkillboard.com\/kill\/.*$', order):
match = re.findall('\d\d\d\d\d\d\d\d', order)
zkillID = match[0]
jkOrder = f'https://zkillboard.com/api/killID/{zkillID}/'
with urllib.request.urlopen(f'{jkOrder}') as url:
orderData = json.loads(url.read().decode())
ekillID = orderData[0]['killmail_id']
ekillHash = orderData[0]['zkb']['hash']
try:
kill_op = self.bot.esi.esi.get_operation(
'get_killmails_killmail_id_killmail_hash')
killsheet = kill_op.json(killmail_id=ekillID,
killmail_hash=ekillHash)
except Exception as e:
print(e)
kill_items = killsheet['victim']['items']
ship = killsheet['victim']['ship_type_id']
item_cell = []
row_num = next_available_row(shop)
for item_type_id in kill_items:
print(item_type_id)
return await ctx.message.add_reaction(chr(0x1f44d))
else:
return await ctx.send('Order must be an Evepraisal or Zkill link.')
if __name__ == '__main__':
# previous version was using the import_csv function from gspread but this was conflicting tiwht google studio as it seems to delete the spreadsheet sheets and create a new one each time, breaking links internal to google studio. We use a batch cells approach now, which works. Reminder of old methods below:
# content = open(CSV_FILENAME, 'r').read().encode('utf-8')
# gc.import_csv(SPREADSHEET_ID, content)
# load csv file
df = pd.read_csv(CSV_FILENAME)
cells = []
# https://github.com/burnash/gspread/issues/515
for n_column, header in enumerate(df.keys()):
# first row is header
cells.append(gspread.Cell(1, n_column + 1, header))
for n_row, value in enumerate(df[header]):
if pd.isna(value):
value = ''
else:
value = str(value)
cells.append(gspread.Cell(n_row + 2, n_column + 1, value))
# the size of the dataset can only increase in theory using current pipeline so we do not bother looking into the spreadsheet, etc, we just overwrite
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILENAME, scope)
def gsheets_import(test_results, worksheet, filename, start_col=1):
"""Upload results data to spreadsheet"""
worksheet_data = worksheet.get_all_values()
populated_rows = len(worksheet_data)
if populated_rows == 0:
# Blank spreadsheet, row 1 will be for column titles
current_row = 2
else:
current_row = populated_rows + 1
# Columns before start_col reserved for manually entered details
start_col = max(1, start_col)
# Columns for Filename, URLs Tested, Timestamp, Test Suite
metadata_cols = 4
# Columns for counts of Tests, and each Test Status
state_cols = 1 + len(TEST_STATES)
# First results column
results_col = start_col + metadata_cols + state_cols
# Column after last results
if populated_rows == 0:
# Blank spreadsheet
next_col = results_col
else:
next_col = max(results_col, len(worksheet_data[0]) + 1)
# Test Names
start_cell_addr = gspread.utils.rowcol_to_a1(1, 1)
end_cell_addr = gspread.utils.rowcol_to_a1(1, next_col)
cell_list_names = worksheet.range("{}:{}".format(start_cell_addr,
end_cell_addr))[:-1]
# Results
start_cell_addr = gspread.utils.rowcol_to_a1(current_row, 1)
end_cell_addr = gspread.utils.rowcol_to_a1(current_row, next_col)
cell_list_results = worksheet.range("{}:{}".format(start_cell_addr,
end_cell_addr))[:-1]
# Columns for Filename, URLs Tested, Timestamp, Test Suite
current_index = start_col - 1 # list is 0-indexed whereas rows/cols are 1-indexed
cell_list_names[current_index].value = "Filename"
cell_list_results[current_index].value = filename
current_index += 1
cell_list_names[current_index].value = "URLs Tested"
try:
urls_tested = []
for endpoint in test_results["endpoints"]:
urls_tested.append("{}:{} ({})".format(endpoint["host"],
endpoint["port"],
endpoint["version"]))
cell_list_results[current_index].value = ", ".join(urls_tested)
except Exception:
print(" * WARNING: JSON file does not include endpoints")
cell_list_results[current_index].value = test_results["url"]
current_index += 1
cell_list_names[current_index].value = "Timestamp"
cell_list_results[current_index].value = (
datetime.datetime.utcfromtimestamp(
test_results["timestamp"]).strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3] +
'Z')
current_index += 1
cell_list_names[current_index].value = "Test Suite"
try:
cell_list_results[current_index].value = "{} ({})".format(
test_results["suite"], test_results["config"]["VERSION"])
except Exception:
print(" * WARNING: JSON file does not include test suite version")
cell_list_results[current_index].value = test_results["suite"]
# Columns for counts of Tests and each Test Status
results_addr = "{}:{}".format(
gspread.utils.rowcol_to_a1(current_row, results_col),
gspread.utils.rowcol_to_a1(current_row, 1)[1:])
current_index += 1
cell_list_names[current_index].value = "Tests"
# count non-empty cells on rest of this row
cell_list_results[current_index].value = "=COUNTIF({}, \"?*\")".format(
results_addr)
for state in TEST_STATES:
current_index += 1
cell_list_names[current_index].value = state
# count cells on the rest of this row that match this column's status
current_col_addr = gspread.utils.rowcol_to_a1(
1, cell_list_names[current_index].col)
cell_list_results[current_index].value = "=COUNTIF({}, CONCAT({},\"*\"))" \
.format(results_addr, current_col_addr)
# Columns for the Results
for result in test_results["results"]:
cell_contents = result["state"]
if result["detail"] != "":
cell_contents += " (" + result["detail"] + ")"
col = next(
(cell.col
for cell in cell_list_names if cell.value == result["name"]),
None)
if col:
index = col - 1 # list is 0-indexed whereas rows/cols are 1-indexed
cell_list_results[index].value = cell_contents
else:
# Test name not found, append column (since gspread doesn't make it easy to insert one)
col = cell_list_names[-1].col + 1 # = cell_list_results[-1].col+1
cell_list_names.append(gspread.Cell(1, col, result["name"]))
cell_list_results.append(
gspread.Cell(current_row, col, cell_contents))
worksheet.update_cells(cell_list_names)
# 'USER_ENTERED' allows formulae to be used
worksheet.update_cells(cell_list_results,
value_input_option='USER_ENTERED')
def to_cells(rows):
cells = ([
gspread.Cell(row=i, col=j, value=v) for j, v in enumerate(row, 1)
] for i, row in enumerate(rows, 1))
return [cell for row in cells for cell in row] # flatten
