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 update_cells(self, sheet_range: SheetRange, values: list, raw_input=True) -> bool:
"""Update all cells in a range.
Parameters
----------
sheet_range: SheetRange
The range to update.
values: list[list[str]]
An array of values; one of the inner lists is a row, so values[i][j] is the ith row, jth column value.
raw_input
If False, GSheets will auto-format the input.
Returns
-------
bool
True if the update was successful.
"""
if not self.valid:
raise RuntimeError('Trying to update a cell on an invalid MatchupSheet.')
range_str = str(sheet_range)
value_input_option = 'RAW' if raw_input else 'USER_ENTERED'
value_range_body = {'values': values}
async with Spreadsheets() as spreadsheets:
request = spreadsheets.values().update(
spreadsheetId=self.gsheet_id,
range=range_str,
valueInputOption=value_input_option,
body=value_range_body
)
response = await make_request(request)
return response is not None
async def initialize(self,
wks_name: str = None,
wks_id: str = None) -> None:
colnames = list()
async with Spreadsheets() as spreadsheets:
# Get names of other spreadsheets
request = spreadsheets.get(spreadsheetId=self.gsheet_id)
sheet_data = await make_request(request)
for sheet in sheet_data['sheets']:
props = sheet['properties']
if (wks_name is not None and props['title'] != wks_name) \
or (wks_id is not None and props['sheetId'] != str(wks_id)):
colnames.append(props['title'])
colnames.extend(['racer', 'results'])
self.column_data.reset(columns=colnames)
await self.column_data.initialize(wks_name=wks_name, wks_id=wks_id)
min_col = None
for colname in colnames:
if colname == 'racer' or colname == 'results':
continue
if self.column_data.getcol(colname) is not None:
min_col = min(min_col, self.column_data.getcol(colname)) if min_col is not None \
else self.column_data.getcol(colname)
self._offset = min_col - self.column_data.results
async def has_read_write_permissions(gsheet_id: str) -> (bool, str):
"""Checks that the bot has read/write permissions to the GSheet.
Parameters
----------
gsheet_id: str
The GSheet ID.
Returns
-------
bool
True if the bot has read and write permissions, False otherwise
str
The name of the sheet if the bot has permissions, or an error message otherwise
"""
if gsheet_id is None:
raise necrobot.exception.BadInputException(
'Failed to provide a GSheet ID.')
async with Spreadsheets() as spreadsheets:
request = spreadsheets.get(spreadsheetId=gsheet_id)
try:
spreadsheet = await make_request(request)
return True, spreadsheet['properties']['title']
except googleapiclient.errors.HttpError as e:
# noinspection PyProtectedMember
e_as_str = e._get_reason()
return False, e_as_str if e_as_str else 'Unknown error.'
async def initialize(self,
wks_name: str = None,
wks_id: str = None) -> None:
async with Spreadsheets() as spreadsheets:
request = spreadsheets.get(spreadsheetId=self.gsheet_id)
sheet_data = await make_request(request)
for sheet in sheet_data['sheets']:
props = sheet['properties']
if wks_name is not None and props['title'] == wks_name:
self.wks_name = wks_name
self.wks_id = props['sheetId']
self._sheet_size = (
int(props['gridProperties']['rowCount']),
int(props['gridProperties']['columnCount']),
)
break
elif wks_id is not None and props['sheetId'] == wks_id:
self.wks_name = props['title']
self.wks_id = wks_id
self._sheet_size = (
int(props['gridProperties']['rowCount']),
int(props['gridProperties']['columnCount']),
)
break
if self.wks_id is None:
self.wks_id = 0
async def initialize(self, wks_name: str, wks_id: str) -> None:
"""Read the GSheet and store the indicies of columns
Parameters
----------
wks_name: str
The name of the worksheet to initialize from.
wks_id: int
The ID of the worksheet to initialize from.
Raises
------
googleapiclient.error.Error
Uncaught Google API error.
AlreadyInitializedException
If we've already called initialize() on this object
NotFoundException
If cannot find a worksheet with name wks_name
"""
if self.wks_id is not None:
raise necrobot.exception.AlreadyInitializedExecption(
'Worksheet already initialized <wks_name = {]> <wks_id = {}>'.
format(self.wks_name, self.wks_id))
async with Spreadsheets() as spreadsheets:
# Find the size of the worksheet
request = spreadsheets.get(spreadsheetId=self.gsheet_id)
sheet_data = await make_request(request)
for sheet in sheet_data['sheets']:
props = sheet['properties']
if wks_name is not None and props['title'] == wks_name:
self.wks_name = wks_name
self.wks_id = props['sheetId']
self._sheet_size = (
int(props['gridProperties']['rowCount']),
int(props['gridProperties']['columnCount']),
)
break
elif wks_id is not None and props['sheetId'] == wks_id:
self.wks_name = props['title']
self.wks_id = wks_id
self._sheet_size = (
int(props['gridProperties']['rowCount']),
int(props['gridProperties']['columnCount']),
)
break
if self.wks_id is None:
self.wks_id = 0
# Old code, we're doing hacky stuff for S8 tho
# raise necrobot.exception.NotFoundException(
# "No worksheet with name {wks_name} on GSheet {gsheetid}".format(
# wks_name=wks_name,
# gsheetid=self.gsheet_id
# )
# )
await self._refresh(spreadsheets)
async def _get_match_row(self, match: Match) -> int or None:
"""Get the index of the row containing the Match.
Parameters
----------
match: Match
Returns
-------
Optional[int]
The row index (from 0) of the Match, or None if nothing found.
Raises
------
IncorrectWksException
If the sheetID for this sheet doesn't match the match's sheetID
"""
if match.sheet_id is not None and match.sheet_id != self.wks_id:
raise necrobot.exception.IncorrectWksException(
'Trying to find match {matchname}, but using incorrect MatchupSheet object '
'(sheetID: {sheetid}, name: {sheetname})'.format(
matchname=match.matchroom_name,
sheetid=self.wks_id,
sheetname=self.wks_name
)
)
if match.sheet_id is not None and match.sheet_row is not None:
return match.sheet_row
async with Spreadsheets() as spreadsheets:
value_range = await self.column_data.get_values(spreadsheets)
if 'values' not in value_range:
return None
match_regex_1 = match.racer_1.name_regex
match_regex_2 = match.racer_2.name_regex
values = value_range['values']
for row, row_values in enumerate(values):
gsheet_name_1 = row_values[self.column_data.racer_1]
gsheet_name_2 = row_values[self.column_data.racer_2]
if (match_regex_1.match(gsheet_name_1) and match_regex_2.match(gsheet_name_2)) \
or (match_regex_1.match(gsheet_name_2) and match_regex_2.match(gsheet_name_1)):
return row
console.warning('Couldn\'t find match {0}-{1} on the GSheet.'.format(
match.racer_1.rtmp_name,
match.racer_2.rtmp_name
))
return None
async def refresh_all(self):
"""Refresh all data"""
async with Spreadsheets() as spreadsheets:
# Find the size of the worksheet
request = spreadsheets.get(spreadsheetId=self.gsheet_id)
sheet_data = await make_request(request)
for sheet in sheet_data['sheets']:
props = sheet['properties']
if props['sheetId'] == self.wks_id:
self.wks_name = props['title']
self._sheet_size = (int(props['gridProperties']['rowCount']),
int(props['gridProperties']['columnCount']),)
break
await self._refresh_all(spreadsheets)
async def update_cell(self,
row: int,
col: int,
value: str,
raw_input: bool = True) -> bool:
"""Update a single cell.
Parameters
----------
row: int
The row index (begins at 0).
col: int
The column index (begins at 0).
value: str
The cell value.
raw_input: bool
If False, GSheets will auto-format the input.
Returns
-------
bool
True if the update was successful.
"""
if not self.valid:
raise RuntimeError(
'Trying to update a cell on an invalid MatchupSheet.')
row += self.header_row + 1
col += self.min_column
range_str = str(SheetCell(row, col, wks_name=self.wks_name))
value_input_option = 'RAW' if raw_input else 'USER_ENTERED'
value_range_body = {'values': [[value]]}
async with Spreadsheets() as spreadsheets:
request = spreadsheets.values().update(
spreadsheetId=self.gsheet_id,
range=range_str,
valueInputOption=value_input_option,
body=value_range_body)
response = await make_request(request)
return response is not None
async def _get_match_cells(self, match: Match) \
-> typing.Tuple[typing.Optional[int], typing.Optional[int], typing.Optional[int], typing.Optional[int]]:
"""Get the cells to update for standings for the match.
Parameters
----------
match: Match
Returns
-------
Tuple[int, int, int, int]
The first pair is the (row, col) of racer 1's standings row, match against racer 2, and the second pair
vice versa.
"""
if match.sheet_id is None:
console.warning(
'Trying to update standings for match {0} fails because this match has no sheetID.'
.format(match.matchroom_name))
return None, None, None, None
match_dupe_number = await matchdb.get_match_gsheet_duplication_number(
match)
async with Spreadsheets() as spreadsheets:
# Get the column name for this match
colname = None
request = spreadsheets.get(spreadsheetId=self.gsheet_id)
sheet_data = await make_request(request)
for sheet in sheet_data['sheets']:
props = sheet['properties']
if match.sheet_id == props['sheetId']:
colname = props['title']
if colname is None:
console.warning(
'Trying to get cells for match {0} fails because the sheet corresponding to its sheetID '
'could not be found.'.format(match.matchroom_name))
return None, None, None, None
if self.column_data.getcol(colname) is None:
console.warning(
'Trying to get cells for match {0} fails because the column corresponding to its worksheet '
'("{1}") could not be found.'.format(
match.matchroom_name, colname))
return None, None, None, None
value_range = await self.column_data.get_values(spreadsheets,
extend_right=True)
if 'values' not in value_range:
return None, None, None, None
r1_row = None
r2_row = None
r1_col = None
r2_col = None
values = value_range['values']
for row, row_values in enumerate(values):
gsheet_name = row_values[self.column_data.racer]
if match.racer_1.name_regex.match(gsheet_name):
r1_row = row
match_dupe_1 = match_dupe_number
for col in range(self.column_data.getcol(colname),
len(row_values)):
if match.racer_2.name_regex.match(row_values[col]):
match_dupe_1 -= 1
if match_dupe_1 < 0:
r1_col = col
break
elif match.racer_2.name_regex.match(gsheet_name):
r2_row = row
match_dupe_2 = match_dupe_number
for col in range(self.column_data.getcol(colname),
len(row_values)):
if match.racer_1.name_regex.match(row_values[col]):
match_dupe_2 -= 1
if match_dupe_2 < 0:
r2_col = col
break
return r1_row, r1_col, r2_row, r2_col
async def get_matches(self, **kwargs):
"""Read racer names and match types from the GSheet; create corresponding matches.
Parameters
----------
kwargs:
Parameters to be passed to matchutil.make_match for every match made.
Returns
-------
list[Match]
The list of created Matches.
"""
console.debug('get_matches begin...')
await self.column_data.refresh_footer()
matches = []
self._not_found_matches = []
write_match_ids = self.column_data.match_id is not None and 'register' in kwargs and kwargs[
'register']
match_ids = []
async with Spreadsheets() as spreadsheets:
value_range = await self.column_data.get_values(spreadsheets)
console.debug('get_matches: Got values from spreadsheets.')
if 'values' not in value_range:
console.debug('get_matches: Values is empty.')
return matches
else:
console.debug('get_matches: Values: {0}'.format(
value_range['values']))
for row_idx, row_values in enumerate(value_range['values']):
try:
racer_1_name = row_values[self.column_data.racer_1].rstrip(
' ')
racer_2_name = row_values[self.column_data.racer_2].rstrip(
' ')
except IndexError:
console.warning(
'Failed to make match from sheet row: <{}>'.format(
row_values))
continue
if not racer_1_name or not racer_2_name:
continue
# if racer_1_name[0] not in string.ascii_letters or racer_2_name[0] not in string.ascii_letters:
# self._not_found_matches.append('{0}-{1}'.format(racer_1_name, racer_2_name))
# continue
console.debug('get_matches: Creating {0}-{1}'.format(
racer_1_name, racer_2_name))
racer_1 = await userlib.get_user(any_name=racer_1_name,
register=True)
racer_2 = await userlib.get_user(any_name=racer_2_name,
register=True)
if racer_1 is None or racer_2 is None:
console.warning(
'Couldn\'t find racers for match {0}-{1}.'.format(
racer_1_name, racer_2_name))
self._not_found_matches.append('{0}-{1}'.format(
racer_1_name, racer_2_name))
continue
sheet_info = MatchGSheetInfo()
sheet_info.wks_id = self.wks_id
sheet_info.row = row_idx
kwarg_copy = kwargs.copy()
if self.column_data.type is not None:
match_info = kwarg_copy[
'match_info'] if 'match_info' in kwargs else matchinfo.MatchInfo(
)
try:
parsed_args = shlex.split(
row_values[self.column_data.type])
kwarg_copy['match_info'] = matchinfo.parse_args_modify(
parsed_args, match_info)
except IndexError:
pass
new_match = await matchutil.make_match(
racer_1_id=racer_1.user_id,
racer_2_id=racer_2.user_id,
gsheet_info=sheet_info,
**kwarg_copy)
matches.append(new_match)
console.debug('get_matches: Created {0}-{1}'.format(
new_match.racer_1.rtmp_name, new_match.racer_2.rtmp_name))
if write_match_ids:
match_ids.append([new_match.match_id])
if write_match_ids:
ids_range = self.column_data.get_range_for_column(
self.column_data.match_id)
await self.column_data.update_cells(sheet_range=ids_range,
values=match_ids,
raw_input=True)
console.debug('get_matches: Returning Matches=<{}>'.format(matches))
return matches
