def findAssociatedTab(spreadsheetApp, access_control_spreadsheet_id,
user_id):
"""Return the symbolic name of the user, to which the specified user ID (such as a Discord snowflake ID) is bound.
In the context of the WOTV bot, this is also the name of the user-specific tabs in any/all associated spreadsheets.
If the ID can't be found, an exception is raised with a safe error message that can be shown publicly in Discord.
"""
# Discord IDs are in column A, the associated tab name is in column B
range_name = WorksheetUtils.safeWorksheetName(
AdminUtils.USERS_TAB_NAME) + '!A:B'
rows = None
try:
values = spreadsheetApp.values().get(
spreadsheetId=access_control_spreadsheet_id,
range=range_name).execute()
rows = values.get('values', [])
if not rows:
raise Exception('')
except:
# pylint: disable=raise-missing-from
raise ExposableException(
'Spreadsheet misconfigured'
) # deliberately low on details as this is replying in Discord.
for row in rows:
if str(row[0]) == str(user_id):
return row[1]
raise ExposableException(
'User with ID {0} is not configured, or is not allowed to access this data. Ask your guild administrator for assistance.'
.format(user_id))
def addUnitRow(self, user_id: str, unit_name: str, unit_url: str,
above_or_below: str, row_1_based: str, sandbox: str):
"""Add a new row for a unit.
The above_or_below parameter needs to be either the string 'above' or 'below'. The row should be in 1-based notation,
i.e. the first row is row 1, not row 0.
If sandbox is True, uses a sandbox sheet so that the admin can ensure the results are good before committing to everyone.
"""
if not AdminUtils.isAdmin(self.spreadsheet_app,
self.access_control_spreadsheet_id, user_id):
raise ExposableException(
'You do not have permission to add a unit.')
target_spreadsheet_id = None
if sandbox:
target_spreadsheet_id = self.sandbox_esper_resonance_spreadsheet_id
else:
target_spreadsheet_id = self.esper_resonance_spreadsheet_id
spreadsheet = self.spreadsheet_app.get(
spreadsheetId=target_spreadsheet_id).execute()
allRequests = WorksheetUtils.generateRequestsToAddRowToAllSheets(
spreadsheet,
int(row_1_based),
above_or_below,
True, # Set a header column...
'B', # ... On the second column (A1 notation)
unit_name, # With text content being the unit name
unit_url) # As a hyperlink to the unit URL
requestBody = {'requests': [allRequests]}
# Execute the whole thing as a batch, atomically, so that there is no possibility of partial update.
self.spreadsheet_app.batchUpdate(spreadsheetId=target_spreadsheet_id,
body=requestBody).execute()
return
def searchVisionCardsByAbility(self, user_name: str, user_id: str, search_text: str) -> [VisionCard]:
"""Search for and return all VisionCards matching the specified search text, for the given user. Returns an empty list if there are no matches.
Set either the user name or the user ID, but not both. If the ID is set, the tab name for the lookup is done as an indirection through
the access control spreadsheet to map the ID of the user to the correct tab. This is best for self-lookups, so that even if a user
changes their own nickname, they are still reading their own data and not the data of, e.g., another user who has their old nickname.
"""
if (user_name is not None) and (user_id is not None):
print('internal error: both user_name and user_id specified. Specify one or the other, not both.')
raise ExposableException('Internal error')
if user_id is not None:
user_name = AdminUtils.findAssociatedTab(self.spreadsheet_app, self.access_control_spreadsheet_id, user_id)
party_ability_row_tuples = WorksheetUtils.fuzzyFindAllRows(
self.spreadsheet_app, self.vision_card_spreadsheet_id, user_name, search_text, 'P', 2)
bestowed_ability_row_tuples = WorksheetUtils.fuzzyFindAllRows(
self.spreadsheet_app, self.vision_card_spreadsheet_id, user_name, search_text, 'Q', 2)
if len(party_ability_row_tuples) == 0 and len(bestowed_ability_row_tuples) == 0:
return []
# Accumulate all the matching rows
all_matching_row_numbers = set()
for (row_number, _) in party_ability_row_tuples:
all_matching_row_numbers.add(row_number)
for (row_number, _) in bestowed_ability_row_tuples:
all_matching_row_numbers.add(row_number)
all_matching_row_numbers = sorted(all_matching_row_numbers)
range_name = WorksheetUtils.safeWorksheetName(user_name) + '!B2:Q' # Fetch everything from below the header row, starting with the name
result = self.spreadsheet_app.values().get(spreadsheetId=self.vision_card_spreadsheet_id, range=range_name).execute()
all_rows = result.get('values', [])
all_matching_vision_cards = []
for row_number in all_matching_row_numbers:
all_matching_vision_cards.append(self.__readVisionCardFromRawRow(all_rows[row_number - 1])) # -1 for the header row
return all_matching_vision_cards
def isAdmin(spreadsheet_app, access_control_spreadsheet_id, user_id):
"""Return True if the specified user id has administrator permissions."""
# Discord IDs are in column A, the associated tab name is in column B, and if 'Admin' is in column C, then it's an admin.
range_name = WorksheetUtils.safeWorksheetName(
AdminUtils.USERS_TAB_NAME) + '!A:C'
rows = None
try:
values = spreadsheet_app.values().get(
spreadsheetId=access_control_spreadsheet_id,
range=range_name).execute()
rows = values.get('values', [])
if not rows:
raise Exception('')
except:
# pylint: disable=raise-missing-from
raise ExposableException(
'Spreadsheet misconfigured'
) # deliberately low on details as this is replying in Discord.
for row in rows:
if str(row[0]) == str(user_id):
result = (len(row) > 2 and row[2]
and row[2].lower() == 'admin')
print('Admin check for user {0}: {1}'.format(user_id, result))
return result
return False
def addEsperColumn(self, user_id: str, esper_name: str, esper_url: str,
left_or_right_of: str, columnA1: str, sandbox: bool):
"""Add a new column for an esper.
The left_or_right_of parameter needs to be either the string 'left-of' or 'right-of'. The column should be in A1 notation.
If sandbox is True, uses a sandbox sheet so that the admin can ensure the results are good before committing to everyone.
"""
if not AdminUtils.isAdmin(self.spreadsheet_app,
self.access_control_spreadsheet_id, user_id):
raise ExposableException(
'You do not have permission to add an esper.')
target_spreadsheet_id = None
if sandbox:
target_spreadsheet_id = self.sandbox_esper_resonance_spreadsheet_id
else:
target_spreadsheet_id = self.esper_resonance_spreadsheet_id
spreadsheet = self.spreadsheet_app.get(
spreadsheetId=target_spreadsheet_id).execute()
allRequests = WorksheetUtils.generateRequestsToAddColumnToAllSheets(
spreadsheet,
columnA1,
left_or_right_of,
True, # Set a header row...
1, # ...On the second row (row index is zero-based)
esper_name, # With text content being the esper name
esper_url) # As a hyperlink to the esper URL
requestBody = {'requests': [allRequests]}
# Execute the whole thing as a batch, atomically, so that there is no possibility of partial update.
self.spreadsheet_app.batchUpdate(spreadsheetId=target_spreadsheet_id,
body=requestBody).execute()
return
def handleAdminAddUser(self, context: CommandContextInfo) -> (str, str):
"""Handle !admin-add-user command to add a new unit to the resonance tracker and the administrative spreadsheet."""
if not AdminUtils.isAdmin(
self.wotv_bot_config.spreadsheet_app,
self.wotv_bot_config.access_control_spreadsheet_id,
context.from_id):
raise ExposableException(
'You do not have permission to add a user.')
match = WotvBotConstants.ADMIN_ADD_USER_PATTERN.match(
context.original_message.content)
snowflake_id = match.group('snowflake_id').strip()
nickname = match.group('nickname').strip()
user_type = match.group('user_type').strip().lower()
is_admin = False
if user_type == 'admin':
is_admin = True
print(
'user add from user {0}#{1}, for snowflake_id {2}, nickname {3}, is_admin {4}'
.format(context.from_name, context.from_discrim, snowflake_id,
nickname, is_admin))
AdminUtils.addUser(self.wotv_bot_config.spreadsheet_app,
self.wotv_bot_config.access_control_spreadsheet_id,
nickname, snowflake_id, is_admin)
context.esper_resonance_manager.addUser(nickname)
context.vision_card_manager.addUser(nickname)
responseText = '<@{0}>: Added user {1}!'.format(
context.from_id, nickname)
return (responseText, None)
def parse(dice_string: str) -> DiceSpec:
"""Parse a string of the form "#d#" where the first number is the number of dice to roll and the second number is the number of sides per die."""
dice_pattern = re.compile(r'^(?P<num_dice>[0-9]+)d(?P<num_sides>[0-9]+)$')
error_addendum = 'Dice rolls look like "2d7", where "2" is the number of dice and "7" is the number of sides per die.'
match = dice_pattern.match(dice_string)
if not match:
raise ExposableException('Not a valid dice roll. ' + error_addendum)
num_dice: int = int(match.group('num_dice'))
if num_dice < 1:
raise ExposableException('Must roll at least 1 die. ' + error_addendum)
num_sides: int = int(match.group('num_sides'))
if num_sides < 2:
raise ExposableException('Dice need to have at least 2 sides. ' + error_addendum)
result = DiceSpec()
result.num_sides = num_sides
result.num_dice = num_dice
return result
def readVisionCardByName(self, user_name: str, user_id: str, vision_card_name: str) -> VisionCard:
"""Read and return a VisionCard containing the stats for the specified vision card name, for the given user.
Set either the user name or the user ID, but not both. If the ID is set, the tab name for the lookup is done as an indirection through
the access control spreadsheet to map the ID of the user to the correct tab. This is best for self-lookups, so that even if a user
changes their own nickname, they are still reading their own data and not the data of, e.g., another user who has their old nickname.
"""
if (user_name is not None) and (user_id is not None):
print('internal error: both user_name and user_id specified. Specify one or the other, not both.')
raise ExposableException('Internal error')
if user_id is not None:
user_name = AdminUtils.findAssociatedTab(self.spreadsheet_app, self.access_control_spreadsheet_id, user_id)
row_number, _ = self.findVisionCardRow(user_name, vision_card_name)
# We have the location. Get the value!
range_name = WorksheetUtils.safeWorksheetName(user_name) + '!B' + str(row_number) + ':Q' + str(row_number)
result = self.spreadsheet_app.values().get(spreadsheetId=self.vision_card_spreadsheet_id, range=range_name).execute()
rows = result.get('values', [])
if not rows:
raise ExposableException('{0} is not tracking any data for vision card {1}'.format(user_name, vision_card_name))
return self.__readVisionCardFromRawRow(rows[0])
def readResonance(self, user_name: str, user_id: str, unit_name: str,
esper_name: str):
"""Read and return the esper resonance, pretty unit name, and pretty esper name for the given (unit, esper) tuple, for the given user.
Set either the user name or the user ID, but not both. If the ID is set, the tab name for the resonance lookup is done the
same way as setResonance - an indirection through the access control spreadsheet is used to map the ID of the user to the
correct tab. This is best for self-lookups, so that even if a user changes their own nickname, they are still reading their own data
and not the data of, e.g., another user who has their old nickname.
"""
if (user_name is not None) and (user_id is not None):
print(
'internal error: both user_name and user_id specified. Specify one or the other, not both.'
)
raise ExposableException('Internal error')
if user_id is not None:
user_name = AdminUtils.findAssociatedTab(
self.spreadsheet_app, self.access_control_spreadsheet_id,
user_id)
esper_column_A1, pretty_esper_name = self.findEsperColumn(
self.esper_resonance_spreadsheet_id, user_name, esper_name)
unit_row, pretty_unit_name = self.findUnitRow(
self.esper_resonance_spreadsheet_id, user_name, unit_name)
# We have the location. Get the value!
range_name = WorksheetUtils.safeWorksheetName(
user_name) + '!' + esper_column_A1 + str(
unit_row) + ':' + esper_column_A1 + str(unit_row)
result = self.spreadsheet_app.values().get(
spreadsheetId=self.esper_resonance_spreadsheet_id,
range=range_name).execute()
final_rows = result.get('values', [])
if not final_rows:
raise ExposableException(
'{0} is not tracking any resonance for esper {1} on unit {2}'.
format(user_name, pretty_esper_name, pretty_unit_name))
return final_rows[0][0], pretty_unit_name, pretty_esper_name
def setVisionCard(self, user_id: str, vision_card: VisionCard) -> None:
"""Copy the vision card data from the specified object into the spreadsheet."""
user_name = AdminUtils.findAssociatedTab(self.spreadsheet_app, self.access_control_spreadsheet_id, user_id)
row_index_1_based, _ = self.findVisionCardRow(user_name, vision_card.Name)
spreadsheet = self.spreadsheet_app.get(spreadsheetId=self.vision_card_spreadsheet_id).execute()
sheet_id = None
for sheet in spreadsheet['sheets']:
sheetTitle = sheet['properties']['title']
if sheetTitle == user_name:
sheet_id = sheet['properties']['sheetId']
break
if sheet_id is None:
raise ExposableException(
'Internal error: sheet not found for {0}.'.format(user_name))
# Columns:
# Name,Awakening,Level,Cost,HP,DEF,TP,SPR,AP,DEX,ATK,AGI,MAG,Luck,Party Ability,Bestowed Abilities
# (B) ..........................................................................(Q)
new_values = []
# TODO: Write awakening and level once they are available
new_values.append('') # Awakening
new_values.append('') # Level
new_values.append(VisionCardManager.valueOrEmpty(vision_card.Cost))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.HP))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.DEF))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.TP))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.SPR))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.AP))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.DEX))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.ATK))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.AGI))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.MAG))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.Luck))
new_values.append(VisionCardManager.valueOrEmpty(vision_card.PartyAbility))
new_values.append(VisionCardManager.toMultiLineString(vision_card.BestowedEffects))
allRequests = [WorksheetUtils.generateRequestToSetRowText(sheet_id, row_index_1_based, 'C', new_values)]
requestBody = {
'requests': [allRequests]
}
# Execute the whole thing as a batch, atomically, so that there is no possibility of partial update.
self.spreadsheet_app.batchUpdate(spreadsheetId=self.vision_card_spreadsheet_id, body=requestBody).execute()
def addVisionCardRow(self, user_id: str, name: str, url: str, above_or_below: str, row_1_based: str):
"""Add a new row for a Vision Card.
The above_or_below parameter needs to be either the string 'above' or 'below'. The row should be in 1-based notation,
i.e. the first row is row 1, not row 0.
"""
if not AdminUtils.isAdmin(self.spreadsheet_app, self.access_control_spreadsheet_id, user_id):
raise ExposableException('You do not have permission to add a vision card.')
spreadsheet = self.spreadsheet_app.get(spreadsheetId=self.vision_card_spreadsheet_id).execute()
allRequests = WorksheetUtils.generateRequestsToAddRowToAllSheets(
spreadsheet, int(row_1_based), above_or_below,
True, # Set a header column...
'B', # ... On the second column (A1 notation)
name, # With text content being the vision card name
url) # As a hyperlink to the url
requestBody = {
'requests': [allRequests]
}
# Execute the whole thing as a batch, atomically, so that there is no possibility of partial update.
self.spreadsheet_app.batchUpdate(spreadsheetId=self.vision_card_spreadsheet_id, body=requestBody).execute()
return
def __invokeTypedSearch(
data_files: DataFiles,
search_type: str = None,
search_text: str = None,
previous_results_to_filter: [UnitSearchResult] = None) -> [UnitSearchResult]:
search_type = search_type.strip().lower()
if search_type == 'all':
results: [UnitSearchResult] = []
for unit in data_files.playable_units_by_id.values():
one_result = UnitSearchResult()
one_result.unit = unit
results.append(one_result)
return results
if search_type == 'skill-name':
return DataFileSearchUtils.findUnitWithSkillName(data_files, search_text, previous_results_to_filter)
if search_type == 'skill-desc' or search_type == 'skill-description':
return DataFileSearchUtils.findUnitWithSkillDescription(data_files, search_text, previous_results_to_filter)
if search_type == 'job' or search_type == 'job-name':
return DataFileSearchUtils.findUnitWithJobName(data_files, search_text, previous_results_to_filter)
if search_type == 'rarity':
return DataFileSearchUtils.findUnitWithRarity(data_files, search_text, previous_results_to_filter)
if search_type == 'element':
return DataFileSearchUtils.findUnitWithElement(data_files, search_text, previous_results_to_filter)
raise ExposableException('Unsupported rich unit search type or refinement: "' + search_type + '". For help using search, use !help')
def toDiscordMessages(message_text):
"""Returns a list of messages, all under DISCORD_MESSAGE_LENGTH_LIMIT in size.
If the given message is longer than DISCORD_MESSAGE_LENGTH_LIMIT, splits the message into as many
chunks as necessary in order to stay under the limit for each message. Tries to respect newlines.
If a line is too long, this method will fail.
"""
if len(message_text) < DISCORD_MESSAGE_LENGTH_LIMIT:
return [message_text]
result = []
buffer = ''
lines = message_text.splitlines(keepends=True)
for line in lines:
if len(line) > DISCORD_MESSAGE_LENGTH_LIMIT:
# There's a line with a single word too long to fit. Abort.
raise ExposableException('response too long')
if (len(buffer) + len(line)) < DISCORD_MESSAGE_LENGTH_LIMIT:
buffer += line
else:
result.append(buffer)
buffer = line
if len(buffer) > 0:
result.append(buffer)
return result
def generateRequestsToAddColumnToAllSheets(spreadsheet,
columnA1: str,
left_or_right_of: str,
set_header: bool = False,
header_row_index: int = 0,
header_text: str = None,
header_url: str = None) -> [{}]:
"""Generate and return a series of Google Sheets requests that will add a column (with optional header row) to every worksheet in the spreadsheet.
:param spreadsheet: the spreadsheet to generate requests for
:param column_A1: the column from which to copy formatting, in A1 notation (see next parameter below)
:param left_or_right_of: either 'left-of', meaning to insert left-of columnA1, or 'right-of', meaning to insert right-of columnA1
:param set_header: if True, set a header row in the newly inserted column. Defaults to False (all remaining parameters ignored)
:param header_row_index: The 0-based offset of the header row to set the value of, i.e. a value of zero refers to the first row
:param header_text: The text to set in the header row
:param header_url: If set, converts the header_text to a hyperlink having the specified URL target.
"""
columnInteger = WorksheetUtils.fromA1(columnA1)
inheritFromBefore = None
if left_or_right_of == 'left-of':
inheritFromBefore = False # Meaning, inherit from right
elif left_or_right_of == 'right-of':
inheritFromBefore = True # Meaning, inherit from left
columnInteger += 1
else:
raise ExposableException(
'Incorrect parameter for position of new column, must be "left-of" or "right-of": '
+ left_or_right_of)
allRequests = []
for sheet in spreadsheet['sheets']:
sheetId = sheet['properties']['sheetId']
# First create an 'insertDimension' request to add a blank column on each sheet.
insertDimensionRequest = {
'insertDimension': {
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#insertdimensionrequest
'inheritFromBefore': inheritFromBefore,
'range': {
'sheetId': sheetId,
'dimension': 'COLUMNS',
'startIndex': columnInteger - 1,
'endIndex': columnInteger
}
}
}
allRequests.append(insertDimensionRequest)
if not set_header:
continue
# Now add the header row to the new column on each sheet.
startColumnIndex = columnInteger - 1
userEnteredValue = None
if header_url:
userEnteredValue = {
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ExtendedValue
'formulaValue':
'=HYPERLINK("' + header_url + '", "' + header_text + '")'
}
else:
userEnteredValue = {'stringValue': header_text}
updateCellsRequest = {
'updateCells': {
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest
'rows': [{
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#RowData
'values': [{
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData
'userEnteredValue': userEnteredValue
}]
}],
'fields':
'userEnteredValue',
'range': {
'sheetId': sheetId,
'startRowIndex': header_row_index, # inclusive
'endRowIndex': header_row_index + 1, # exclusive
'startColumnIndex': startColumnIndex, # inclusive
'endColumnIndex': startColumnIndex + 1 # exclusive
}
}
}
allRequests.append(updateCellsRequest)
return allRequests
def setResonance(self, user_id: str, unit_name: str, esper_name: str,
resonance_numeric_string: str, priority: str,
comment: str):
"""Set the esper resonance.
Returns the old value, new value, pretty unit name, and pretty esper name for the given (unit, esper) tuple, for the given user.
"""
resonance_int = None
try:
resonance_int = int(resonance_numeric_string)
except:
# pylint: disable=raise-missing-from
raise ExposableException('Invalid resonance level: "{0}"'.format(
resonance_numeric_string
)) # deliberately low on details as this is replying publicly.
if (resonance_int < 0) or (resonance_int > 10):
raise ExposableException(
'Resonance must be a value in the range 0 - 10')
user_name = AdminUtils.findAssociatedTab(
self.spreadsheet_app, self.access_control_spreadsheet_id, user_id)
esper_column_A1, pretty_esper_name = self.findEsperColumn(
self.esper_resonance_spreadsheet_id, user_name, esper_name)
unit_row, pretty_unit_name = self.findUnitRow(
self.esper_resonance_spreadsheet_id, user_name, unit_name)
spreadsheet = self.spreadsheet_app.get(
spreadsheetId=self.esper_resonance_spreadsheet_id).execute()
sheetId = None
for sheet in spreadsheet['sheets']:
sheetTitle = sheet['properties']['title']
if sheetTitle == user_name:
sheetId = sheet['properties']['sheetId']
break
if sheetId is None:
raise ExposableException(
'Internal error: sheet not found for {0}.'.format(user_name))
# We have the location. Get the old value first.
range_name = WorksheetUtils.safeWorksheetName(
user_name) + '!' + esper_column_A1 + str(
unit_row) + ':' + esper_column_A1 + str(unit_row)
result = self.spreadsheet_app.values().get(
spreadsheetId=self.esper_resonance_spreadsheet_id,
range=range_name).execute()
final_rows = result.get('values', [])
old_value_string = '(not set)'
if final_rows:
old_value_string = final_rows[0][0]
# Now that we have the old value, try to update the new value.
# If priority is blank, leave the level (high/medium/low) alone.
if priority is not None:
priority = priority.lower()
priorityString = None
if resonance_int == 10:
priorityString = '10/10'
elif (priority == 'l') or (priority == 'low') or (
priority is None and 'low' in old_value_string.lower()):
priorityString = EsperResonanceManager.RESONANCE_LOW_PRIORITY_VALUE_TEMPLATE.format(
resonance_int)
elif (priority == 'm') or (priority == 'medium') or (
priority is None and 'medium' in old_value_string.lower()):
priorityString = EsperResonanceManager.RESONANCE_MEDIUM_PRIORITY_VALUE_TEMPLATE.format(
resonance_int)
elif (priority == 'h') or (priority == 'high') or (
priority is None and 'high' in old_value_string.lower()):
priorityString = EsperResonanceManager.RESONANCE_HIGH_PRIORITY_VALUE_TEMPLATE.format(
resonance_int)
elif priority is None:
# Priority not specified, and old value doesn't have high/medium/low -> old value was blank, or old value was 10.
# Default to low priority.
priorityString = EsperResonanceManager.RESONANCE_LOW_PRIORITY_VALUE_TEMPLATE.format(
resonance_int)
else:
raise ExposableException(
'Unknown priority value. Priority should be blank or one of "L", "low", "M", "medium", "H", "high"'
)
allRequests = [
WorksheetUtils.generateRequestToSetCellText(
sheetId, unit_row, esper_column_A1, priorityString)
]
if comment:
comment_text = comment
if comment == '<blank>': # Allow clearing the comment
comment_text = None
allRequests.append(
WorksheetUtils.generateRequestToSetCellComment(
sheetId, unit_row, esper_column_A1, comment_text))
requestBody = {'requests': [allRequests]}
# Execute the whole thing as a batch, atomically, so that there is no possibility of partial update.
self.spreadsheet_app.batchUpdate(
spreadsheetId=self.esper_resonance_spreadsheet_id,
body=requestBody).execute()
return old_value_string, priorityString, pretty_unit_name, pretty_esper_name
def readResonanceList(self, user_name: str, user_id: str,
query_string: str):
"""Read and return the pretty name of the query subject (either a unit or an esper), and resonance list for the given user.
Set either the user name or the user ID, but not both. If the ID is set, the tab name for the resonance lookup is done the
same way as setResonance - an indirection through the access control spreadsheet is used to map the ID of the user to the
correct tab. This is best for self-lookups, so that even if a user changes their own nickname, they are still reading their own data
and not the data of, e.g., another user who has their old nickname.
The returned list of resonances is either (unit/resonance) or (esper/resonance) tuples.
"""
if (user_name is not None) and (user_id is not None):
print(
'internal error: both user_name and user_id specified. Specify one or the other, not both.'
)
raise ExposableException('Internal error')
if user_id is not None:
user_name = AdminUtils.findAssociatedTab(
self.spreadsheet_app, self.access_control_spreadsheet_id,
user_id)
esper_column_A1 = None
pretty_esper_name = None
unit_row_index = None
pretty_unit_name = None
mode = None
target_name = None
# First try to look up a unit whose name matches.
unit_lookup_exception_message = None
try:
unit_row_index, pretty_unit_name = self.findUnitRow(
self.esper_resonance_spreadsheet_id, user_name, query_string)
mode = 'for unit'
target_name = pretty_unit_name
except ExposableException as ex:
unit_lookup_exception_message = ex.message
# Try an esper lookup instead
esper_lookup_exception_message = None
if mode is None:
try:
esper_column_A1, pretty_esper_name = self.findEsperColumn(
self.esper_resonance_spreadsheet_id, user_name,
query_string)
mode = 'for esper'
target_name = pretty_esper_name
except ExposableException as ex:
esper_lookup_exception_message = ex.message
# If neither esper or unit is found, fail now.
if mode is None:
raise ExposableException(
'Unable to find a singular match for: ```{0}```\nUnit lookup results: {1}\nEsper lookup results: {2}'
.format(query_string, unit_lookup_exception_message,
esper_lookup_exception_message))
# Grab all the data in one call, so we can read everything at once and have atomicity guarantees.
result = self.spreadsheet_app.values().get(
spreadsheetId=self.esper_resonance_spreadsheet_id,
range=WorksheetUtils.safeWorksheetName(user_name)).execute()
result_rows = result.get('values', [])
resonances = []
if mode == 'for esper':
esper_index = WorksheetUtils.fromA1(
esper_column_A1) - 1 # 0-indexed in result
rowCount = 0
for row in result_rows:
rowCount += 1
if rowCount < 3:
# skip headers
continue
# rows collapse to the left, so only the last non-empty column exists in the data
if len(row) > esper_index:
# annnnd as a result, there might be a value to the right, while this column could be empty.
if row[esper_index]:
resonances.append(row[1] + ': ' + row[esper_index])
else: # mode == 'for unit'
colCount = 0
unit_row = result_rows[unit_row_index - 1] # 0-indexed in result
for column in unit_row:
colCount += 1
if colCount < 3:
# skip headers
continue
if column:
# Grab the esper name from the top of this column, and then append the column value.
resonances.append(result_rows[1][colCount - 1] + ': ' +
column)
# Format the list nicely
resultString = ''
for resonance in resonances:
resultString += resonance + '\n'
resultString = resultString.strip()
return (target_name, resultString)
def generateRequestsToAddRowToAllSheets(spreadsheet,
row_1_based: int,
above_or_below: str,
set_header: bool = False,
header_column_A1: str = None,
header_text: str = None,
header_url: str = None) -> [{}]:
"""Generate and return a series of Google Sheets requests that will add a row (with optional header column) to every worksheet in the spreadsheet.
:param spreadsheet: the spreadsheet to generate requests for
:param row_1_based: the row from which to copy formatting (first row is row 1) (see next parameter below)
:param above_or_below: either 'above', meaning to insert just above row_1_based, or 'after', meaning to insert just below row_1_based
:param set_header: if True, set a header column in the newly inserted row. Defaults to False (all remaining parameters ignored)
:param header_column_A1: The A1 notation of the header column to set the value of, i.e. a value of 'A' refers to the first column
:param header_text: The text to set in the header column
:param header_url: If set, converts the header_text to a hyperlink having the specified URL target.
"""
inheritFromBefore = None
if above_or_below == 'above':
inheritFromBefore = False # Meaning, inherit from below
elif above_or_below == 'below':
inheritFromBefore = True # Meaning, inherit from above
row_1_based += 1
else:
raise ExposableException(
'Incorrect parameter for position of new row, must be "above" or "below": '
+ above_or_below)
allRequests = []
for sheet in spreadsheet['sheets']:
sheetId = sheet['properties']['sheetId']
# First create an 'insertDimension' request to add a blank row on each sheet.
insertDimensionRequest = {
'insertDimension': {
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#insertdimensionrequest
'inheritFromBefore': inheritFromBefore,
'range': {
'sheetId': sheetId,
'dimension': 'ROWS',
'startIndex': row_1_based - 1,
'endIndex': row_1_based
}
}
}
allRequests.append(insertDimensionRequest)
if not set_header:
continue
# Now add the header row to the new column on each sheet.
startRowIndex = row_1_based - 1
userEnteredValue = None
if header_url:
userEnteredValue = {
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ExtendedValue
'formulaValue':
'=HYPERLINK("' + header_url + '", "' + header_text + '")'
}
else:
userEnteredValue = {'stringValue': header_text}
updateCellsRequest = {
'updateCells': {
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest
'rows': [{
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#RowData
'values': [{
# Format: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData
'userEnteredValue': userEnteredValue
}]
}],
'fields':
'userEnteredValue',
'range': {
'sheetId':
sheetId,
'startRowIndex':
startRowIndex, # inclusive
'endRowIndex':
startRowIndex + 1, # exclusive
'startColumnIndex':
WorksheetUtils.fromA1(header_column_A1) -
1, # inclusive
'endColumnIndex':
WorksheetUtils.fromA1(header_column_A1) # exclusive
}
}
}
allRequests.append(updateCellsRequest)
return allRequests
