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 append_worksheet_values(worksheet: Worksheet, values: List[list]):
_log.debug("appending %s rows values to worksheet...", len(values))
cells = convert_values_to_cells(values,
start_row=len(worksheet.col_values(1)) + 1)
worksheet.add_rows(len(values))
worksheet.update_cells(cells)
def set_worksheet_values(worksheet: Worksheet, values: List[list]):
_log.debug("clearing worksheet: %s ...", worksheet)
worksheet.clear()
cells = convert_values_to_cells(values)
_log.debug("updating worksheet values...")
worksheet.update_cells(cells)
_log.debug("worksheet values has been set!")
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 model_cell(wks: Worksheet, index: str) -> Cell:
'''???'''
cell = Cell(index)
wks.unlink()
cell.text_format['fontSize'] = 10
cell.text_format['bold'] = True
wks.link()
return cell
def sync_lang(sheet: Worksheet):
raw_sheet_lang = sheet.row_values(1)
sheet_lang = set(raw_sheet_lang)
origin_lang = set(language_pack.languages)
empty_langs = origin_lang - sheet_lang
if empty_langs:
data = [raw_sheet_lang + list(empty_langs)]
acell = f'A1:{ascii_uppercase[len(data[0]) - 1]}1'
print(f'update {acell} to ', data)
sheet.update(acell, data)
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 remove_keys(sheet: Worksheet, keys: List[str]):
rows = []
_remove_keys = []
col_num = get_id_field_cell(sheet).col
for k in keys:
try:
cell = sheet.find(k, in_column=col_num)
rows.append(cell.row)
_remove_keys.append(k)
except Exception as e:
pass
if rows:
sheet.delete_rows(rows)
print(f'remove {len(rows)} keys')
print(_remove_keys)
def __post_init__(self, order: str, search_sheet: Worksheet):
row = int(re.search(r"(R[0-9]+)", order).group(0)[1:]) # type: ignore
column = int(
re.search(r"(C[0-9]+)", order).group(0)[1:] # type: ignore
) - 1
self.cell_coordinates = (row, column)
self.cell_address = search_sheet.cell(row, column).address
self.steel_type = search_sheet.cell(row, 4).value
self.steel_depth = search_sheet.cell(row, 5).value
self.user_cell_color = get_format_safe(
get_user_entered_format, search_sheet,
self.cell_address).backgroundColor
self.user_f_row_color = get_format_safe(get_user_entered_format,
search_sheet,
f"F{row}").backgroundColor
def find_lang_col(sheet: Worksheet, sync=True):
if sync:
sync_lang(sheet)
return {
lang: sheet.find(lang, in_row=1).col
for lang in language_pack.languages
}
def parse_list(sheet: Worksheet):
cards = []
for row in sheet.get_all_values():
if row[0] and row[0] != 'ID' and row[2]:
cards.append(Card(row))
sorted_cards = sorted(cards, key=lambda e: e.id)
return sorted_cards
def apply_to_sheet(self, worksheet: gspread.Worksheet):
"""
Add a row to the specified Google Sheet worksheet corresponding to
this update's new Slack message.
"""
user_name = slack_utils.get_user_display(self.user_id)
row_data = [
self.message_id,
user_name,
self.message,
self.timestamp.isoformat()
]
# Inserts row into the spreadsheet with an offset of 2
# (After row 1 (header row))
worksheet.insert_row(row_data, 2)
logger.info(f"Message with id={self.message} by {user_name} added")
def load_accounts_from_sheet(sheet: gspread.Worksheet) -> typing.List[list]:
result = []
for record in sheet.get_all_records():
cols = [k.lower() for k in record.keys()]
if 'date' in cols and 'account' in cols:
result.append(record)
else:
logger.info(f"Skipping {record}")
return result
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 apply_to_sheet(self, worksheet: gspread.Worksheet):
"""
Remove the row corresponding to this update's deleted message from
the specified Google Sheet worksheet.
"""
# Find existing cell with the same ID
try:
cell = worksheet.find(self.message_id)
except gspread.CellNotFound:
logger.error(f"Failed to delete 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 delete message with id={self.message_id} - no message with same ID found.")
return
worksheet.delete_row(cell.row)
# Prints Success message to console
logger.info(f"Successfully deleted message with id={self.message_id}.")
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 gsheet_read(worksheet: gspread.Worksheet) -> pandas.DataFrame:
"""
Read the specified worksheet directly into a DataFrame
and remove rows, where no ID is given.
"""
if not worksheet:
return pandas.DataFrame()
dataframe = pandas.DataFrame(worksheet.get_all_records(), dtype=str)
if len(dataframe.index) == 0:
return pandas.DataFrame(columns=GSHEET_HEADER)
# Remove rows where the ID is missing
empty_rows = dataframe[dataframe['id'] == '']
if len(empty_rows.index) > 0:
for row in empty_rows.index:
worksheet.delete_rows(start_index=row + 2)
dataframe.drop(index=row, inplace=True)
dataframe.reset_index(drop=True, inplace=True)
return dataframe
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 is_row_empty(ws: gspread.Worksheet, row: int,
min_horizontal_empty_cells: int) -> bool:
"""
Checks whether a given row in a worksheet is empty
:param ws: Worksheet to check whether the given row is empty on.
:param row: The index of the row to check
:param min_horizontal_empty_cells: Minimum number of empty continuous cells needed, from left to right.
:return: Whether there are at least min_horizontal_empty continuous empty cells in the row from left to right.
"""
for col in range(1, 1 + min_horizontal_empty_cells):
if ws.cell(row, col).value:
return False
return True
def find_lang_start_col(sheet: Worksheet):
raw_sheet_lang = sheet.row_values(1)
min_index = None
for x in language_pack.languages:
try:
i = raw_sheet_lang.index(x)
min_index = i if min_index > i else min_index
except Exception as e:
pass
if min_index:
start_lang_idx = min_index + 1
else:
start_lang_idx = len(raw_sheet_lang) + 1
return start_lang_idx
def __resize_sheet(self, ws: gspread.Worksheet, num_rows: int,
num_cols: int) -> tuple:
"""
This *private* method resizes the worksheet to the given dimensions.
:param ws: the one Google Worksheet in the file
:param num_rows: number of rows
:param num_cols: number of columns
:return: None
"""
try:
if num_rows > 0 and num_cols > 0:
# ws.resize() calls batch_update
response = ws.resize(rows=num_rows, cols=num_cols)
# NOTE: ws.resize() has a known BUG, it does not update the ws.row_count and ws.col_count properties
# of the gspread.Worksheet object. Some gspread methods rely on these properties.
# So the following two statements update those properties.
ws._properties['gridProperties']['rowCount'] = num_rows
ws._properties['gridProperties']['columnCount'] = num_cols
return True, ''
except Exception as e:
return False, 'There was an error resizing the Google Sheet.'
def make_update_records_data(worksheet: Worksheet, lang_index: dict):
update_records_data = []
get_acel = get_range()
sort_lang = make_sort_lang(lang_index)
print(sort_lang)
for i, record in enumerate(worksheet.get_all_records()):
data = {
'range':
get_acel(i + 1),
'values':
make_values(sort_lang,
language_pack.get_all_language_value(record[ID_FIELD]))
}
update_records_data.append(data)
return update_records_data
def done(self,
ws: Worksheet,
env: Env,
results: Optional[Dict[str, int]] = None,
prefix_metrics: Optional[str] = None):
self.update_state(ws, State.done, env)
if env != Env.eval:
self.update_date(ws, env, TestProgress.end, datetime.now())
metrics = [
Metrics.loss, Metrics.accuracy, Metrics.f1_score, Metrics.miou,
Metrics.precision, Metrics.recall
]
cells = []
prefix_col = '' if env == Env.train else f'{env.value}_'
for m in metrics:
cell = ws.cell(self.id + 1,
self.columns.index(prefix_col + m.value) + 1)
value = results[(prefix_metrics or '') + m.value]
cell.value = "{:.6f}".format(value).replace('.', ',')
cells.append(cell)
if env != env.test:
for m in metrics:
name_key_best_val = f'best_{prefix_col}{m.value}'
cell = ws.cell(self.id + 1,
self.columns.index(name_key_best_val) + 1)
value = results['best_' + (prefix_metrics or '') + m.value]
cell.value = "{:.6f}".format(value).replace('.', ',')
cells.append(cell)
cell = ws.cell(self.id + 1, self.columns.index('machine') + 1)
cell.value = socket.gethostname()
ws.update_cells([*cells, cell])
def __enter_data_on_sheet(self, ws: gspread.Worksheet) -> tuple:
"""
This *private* method enters the data into the worksheet.
:param ws: the one Google Worksheet in the file
:return:
"""
if self.__data_list:
data = [self.__header_list[2]] + self.__data_list
else:
data = [self.__header_list[2]]
try:
response = ws.update('A1', data, raw=False)
return True, ''
except Exception as e:
return False, 'There was an error entering the data on the Google Sheet.'
def parse_competences(wsh : gspread.Worksheet):
ll = wsh.get_all_values() # type: List[List[str]]
comp = [] # type: List[Tuple[str, int, COMP_LEVEL]]
for line in range(2, 10):
comp.append((ll[line][COMP_NAME_COLUMN].strip().lower(), int(ll[line][COMP_SCORE_MAIN]), COMP_LEVEL.NORMAL))
comp_level = COMP_LEVEL.NORMAL
line = 13
while ll[line][COMP_NAME_COLUMN] and ll[line][COMP_SCORE_XPABLE]:
if not ll[line][COMP_SCORE_XPABLE].isnumeric():
try:
comp_level = STR_TO_COMP_LEVEL[ll[line][COMP_SCORE_XPABLE]]
except KeyError:
raise BotError(
f"Unexcepted value when parsing comp score, got \"{ll[line][COMP_SCORE_XPABLE]}\" at line {line} ({ll[line][COMP_NAME_COLUMN]})")
else:
comp.append((ll[line][COMP_NAME_COLUMN].strip().lower(), int(ll[line][COMP_SCORE_XPABLE]), comp_level))
line += 1
return comp
def process_datasets(config: SettingConfig, sheet: Worksheet) -> None:
print("Started running RASA")
# Compute and write the title of the spreadsheet based on the loaded configurations
spreadsheet_title = [config.identifier]
sheet.insert_row(spreadsheet_title, 1)
# For each scenario folder
spreadsheet_row_index = SPREADSHEET_START_VERTICAL_OFFSET
for file in os.listdir(config.datasets_path):
# Compute the path for the scenario folder
folder_path = os.path.join(config.datasets_path, file)
if os.path.isdir(folder_path):
# Break if the directory does not contain the splits
if len(os.listdir(folder_path)) <= 2:
print(
"Directory only contains train and test files, but no splits. Breaking."
)
break
# Compute the scenario file name
file_path = os.path.join(folder_path, file)
# Compute the reports path and create the directory
scenario_reports_path = os.path.join(folder_path,
'scenario_reports')
os.mkdir(scenario_reports_path)
# Compute the intent and slot reports paths and create them
intent_reports_path = os.path.join(scenario_reports_path,
'intent_reports')
slot_reports_path = os.path.join(scenario_reports_path,
'slot_reports')
os.mkdir(intent_reports_path)
os.mkdir(slot_reports_path)
scenario_slot_results = [f'Slot - {file}']
scenario_intent_results = [f'Intent - {file}']
for split_id in range(config.splits):
# Compute the identifier, get the train split and test split
identifier = f" {file}, split {split_id}"
train_split = file_path + "_train_" + f"{split_id}" + ".json"
test_split = file_path + "_test_" + f"{split_id}" + ".json"
# Run the subprocess for RASA training and testing, and wait for its completion
command = [
config.rasa_script_path, train_split, test_split,
config.rasa_config_path
]
subprocess.Popen(command, shell=True).wait()
# Process the slot and intent errors & reports and save their return values
intent_f1 = process_intent_result(identifier,
scenario_reports_path,
config)
slot_f1 = process_slot_result(identifier,
scenario_reports_path, config)
# Move the confusion matrix to the results path
copy_confusion_matrix(identifier, config)
scenario_slot_results.append(float("{:0.4f}".format(slot_f1)))
scenario_intent_results.append(
float("{:0.4f}".format(intent_f1)))
print(f"Finished processing split {identifier}")
# Append the mean value to each list for the scenario
scenario_intent_results.append(
float("{:0.4f}".format(mean(scenario_intent_results[1:]))))
scenario_slot_results.append(
float("{:0.4f}".format(mean(scenario_slot_results[1:]))))
# Append the standard deviation to each list for the scenario
scenario_intent_results.append(
float("{:0.3f}".format(
stdev(scenario_intent_results[1:len(scenario_intent_results
) - 2]))))
scenario_slot_results.append(
float("{:0.3f}".format(
stdev(scenario_slot_results[1:len(scenario_slot_results) -
2]))))
# Append the line in the google doc:
sheet.insert_row(scenario_slot_results, spreadsheet_row_index)
sheet.insert_row(scenario_intent_results, spreadsheet_row_index)
spreadsheet_row_index += 3
def write(sheet: gspread.Worksheet, df: pd.DataFrame) -> None:
cells = sheet.get_all_values()
set_with_dataframe(sheet, df, include_index=False,
include_column_header=False, row=len(cells) + 1, resize=False)