def write_cocktail_instructions_return_next_row(
cocktail_recipe: CocktailRecipe,
ws: gspread.Worksheet = None,
row: int = None) -> int:
"""
Writes a cocktail_recipe recipe into a google sheet worksheet.
Format:
0, 0, 0,
Cocktail name,
Instruction 1,
...,
Instruction limit
The cocktail has a separator line after the previous entry in the
worksheet, unless the worksheet is empty.
:param ws: The worksheet where to write the cocktail_recipe to
:param cocktail_recipe: The cocktail_recipe recipe to write
:param row: the row the entries should populate from
:return: The next empty row below the instructions for this cocktail.
"""
current_row = write_cocktail_header_return_next_row(
cocktail_recipe, ws, row)
for instruction in cocktail_recipe.get_instructions():
ws.update_cell(current_row, 2, instruction)
current_row += 1
return current_row
def apply_to_sheet(self, worksheet: gspread.Worksheet):
"""
Update the specified Google Sheet worksheet by altering the row
corresponding to this update's edited message.
"""
# Find existing cell with the same ID
try:
cell = worksheet.find(self.message_id)
except gspread.CellNotFound:
logger.error(f"Failed to edit message with id={self.message_id} - original message not found in sheet.")
return
if not cell.col == ColumnHeaders['MessageID'].value:
logger.error(f"Failed to edit message with id={self.message_id} - no message with same ID found.")
return
# Get column value where the message is stored
message_cell_col = ColumnHeaders['Message'].value
# Get column value where edited timestamp is stored
edited_timestamp_cell_col = ColumnHeaders['LastEdited'].value
# Updated the cells with new edits
worksheet.update_cell(cell.row, message_cell_col, self.message)
worksheet.update_cell(cell.row, edited_timestamp_cell_col, self.edit_timestamp.isoformat())
# Prints success to console
logger.info(f"Cells ({cell.row}, {message_cell_col}), ({cell.row}, {edited_timestamp_cell_col}) updated")
def update_date(self,
ws: Worksheet,
env: Env,
prog: TestProgress,
date: Optional[datetime] = None):
ws.update_cell(self.id + 1,
self.columns.index(f'{env.name}_{prog.name}') + 1,
date.strftime(self.date_fmt) if date else '')
def write_separator_line(ws: gspread.Worksheet, separator_row_index: int,
width: int, separator: Union[str, int]) -> None:
"""
Writes a separator line to mark the start of a new entry.
:param ws: Worksheet to write the line to
:param separator_row_index: index of the row to write the line in
:param width: How many cells should be written to in the given row
:param separator: The value which to fills the cells with
:return: None
"""
for col in range(1, width + 1):
ws.update_cell(separator_row_index, col, separator)
def write_cocktail_names_based_on_ingredient_return_next_row(
ingredient: str,
ws: gspread.Worksheet,
next_empty_row=None,
limit: int = 1) -> int:
"""
:param ingredient: The main ingredient of the drinks whose name to write.
:param ws: The worksheet to insert the drink name to
:param next_empty_row: If known, the next empty row where to write the drink names to.
:param limit: The maximum number of drink names to write for the given ingredient.
:return: The next empty row below where the drink names were written.
"""
current_row = write_ingredient_header_return_next_row(
ingredient, ws, next_empty_row)
for cocktail in get_drinks_based_on_ingredient(ingredient, limit):
ws.update_cell(current_row, 2, cocktail.get_name())
current_row += 1
return current_row
def write_cocktail_ingredients_into_spreadsheet_return_next_row(
cocktail_recipe: CocktailRecipe,
ws: gspread.Worksheet,
next_empty_row=None) -> int:
"""
Inserts the cocktail_recipe recipe into the bottom of the spreadsheet.
The first entry in the row is the
:param cocktail_recipe: The cocktail_recipe recipe to insert into the spreadsheet.
:param ws: The worksheet to insert the cocktail_recipe to
:param next_empty_row: If known, the next empty row where to write the ingredients to.
:return The next empty row below the current row.
"""
current_row = write_cocktail_header_return_next_row(
cocktail_recipe, ws, next_empty_row)
for ingredient, amount in cocktail_recipe.get_ingredients().items():
ws.update_cell(current_row, 2, ingredient)
ws.update_cell(current_row, 3, amount)
current_row += 1
return current_row
def write_cocktail_header_return_next_row(cocktail_recipe: CocktailRecipe,
ws: gspread.Worksheet,
row: int = None):
"""
Writes a separator line and drink name into the spreadsheet at the next available row
:param cocktail_recipe: Recipe to which to write the header for.
:param ws: Worksheet that should be written to.
:param row: Row which to write the header into.
:return: The rwo below the cocktail header
"""
top_margin_row = 3
current_row = row
if row is None:
current_row = find_next_empty_row_index(ws)
if current_row >= top_margin_row:
write_separator_line(ws, current_row, width=3, separator="0")
current_row += 1
ws.update_cell(current_row, 1, cocktail_recipe.get_name())
current_row += 1
return current_row
def write_ingredient_header_return_next_row(ingredient: str,
ws: gspread.Worksheet,
row: int = None):
"""
Writes a separator line and ingredient name into the spreadsheet at the next available row.
If no row is given, it finds the next empty row in the worksheet.
:param ingredient: The ingredient of the drinks
:param ws: Worksheet that should be written to.
:param row: Row which to write the header to.
:return: The row below the ingredient header.
"""
top_margin_row = 3
current_row = row
if current_row is None:
current_row = find_next_empty_row_index(ws)
if current_row >= top_margin_row:
write_separator_line(ws, current_row, width=2, separator="0")
current_row += 1
ws.update_cell(current_row, 1, ingredient)
current_row += 1
return current_row
def update_gsheet_cells(row, cursheet: gspread.Worksheet, line_num) -> None:
"""
(helper) Paste values found in tracker_listings to the Needs Review Tracker
Parameters
----------
row : pandas Series
The current row in the tracker_listings dataframe
cursheet : gspread.Worksheet
The current sheet. This should always be 'Needs Review Tracker'
line_num : TYPE
The index of the Needs Review Tracker where there is a blank value.
This is based off of the length of the 'Date Assigned' column of the
Needs Review Tracker
Returns
-------
None
"""
line_num += row[3] # To go to the next row, according to the Priority Rank value
cursheet.update_cell(line_num, 8, row[0]) # Date Assigned
cursheet.update_cell(line_num, 10, row[1]) # client_id
cursheet.update_cell(line_num, 13, row[2]) # total_sales
cursheet.update_cell(line_num, 14, row[3]) # Priority Rank
cursheet.update_cell(line_num, 15, row[4]) # Count - Priority Items
time.sleep(5) # Necessary, otherwise it'll throw an error from running too fast
def update_state(self, ws: Worksheet, s: State, env: Env):
ws.update_cell(self.id + 1,
self.columns.index(f'{env.name}_state') + 1, s.name)
ws.update_cell(self.id + 1,
self.columns.index('machine') + 1, socket.gethostname())