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())