async def refresh_footer(self):
"""Refresh the self.footer_row property from the GSheet"""
async with Spreadsheets() as spreadsheets:
# Find the header row and the column indicies
row_query_min = 1
row_query_max = 10
while self.footer_row is None and row_query_min <= self._sheet_size[0]:
# Get the cells
range_to_get = SheetRange(
ul_cell=(row_query_min, 1,),
lr_cell=(row_query_max, self._sheet_size[1],),
wks_name=self.wks_name
)
request = spreadsheets.values().get(
spreadsheetId=self.gsheet_id,
range=range_to_get,
majorDimension='ROWS'
)
value_range = await make_request(request)
# Check if the cells we got are completely empty
if 'values' not in value_range:
if self.header_row is not None:
self.footer_row = row_query_min
# If there are values in the cells, find header and footers
else:
values = value_range['values']
# If we got fewer than the requested number of rows, we've found the footer
if len(values) < row_query_max - row_query_min + 1:
self.footer_row = row_query_min + len(values)
# Prepare for next loop
row_query_min = row_query_max + 1
row_query_max = min(2*row_query_max, self._sheet_size[0])
async def overwrite_gsheet(self):
await self.column_data.refresh_all()
header_row = ['Run ID', 'Verified', 'Racer', 'Category', 'Time', 'Date', 'Vod']
# Get the match data
speedrun_data = await speedrundb.get_raw_data()
# Construct the SheetRange to update
range_to_update = SheetRange(
ul_cell=(1, 1),
lr_cell=(len(speedrun_data) + 1, len(header_row)),
wks_name=self.wks_name,
)
# Construct the value array to place in the sheet
values = [header_row]
for raw_entry in speedrun_data:
run_id = raw_entry[0]
user_id = raw_entry[1]
run_type_id = raw_entry[2]
run_time = raw_entry[3]
vod_url = raw_entry[4]
submission_time = raw_entry[5]
verified_bool = raw_entry[6]
# Convert user ID to a username
racer_user = await userlib.get_user(user_id=user_id)
# Convert run type to a string
race_info = await racedb.get_race_info_from_type_id(race_type=run_type_id)
race_info_str = race_info.descriptor
# Convert run time to a string; note GSheet ' operator for inputting as raw (no conversion to a datetime)
run_time_str = "'{}".format(categories.convert_score_to_text(race_info.descriptor, run_time))
# Convert submission time to string
if submission_time is None:
submission_time_str = ''
else:
submission_time_str = pytz.utc.localize(submission_time).astimezone(pytz.timezone('US/Eastern'))\
.strftime('%Y-%m-%d %H:%M:%S')
# Convert verified info to string
verified_str = 'Yes' if verified_bool else 'No'
values.append([
run_id,
verified_str,
racer_user.display_name,
race_info_str,
run_time_str,
submission_time_str,
vod_url,
])
await self.column_data.update_cells(
sheet_range=range_to_update,
values=values,
raw_input=False
)
async def overwrite_gsheet(self):
await self.column_data.refresh_all()
header_row = ['Match ID', 'Autogenned', 'Racer 1', 'Racer 2', 'Date', 'Winner', 'Score', 'Cawmentary', 'Vod']
# Get the match data
matchview_data = await matchdb.get_matchview_raw_data()
# Construct the SheetRange to update
range_to_update = SheetRange(
ul_cell=(1, 1),
lr_cell=(len(matchview_data) + 1, len(header_row)),
wks_name=self.wks_name,
)
# Construct the value array to place in the sheet
values = [header_row]
for raw_match in matchview_data:
cawmentator_name = raw_match[4]
cawmentator_str = 'twitch.tv/{0}'.format(cawmentator_name) if cawmentator_name else ''
if raw_match[3] is None or not raw_match[10]:
time_str = ''
else:
time_str = pytz.utc.localize(raw_match[3]).astimezone(pytz.timezone('US/Eastern'))\
.strftime('%Y-%m-%d %H:%M:%S')
if raw_match[7]: # completed
if raw_match[5] >= raw_match[6]:
winner_str = raw_match[1]
score_str = "'{r1}-{r2}".format(r1=raw_match[5], r2=raw_match[6])
else:
winner_str = raw_match[2]
score_str = "'{r2}-{r1}".format(r1=raw_match[5], r2=raw_match[6])
else:
winner_str = ''
score_str = ''
autogen_str = 'Auto-gen' if raw_match[9] else 'Challenge'
values.append([
raw_match[0],
autogen_str,
raw_match[1],
raw_match[2],
time_str,
winner_str,
score_str,
cawmentator_str,
raw_match[8] if raw_match[8] is not None else '',
])
await self.column_data.update_cells(
sheet_range=range_to_update,
values=values,
raw_input=False
)
def get_range(self, left, right, top, bottom) -> SheetRange:
return SheetRange(ul_cell=(
self.header_row + top + 1,
self.min_column + left,
),
lr_cell=(
self.header_row + bottom + 1,
self.min_column + right,
),
wks_name=self.wks_name)
def full_range_extend_right(self):
return SheetRange(ul_cell=(
self.header_row + 1,
self.min_column,
),
lr_cell=(
self.footer_row - 1,
self._sheet_size[1],
),
wks_name=self.wks_name)
def full_range(self):
return SheetRange(ul_cell=(
self.header_row + 1,
self.min_column,
),
lr_cell=(
self.footer_row - 1,
self.max_column,
),
wks_name=self.wks_name)
async def _refresh(self, spreadsheets):
"""Find the array bounds and the column indicies"""
# Find the header row and the column indicies
row_query_min = 1
row_query_max = 10
col_vals = []
while self.footer_row is None and row_query_min <= self._sheet_size[0]:
# Get the cells
range_to_get = SheetRange(ul_cell=(
row_query_min,
1,
),
lr_cell=(
row_query_max,
self._sheet_size[1],
),
wks_name=self.wks_name)
request = spreadsheets.values().get(spreadsheetId=self.gsheet_id,
range=range_to_get,
majorDimension='ROWS')
value_range = await make_request(request)
# Check if the cells we got are completely empty
if 'values' not in value_range:
if self.header_row is not None:
self.footer_row = row_query_min
# If there are values in the cells, find header and footers
else:
values = value_range['values']
for row, row_values in enumerate(values):
row += 1
if self.header_row is None:
for col, cell_value in enumerate(row_values):
col += 1
if self._make_index(cell_value, col):
self.header_row = row
col_vals.append(col)
# If we got fewer than the requested number of rows, we've found the footer
if len(values) < row_query_max - row_query_min + 1:
self.footer_row = row_query_min + len(values)
# Prepare for next loop
row_query_min = row_query_max + 1
row_query_max = min(2 * row_query_max, self._sheet_size[0])
if col_vals:
self.min_column = min(self.min_column, min(
col_vals)) if self.min_column is not None else min(col_vals)
self.max_column = max(self.max_column, max(
col_vals)) if self.max_column is not None else max(col_vals)
async def overwrite_gsheet(self, league_tag: str) -> None:
raw_data = await leaguedb.get_standings_data_raw(league_tag=league_tag)
racers = set()
record = defaultdict(lambda: [0, 0])
for row in raw_data:
if row[0] is not None and row[1] is not None:
r1_name = row[0].lower()
r2_name = row[1].lower()
racers.add(r1_name)
racers.add(r2_name)
record[(r1_name, r2_name)][0] += row[2]
record[(r1_name, r2_name)][1] += row[3]
record[(r2_name, r1_name)][0] += row[3]
record[(r2_name, r1_name)][1] += row[2]
racers = sorted(racers)
values = [[''] + list(x for x in racers)]
for racer in racers:
values.append([racer] + ['' for _ in range(len(racers))])
for the_racers, the_record in record.items():
idx = racers.index(the_racers[0]) + 1
jdx = racers.index(the_racers[1]) + 1
values[idx][jdx] = '{wins}-{losses}'.format(wins=the_record[0],
losses=the_record[1])
# Construct the SheetRange to update
range_to_update = SheetRange(
ul_cell=(1, 1),
lr_cell=(len(racers) + 1, len(racers) + 1),
wks_name=self.wks_name,
)
await self._update_cells(sheet_range=range_to_update,
values=values,
raw_input=True)