def import_dictrows(spreadsheet, worksheet_name, rows, header):
"""
:param spreadsheet:
:param worksheet_name:
:param rows:
:param header:
:return:
"""
if len(rows) == 0:
logging.warning("no row to be added for worksheet %s", worksheet_name)
return
count_rows = len(rows) + 1
if header is None:
header = rows[0].keys()
count_cols = len(header)
worksheet = spreadsheet.add_worksheet(title=worksheet_name,
rows=count_rows,
cols=count_cols)
header_range = worksheet.range(
'%s:%s' % (rowcol_to_a1(1, 1), rowcol_to_a1(1, count_cols)))
for field, cell in zip(header, header_range):
cell.value = field
worksheet.update_cells(header_range)
data_range = worksheet.range(
'%s:%s' % (rowcol_to_a1(2, 1), rowcol_to_a1(count_rows, count_cols)))
for cell, value in zip(data_range, walk_through_range(header, rows)):
cell.value = value
worksheet.update_cells(data_range)
return worksheet
def _get_range(self, start, end):
"""Transform start and end to cell range like A1:B5"""
start_int = self._get_cell_as_tuple(start)
end_int = self._get_cell_as_tuple(end)
return "{0}:{1}".format(rowcol_to_a1(*start_int),
rowcol_to_a1(*end_int))
def fetch(self):
try:
sheet = self.client.open(self.options.spreadsheet).worksheet(self.options.worksheet)
except SpreadsheetNotFound:
raise ValueError("Spreadsheet was not found with name '{}'".format(self.options.spreadsheet))
except WorksheetNotFound:
raise ValueError("Worksheet was not found with name '{}'".format(self.options.worksheet))
header = sheet.row_values(1)
LOG.debug("Fetched spreadsheet header: %s", header)
update_date_col_idx = self.find_column_idx_in_header(header, self.options.update_date_column, "update date")
if update_date_col_idx < 0:
self.options.do_update_date = False
status_col_idx = self.find_column_idx_in_header(header, self.options.status_column, "status")
if status_col_idx < 0:
self.options.do_update_status = False
rows = sheet.get_all_records()
LOG.debug("Received data from sheet %s: %s", self.options.worksheet, pformat(rows))
# Check column is found
jira_col = self.options.jira_column
if rows and len(rows) > 0:
if not jira_col in rows[0]:
row0 = rows[0]
raise ValueError("Jira column with name '{}' was not found in "
"received data! First row of data: {}".format(jira_col, row0))
issues = []
# 1 because of 0-based indexing (rows are 1-based)
# 2 because of header row is the 1st row
idx_correction_row = 2
# 1 because of 0-based col indexing from header
idx_correction_col = 1
for idx, row in enumerate(rows):
issue = row[jira_col]
issues.append(issue)
update_date_cell_id, status_cell_id = None, None
if self.options.do_update_date:
update_date_cell_id = rowcol_to_a1(idx + idx_correction_row, update_date_col_idx + idx_correction_col)
if self.options.do_update_status:
status_cell_id = rowcol_to_a1(idx + idx_correction_row, status_col_idx + idx_correction_col)
# If update is required for any cell, we need to store a CellUpdateForIssue object, otherwise don't store it
if update_date_cell_id or status_cell_id:
self.issue_to_cellupdate[issue] = CellUpdateForIssue(issue, update_date_cell_id, status_cell_id)
LOG.debug("Issue to CellUpdate mappings: %s", self.issue_to_cellupdate)
LOG.debug("Found Jira issue from GSheet: %s", issues)
self.sheet = sheet
return issues
def list_of_col_names(number_of_cols, starting_col='B'):
first_col_num = a1_to_rowcol(starting_col + '1')[1]
last_col_num = first_col_num + number_of_cols - 1
return [
a1[:-1] for a1 in list_from_range_string(
rowcol_to_a1(1, first_col_num) + ':' +
rowcol_to_a1(1, last_col_num))
]
def records(self):
col_count = self.sheet.col_count
for row_id in range(self.record_start_row, self.sheet.row_count):
row_start_cell = rowcol_to_a1(row_id, 1)
row_end_cell = rowcol_to_a1(row_id, col_count)
row_cells = self.sheet.range('{}:{}'.format(
row_start_cell, row_end_cell))
# stop upon first empty row
if not any(cell.value for cell in row_cells):
break
yield TrackingRecord(self, row_id, row_cells)
def update_sheet(self, standings, is_long):
if (is_long):
for i in range(len(standings.results)):
for j in range(len(standings.results[i])):
if standings.results[i][j] != 1:
standings.results[i][j] = ''
self.wks.update(rowcol_to_a1(1, self.offset + 1), standings.name)
self.wks.update(
rowcol_to_a1(self.header + 1, self.offset + 1) + ":" +
rowcol_to_a1(self.header + len(standings.results),
self.offset + max(len(row) for row in standings.results)),
standings.results)
self.offset += len(standings.problems)
def write_range(self, spreadsheet_name: str, worksheet_name: str, start: tuple, end: tuple, iterable: list):
first_cell = utils.rowcol_to_a1(start[0], start[1])
last_cell = utils.rowcol_to_a1(end[0], end[1])
cell_range = '{}:{}'.format(first_cell, last_cell)
try:
cells = self.SpreadsheetObjects[spreadsheet_name][worksheet_name].range(cell_range)
for x, cell in enumerate(cells):
cell.value = iterable[x]
self.SpreadsheetObjects[spreadsheet_name][worksheet_name].update_cells(cells)
except Exception as e:
if isinstance(e, KeyError):
raise KeyError('{} key not found in SpreadsheetObjects'.format(e))
else:
raise Exception('Unhandled exception: ' + str(e))
def get_next_empty_cell_after_cell(worksheet: Worksheet,
after_cell: Cell,
return_format="a1") -> Any:
""" Gets the next empty cell after a specified cell (column-wise) in the specified format """
# look at all cells next to after_cell
all_names = worksheet.row_values(after_cell.row)[after_cell.col:]
# start col counter at cell next to after_cell
col_counter = after_cell.col + 1
for name in all_names:
# if string is empty we've found an empty cell inbetween names
if not name:
break
col_counter += 1
# if no empty cells found inbetween names the col_counter will be at cell after all names
try:
empty_cell = worksheet.cell(after_cell.row, col_counter)
# if we encounter an APIError we've run out of columns and need to allocate more
except GSpreadException:
worksheet.add_cols(10)
empty_cell = worksheet.cell(after_cell.row, col_counter)
# return cell based on return format
if 'a1' in return_format:
return_val = rowcol_to_a1(empty_cell.row, empty_cell.col)
elif 'cell' in return_format:
return_val = empty_cell
elif 'rowcol' in return_format:
return_val = empty_cell.row, empty_cell.col
else:
raise Exception(
"ERROR: Unknown format requested : '{0}'. Expected one of : 'a1', 'cell', 'rowcol'."
.format(return_format))
return return_val
def get_summery_request():
payload = dict()
kwargs = dict()
kwargs["range"] = "Summary!A1:{last_cell}".format(
last_cell=utils.rowcol_to_a1(
len(contacts) + 2,
len(flags) + 2))
kwargs["params"] = {"valueInputOption": "USER_ENTERED"}
payload["majorDimension"] = "ROWS"
values = list()
values.append(list(itertools.chain(*[[" "], flags])))
for contact in contacts:
v = list()
v.append(contact)
for flag in flags:
v.append(
"=COUNTIFS('Full Report'!{c}2:{c}1000, \"{contact}\",'Full Report'!{f}2:{f}1000,\"{flag}\")"
.format(contact=contact,
flag=flag.replace("?", "~?"),
c=self.get_filed_col_index("QA CONTACT"),
f=self.get_filed_col_index("Flag")))
values.append(v)
payload["values"] = values
kwargs["body"] = payload
return kwargs
def get_data_validation_rule(worksheet, label):
"""Returns a DataValidationRule object or None representing the
data validation in effect for the cell identified by ``label``.
:param worksheet: Worksheet object containing the cell whose data
validation rule is desired.
:param label: String with cell label in common format, e.g. 'B1'.
Letter case is ignored.
Example:
>>> get_data_validation_rule(worksheet, 'A1')
<DataValidationRule condition=(bold=True)>
>>> get_data_validation_rule(worksheet, 'A2')
None
"""
label = '%s!%s' % (worksheet.title, rowcol_to_a1(*a1_to_rowcol(label)))
resp = worksheet.spreadsheet.fetch_sheet_metadata({
'includeGridData':
True,
'ranges': [label],
'fields':
'sheets.data.rowData.values.effectiveFormat,sheets.data.rowData.values.dataValidation'
})
props = resp['sheets'][0]['data'][0]['rowData'][0]['values'][0].get(
'dataValidation')
return DataValidationRule.from_props(props) if props else None
def from_cells(cls, worksheet: Worksheet, row: int, cells: List):
kwargs = dict(
worksheet=worksheet, label=f"{TransactionHistory.HAS_RECEIPT_COLUMN}{row}"
)
for col, cell_value in enumerate(cells, 1):
label = rowcol_to_a1(row, col)
try:
if label.startswith(TransactionHistory.HAS_RECEIPT_COLUMN):
kwargs.update(has_receipt=bool(cell_value))
elif label.startswith(TransactionHistory.DATE_COLUMN):
kwargs.update(created=parse(cell_value).date())
elif label.startswith(TransactionHistory.TITLE_COLUMN):
kwargs.update(title=cell_value)
elif label.startswith(TransactionHistory.PRICE_COLUMN):
kwargs.update(price=Decimal(cell_value))
except Exception:
raise ValueError(
f"Can't convert '{cell_value}' from cell {label} ({worksheet.title}) into Transaction. "
f"Transaction wasn't created."
)
return cls(**kwargs)
def get_user_entered_format(worksheet, label):
"""Returns a CellFormat object or None representing the user-entered formatting directives,
if any, for the cell.
:param worksheet: Worksheet object containing the cell whose format is desired.
:param label: String with cell label in common format, e.g. 'B1'.
Letter case is ignored.
Example:
>>> get_user_entered_format(worksheet, 'A1')
<CellFormat textFormat=(bold=True)>
>>> get_user_entered_format(worksheet, 'A2')
None
"""
label = '%s!%s' % (worksheet.title, rowcol_to_a1(*a1_to_rowcol(label)))
resp = worksheet.spreadsheet.fetch_sheet_metadata({
'includeGridData':
True,
'ranges': [label],
'fields':
'sheets.data.rowData.values.userEnteredFormat'
})
props = resp['sheets'][0]['data'][0]['rowData'][0]['values'][0].get(
'userEnteredFormat')
return CellFormat.from_props(props) if props else None
def find_cell(
self, query
) -> list: # retrieve one or more cell's position in A1 notation
cell = self.ws.findall(query)
for i in range(len(cell)):
cell[i] = utils.rowcol_to_a1(cell[i].row, cell[i].col)
return cell
def get_all_colors(self, worksheet):
"""
Return background colors of all cells.
:return dict: {
"A1": {"red": 0.858689, "green": 0.3425, "blue": 0.85004},
...
}
"""
result = {}
spreadsheet_id = worksheet.spreadsheet.id
url = (
f"{SPREADSHEETS_API_V4_BASE_URL}/{spreadsheet_id}?ranges='{worksheet.title}'"
f"&fields=sheets/data/rowData/values/userEnteredFormat/backgroundColor"
)
response = self.request("get", url)
content = json.loads(response.content)
row_containers = content["sheets"][0]["data"][0]["rowData"]
for row, row_container in enumerate(row_containers, 1):
cell_containers = row_container.get("values", [])
for col, cell_container in enumerate(cell_containers, 1):
formatting = cell_container.get("userEnteredFormat")
if formatting:
label = rowcol_to_a1(row=row, col=col)
result[label] = formatting.get("backgroundColor")
return result
def get_all_notes(self, worksheet):
"""
Get notes of all cells from a certain worksheet.
:return dict: {
"A1": "Blah",
"L2": "Note"
}
"""
result = {}
spreadsheet_id = worksheet.spreadsheet.id
url = (
f"{SPREADSHEETS_API_V4_BASE_URL}/{spreadsheet_id}?ranges='{worksheet.title}'"
f"&fields=sheets/data/rowData/values/note")
response = self.request("get", url)
content = json.loads(response.content)
row_containers = content["sheets"][0]["data"][0]["rowData"]
for row, row_container in enumerate(row_containers, 1):
cell_containers = row_container.get("values", [])
for col, cell_container in enumerate(cell_containers, 1):
note = cell_container.get("note")
if note:
label = rowcol_to_a1(row=row, col=col)
result[label] = note
return result
def get_ranges(sheet_name, cols):
"""Get a list of ranges for the given worksheet and columns."""
ranges = []
for col in cols:
start_label = rowcol_to_a1(1, col)
range_label = "%s!%s:%s" % (sheet_name, start_label, start_label[:-1])
ranges.append(range_label)
return ranges
async def ajouter(ctx, date="", joueur="", prix=""):
listeJoueurs = lireJoueurs()
if date.lower() in listeDates and joueur.lower() in listeJoueurs and verifPrix(prix):
ligne = calcNavet.find(listeDates.get(date.lower())).row
colonne = calcNavet.find(joueur.capitalize()).col
calcNavet.update(rowcol_to_a1(ligne, colonne), prix)
await ctx.send("c bon!!")
else:
await ctx.send("ola fraté tia serré?? date, nom de joueur ou prix invalide go !aide pour plus de détails sur la syntaxe")
def write_col_multi(self, row, col_start, values):
"""
Write multiple values in a row "row" starting at "col_start"
:param row: Row index
:type row: int
:param col_start: Column start index
:type col_start: int
:param values: list with values
:type values: list
"""
if row <= self.__lock_row:
return
col_stop = col_start + len(values) - 1
c1 = gut.rowcol_to_a1(row, col_start)
c2 = gut.rowcol_to_a1(row, col_stop)
cell_list = self._get_range(c1, c2)
for cell, val in zip(cell_list, values):
cell.value = val
self.__write_col_cells(cell_list, c1, c2)
def publish_statistics_sheet(tournaments: models.Tournaments,
rankings: models.Rankings,
players: models.Players,
tid: str,
prev_tid: str,
upload: bool = False) -> None:
""" Copy details from log and output details of given tournament"""
xlsx_filename = cfg["io"]["data_folder"] + cfg["io"][
"publish_filename"].replace("NN", tid)
sheet_name = cfg["sheetname"]["statistics_key"]
stats = rankings.get_statistics()
headers = models.categories + ['total'] + models.categories + ['total']
with _get_writer(xlsx_filename, sheet_name) as writer:
stats.to_excel(writer,
sheet_name=sheet_name,
index=True,
header=headers,
index_label=cfg["labels"]["tid"])
ws = writer.book[sheet_name]
ws.insert_rows(0, 1)
n_headers = len(headers)
starting_cell, ending_cell = rowcol_to_a1(row=1, col=2), rowcol_to_a1(
row=1, col=n_headers / 2 + 1)
ws[starting_cell] = cfg["labels"]["Cumulated"]
ws.merge_cells(f'{starting_cell}:{ending_cell}')
starting_cell, ending_cell = rowcol_to_a1(
row=1, col=n_headers / 2 + 2), rowcol_to_a1(row=1,
col=n_headers + 1)
ws[starting_cell] = cfg["labels"]["By Tournament"]
ws.merge_cells(f'{starting_cell}:{ending_cell}')
if upload:
load_and_upload_sheet(xlsx_filename, sheet_name,
cfg["io"]["temporal_spreadsheet_id"])
def flush(self):
ws = get_spreadsheet(self.parent.credentials, self.parent.doc_id,
self.parent.tab_id)
num_columns = len(self.buffer[0])
num_lines = len(self.buffer)
ws.resize(rows=num_lines, cols=num_columns)
range = 'A1:%s' % rowcol_to_a1(num_lines, num_columns)
ws.update(range,
self.buffer,
value_input_option=self.parent.write_format)
self.buffer = []
def clear_expenses(self):
"""Clear all expenses and notes for the month in all categories."""
_, col_1 = a1_to_rowcol(f"{self.FIRST_DAY_COLUMN}1")
_, col_31 = a1_to_rowcol(f"{self.LAST_DAY_COLUMN}1")
cell_list = [
Cell(row=row, col=col, value="")
for col in range(col_1, col_31 + 1)
for row in self.CATEGORY_ROWS.values()
]
label_notes = {
rowcol_to_a1(cell.row, cell.col): ""
for cell in cell_list
}
self.worksheet.update_cells(cell_list=cell_list)
self.worksheet.spreadsheet.client.insert_notes(
worksheet=self.worksheet, labels_notes=label_notes, replace=True)
def get_full_report_request():
kwargs = dict()
kwargs["range"] = "Full Report!A1:{last_cell}".format(
last_cell=utils.rowcol_to_a1(
len(self.all_bugs) +
1, len(self.get_report_title().split(","))))
kwargs["params"] = {"valueInputOption": "USER_ENTERED"}
payload = dict()
payload["majorDimension"] = "ROWS"
payload["values"] = list(
itertools.chain(*[[self.get_report_title().split(",")],
[bug for bug in self.all_bugs]]))
kwargs["body"] = payload
return kwargs
def flush(self):
ws = self.parent.get_spreadsheet()
num_columns = len(self.buffer[0])
num_lines = len(self.buffer)
ws.resize(rows=num_lines, cols=num_columns)
cell_list = ws.range( 'A1:%s' % rowcol_to_a1(num_lines, num_columns) )
for cell in cell_list:
val = self.buffer[cell.row-1][cell.col-1]
# if type(val) is str:
# val = val.decode('utf-8')
cell.value = val
ws.update_cells(cell_list)
self.buffer = []
def get_earliest_label(*labels):
"""Return the earliest among labels in left-to-right top-to-bottom order."""
labels = [label for label in labels if label]
if not labels:
raise ValueError("At least one non-empty label must be provided.")
if len(labels) == 1:
return labels[0]
coords = [a1_to_rowcol(label) for label in labels]
min_row = min(row for row, _ in coords)
earliest_row = [(row, col) for row, col in coords if row == min_row]
min_col = min(col for _, col in earliest_row)
earliest = [(row, col) for row, col in earliest_row if col == min_col][0]
earliest_label = rowcol_to_a1(*earliest)
return earliest_label
def _prices(self):
"""
Get all prices and recognize their type.
This method practices lazy evaluation too for the same reasons.
:return dict: a map sorted by cell label
{
"D10": (Decimal(3.45), CellType.REGULAR),
"D12": (Decimal(4.56), CellType.REGULAR),
...
"D13": (Decimal(1.23), CellType.TOTAL),
"D15": (Decimal(1.23), CellType.TAX),
}
"""
result = {}
_, col = a1_to_rowcol(f"{self.PRICE_COLUMN}1")
price_cells = [
Cell(row=row, col=col, value=line[col - 1])
for row, line in enumerate(self.content, 1)
if line[col - 1] and row > 1
]
for cell in reversed(price_cells):
label = rowcol_to_a1(cell.row, cell.col)
amount = price_to_decimal(cell.value,
worksheet_title=self.worksheet.title,
label=label)
is_summary_collected = all(price_type in result
for price_type in SUMMARY_TYPES)
if is_summary_collected:
result[label] = (amount, CellType.REGULAR)
# if all summary prices are identified already, then we don't need
# to check the color of other prices because the rest of them are
# regular prices. That's why we move on to the next cell right away.
continue
cell_type = self.get_cell_type(label=label) or CellType.REGULAR
result[label] = (amount, cell_type)
result = dict(natsorted(result.items()))
return result
def save_sheet(svc_sheet, spreadsheet_id, tab_name, header, records):
"""
Saves indicated records to sheet.
:param svc_sheet:
:param spreadsheet_id:
:param tab_name:
:param header:
:param records: list of dict to be saved (records-like format)
:return:
"""
if len(records) == 0:
return
count_columns = len(header)
count_rows = len(records) + 1
workbook = svc_sheet.open_by_key(spreadsheet_id)
sheets = dict()
for sheet in workbook.worksheets():
sheets[sheet.title] = sheet
if tab_name not in sheets:
worksheet = workbook.add_worksheet(tab_name, count_rows, count_columns)
else:
worksheet = sheets[tab_name]
worksheet.resize(rows=count_rows, cols=count_columns)
range_text = 'A1:{}'.format(rowcol_to_a1(count_rows, count_columns))
logging.info('accessing range {}'.format(range_text))
cells = worksheet.range(range_text)
for cell in cells:
count_row = cell.row - 1
count_col = cell.col - 1
field = header[count_col]
if count_row == 0:
cell.value = field
else:
row_data = records[count_row - 1]
cell.value = row_data[field]
worksheet.update_cells(cells)
def _names(self):
"""
Get all names from the Names column with recognized type.
This method practices lazy evaluation - the first time a
certain good gets requested, the entire column of purchased
items gets parsed. This is done for performance reasons in
order to reduce the number of API requests because it takes
one API call to get the style of each cell.
:return dict: a map sorted by cell label
{
"B8": ('Bread', CellType.GROCERY),
"B10": ('SUSHI ROLL', CellType.TAKEOUTS),
"B14": ('DEBIT', CellType.REGULAR),
...
}
"""
result = {}
_, col = a1_to_rowcol(f"{self.NAME_COLUMN}1")
for row, line in enumerate(self.content, 1):
if row == 1:
continue
value = line[col - 1]
label = rowcol_to_a1(row, col)
cell_type = self.get_cell_type(label=label)
if (not cell_type or cell_type == CellType.REGULAR) and not value:
continue
result[label] = (value, cell_type)
result = dict(natsorted(result.items()))
return result
def list_from_range_string(range_string):
"""Extract all individual cell names from a Excel range.
Keyword arguments:
range_string -- The Excel expression for the range
Example:
If range_string == 'A1:B3' then the list
['A1', 'B1', 'A2', 'B2', 'A3', 'B3'] is returned
"""
colon_position = range_string.find(':')
if colon_position == -1:
raise
first_cell = range_string[:colon_position]
last_cell = range_string[colon_position + 1:]
first_row, first_col = a1_to_rowcol(first_cell)
last_row, last_col = a1_to_rowcol(last_cell)
return [
rowcol_to_a1(i, j)
for i, j in product(range(first_row, last_row +
1), range(first_col, last_col + 1))
]
def generate_room(credentials: Credentials, file_id: str, sheet_index: int,
prefix: str, judge_num: int, api_key: Dict[str, str],
settings: Dict[str, Any]):
"""試合会場を生成する
:param credentials: Google の認証情報
:type credentials: Credentials
:param file_id: 管理用スプレッドシートのID
:type file_id: str
:param sheet_index: 対戦表シートのインデックス
:type sheet_index: int
:param prefix: 会場名のプレフィックス
:type prefix: str
:param judge_num: ジャッジの人数
:type judge_num: int
:param api_key: Zoom の APIキー/APIシークレット
:type api_key: Dict[str, str]
:param settings: Zoom ミーティングの設定情報
:type settings: Dict[str, Any]
"""
def get_users(client: ZoomClient) -> List[Dict[str, Any]]:
response = client.raw.get_all_pages('/users',
query={'status': 'active'})
return response['users']
def find_user(users: List[Dict[str, Any]], key: str,
value: Any) -> List[Dict[str, Any]]:
users = list(filter(lambda x: x[key] == value, users))
return users
def generate_password(length: int = 6):
chars = string.digits
return ''.join(secrets.choice(chars) for x in range(length))
gc = gspread.authorize(credentials)
book = gc.open_by_key(file_id)
sheet = WorksheetEx.cast(book.get_worksheet(sheet_index))
values = sheet.get_all_values()
now = datetime.now()
year = now.year
month, day = values.pop(0)[1].split('/')
values.pop(0)
client = ZoomClient(api_key['api-key'], api_key['api-secret'])
users = get_users(client)
meetings = []
for value in values:
matchName = value[0]
hour_s, min_s = value[2].split(':')
hour_e, min_e = value[3].split(':')
start_time = datetime(year, int(month), int(day), int(hour_s),
int(min_s))
end_time = datetime(year, int(month), int(day), int(hour_e),
int(min_e))
duration = math.ceil((end_time - start_time).total_seconds() / 60.0)
userId = value[5 + judge_num + 1] if len(
find_user(users, 'email', value[5 + judge_num + 1])) > 0 else None
url = value[5 + judge_num + 3]
meeting_id = value[5 + judge_num + 4]
password = value[5 + judge_num + 5]
if url and meeting_id and password:
meetings.append([url, meeting_id, password])
else:
request = {
'topic': prefix + matchName,
'type': 2,
'start_time': start_time.strftime('%Y-%m-%dT%H:%M:%S+09:00'),
'duration': duration,
'timezone': 'Asia/Tokyo',
'password': generate_password(),
'agenda': prefix + matchName,
'settings': settings
}
response = client.raw.post(f'/users/{userId}/meetings',
body=request)
if response.ok:
data = response.json()
meetings.append(
[data['join_url'], data['id'], data['password']])
else:
meetings.append([None, None, None])
start = gsutils.rowcol_to_a1(3, 6 + judge_num + 3)
end = gsutils.rowcol_to_a1(2 + len(values), 6 + judge_num + 5)
sheet.batch_update([{
'range': f'{start}:{end}',
'values': meetings
}],
value_input_option='USER_ENTERED')
def generate_ballot(credentials: Credentials, file_id: str,
sheet_index_matches: int, sheet_index_vote: int,
judge_num: int, ballot_config: Dict[str, Any]):
"""対戦表に基づき、勝敗・ポイント記入シートを生成する
:param credentials: Google の認証情報
:type credentials: Credentials
:param file_id: 管理用スプレッドシートのID
:type file_id: str
:param sheet_index_matches: 対戦表シートのインデックス
:type sheet_index_matches: int
:param sheet_index_vote: 投票シートのインデックス
:type sheet_index_vote: int
:param judge_num: ジャッジの人数
:type judge_num: int
:param ballot_config: 勝敗・ポイント記入シートの参照関係設定
:type ballot_config: Dict[str, Any]
"""
gc = gspread.authorize(credentials)
book = gc.open_by_key(file_id)
sheet_matches = WorksheetEx.cast(book.get_worksheet(sheet_index_matches))
values = sheet_matches.get_all_values()
values.pop(0)
values.pop(0)
sheet_vote = WorksheetEx.cast(book.get_worksheet(sheet_index_vote))
row_count = len(sheet_vote.col_values(1))
if row_count > 1:
sheet_vote.delete_rows(2, row_count)
sheet_vote = WorksheetEx.cast(book.get_worksheet(sheet_index_vote))
votes = []
new_ballots = []
for i, value in enumerate(values):
ballots = []
for j in range(judge_num):
new_book = gc.copy(ballot_config['template'])
ballots.append(new_book.url)
new_sheet = WorksheetEx.cast(new_book.get_worksheet(0))
new_book.batch_update({
'requests': [{
'updateSpreadsheetProperties': {
'properties': {
'title':
f"{ballot_config['title']} {value[0]} #{j}"
},
'fields': 'title'
}
}]
})
gauth = GoogleAuth()
gauth.credentials = credentials
gdrive = GoogleDrive(gauth)
gfile = gdrive.CreateFile({'id': new_book.id})
gfile.FetchMetadata(fetch_all=True)
gfile['parents'] = [{'id': ballot_config['folder']}]
gfile.Upload()
row = 2 + j + judge_num * i
vote = [''] * 12
vote[1] = j
vote[2] = value[6 + j]
vote[
4] = f'=IF({gsutils.rowcol_to_a1(row,10)}={gsutils.rowcol_to_a1(row,4)},1,0)'
vote[
7] = f'=IF({gsutils.rowcol_to_a1(row,10)}={gsutils.rowcol_to_a1(row,7)},1,0)'
for link in ballot_config['to_vote']:
vote[link[
1]] = f'=IMPORTRANGE("{new_book.id}","{new_sheet.title}!{link[0]}")'
pass
votes.append(vote)
for link in ballot_config['to_ballot']:
if type(link[0]) == int:
if len(link) >= 3 and link[2]:
new_sheet.update_acell(link[1], value[link[0] + j])
else:
new_sheet.update_acell(link[1], value[link[0]])
elif type(link[0]) == list:
options = [value[x] for x in link[0]]
new_sheet.set_data_validation(
link[1],
WorksheetEx.conditiontype.ONE_OF_LIST,
options,
strict=True,
custom_ui=True)
new_ballots.append(ballots)
sheet_vote.append_rows(votes,
value_input_option='USER_ENTERED',
insert_data_option='INSERT_ROWS')
start = gsutils.rowcol_to_a1(3, 7)
end = gsutils.rowcol_to_a1(2 + len(new_ballots), 6 + judge_num)
target_range = f'{start}:{end}' if judge_num > 1 or len(
new_ballots) > 2 else f'{start}'
judges = sheet_matches.get(target_range)
if (len(judges)) > 0:
new_values = [[
f'=HYPERLINK("{col}","{judges[i][j]}")'
for j, col in enumerate(row)
] for i, row in enumerate(new_ballots)]
sheet_matches.batch_update([{
'range': f'{start}:{end}',
'values': new_values
}],
value_input_option='USER_ENTERED')
pass
def test_addr_converters(self):
for row in range(1, 257):
for col in range(1, 512):
addr = utils.rowcol_to_a1(row, col)
(r, c) = utils.a1_to_rowcol(addr)
self.assertEqual((row, col), (r, c))
def test_rowcol_to_a1(self):
self.assertEqual(utils.rowcol_to_a1(3, 731), 'ABC3')
self.assertEqual(utils.rowcol_to_a1(1, 104), 'CZ1')
