def test_a1_to_grid_range_improper_range(self):
expected_single_cell = {
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 1,
}
actual_single_cell = utils.a1_range_to_grid_range("A1")
expected_single_column = {"startColumnIndex": 0, "endColumnIndex": 1}
actual_single_column = utils.a1_range_to_grid_range("A")
expected_single_row = {"startRowIndex": 0, "endRowIndex": 1}
actual_single_row = utils.a1_range_to_grid_range("1")
expected_with_sheet = {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 1,
}
actual_with_sheet = utils.a1_range_to_grid_range("A1", sheet_id=0)
self.assertEqual(actual_single_cell, expected_single_cell)
self.assertEqual(actual_single_column, expected_single_column)
self.assertEqual(actual_single_row, expected_single_row)
self.assertEqual(actual_with_sheet, expected_with_sheet)
def test_a1_to_grid_range_simple(self):
expected_single_dimension = {
"startRowIndex": 0,
"endRowIndex": 10,
"startColumnIndex": 0,
"endColumnIndex": 1,
}
actual_single_dimension = utils.a1_range_to_grid_range("A1:A10")
expected_two_dimensional = {
"startRowIndex": 2,
"endRowIndex": 4,
"startColumnIndex": 0,
"endColumnIndex": 2,
}
actual_two_dimensional = utils.a1_range_to_grid_range("A3:B4")
expected_with_sheet_id = {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 10,
"startColumnIndex": 0,
"endColumnIndex": 1,
}
actual_with_sheet_id = utils.a1_range_to_grid_range("A1:A10", sheet_id=0)
self.assertEqual(actual_single_dimension, expected_single_dimension)
self.assertEqual(actual_two_dimensional, expected_two_dimensional)
self.assertEqual(actual_with_sheet_id, expected_with_sheet_id)
def test_a1_to_grid_range_other_directions(self):
from_top_left = utils.a1_range_to_grid_range("C2:D4")
from_bottom_right = utils.a1_range_to_grid_range("D4:C2")
from_top_right = utils.a1_range_to_grid_range("D2:C4")
from_bottom_left = utils.a1_range_to_grid_range("C4:D2")
self.assertEqual(from_top_left, from_bottom_right)
self.assertEqual(from_top_left, from_bottom_left)
self.assertEqual(from_top_left, from_top_right)
def merge_cells(self, ranges: (str, list)) -> None:
request = []
for current_range in [ranges] if isinstance(ranges, str) else ranges:
grid_range = a1_range_to_grid_range(current_range, self.sheet.id)
request.append({"mergeCells": {"range": grid_range}})
body = {"requests": request}
self.sheet.spreadsheet.batch_update(body)
def test_a1_to_grid_range_unbounded(self):
expected_unbounded = {
"startRowIndex": 4,
"startColumnIndex": 0,
"endColumnIndex": 2,
}
actual_unbounded = utils.a1_range_to_grid_range("A5:B")
expected_full_columns = {"startColumnIndex": 0, "endColumnIndex": 2}
actual_full_columns = utils.a1_range_to_grid_range("A:B")
expected_with_sheet_id = {
"sheetId": 0,
"startRowIndex": 4,
"startColumnIndex": 0,
"endColumnIndex": 2,
}
actual_with_sheet_id = utils.a1_range_to_grid_range("A5:B", sheet_id=0)
self.assertEqual(actual_unbounded, expected_unbounded)
self.assertEqual(actual_full_columns, expected_full_columns)
self.assertEqual(actual_with_sheet_id, expected_with_sheet_id)
def set_data_validation(self,
name: str,
cond_type: str,
cond_values: List[Union[int, float, str]],
message: Union[str, None] = None,
strict: bool = False,
custom_ui: bool = False):
"""データの入力規則を追加する
:param name: データの入力規則を設定するレンジ
:type name: str
:param cond_type: 条件のタイプ
:type cond_type: str
:param cond_values: 条件の値
:type cond_values: List[Union[int, float, str]]
:param message: 入力時に表示するメッセージ, defaults to None
:type message: Union[str, None], optional
:param strict: 条件に一致しない値を拒否する, defaults to False
:type strict: bool, optional
:param custom_ui: プルダウンリストを表示する, defaults to False
:type custom_ui: bool, optional
"""
grid_range = gsutils.a1_range_to_grid_range(name, self.id)
cv = []
for v in cond_values:
if v in WorksheetEx.relativedate.__dict__.keys():
cv.append({'relativeDate': v})
elif type(v) is str:
cv.append({'userEnteredValue': v})
else:
cv.append({'userEnteredValue': str(v)})
rule = {
'condition': {
'type': cond_type,
'values': cv
},
'strict': strict,
'showCustomUi': custom_ui
}
if message is not None:
rule['inputMessage'] = message
self.spreadsheet.batch_update({
"requests": [{
"setDataValidation": {
'range': grid_range,
"rule": rule
}
}]
})
def add_gradient_format(self, name: str, grad_rule: Dict[str, Any]):
grid_range = gsutils.a1_range_to_grid_range(name, self.id)
self.spreadsheet.batch_update({
"requests": [{
"addConditionalFormatRule": {
"rule": {
'ranges': [grid_range],
'gradientRule': grad_rule
},
'index': 0
}
}]
})
def fill_down_base(
worksheet, col: (str, int), cell_value: str,
):
"""
create ranges from cols
get table length for rows
"""
if isinstance(col, int):
col = col_to_a1(col)
col = col.upper()
range1 = gu.a1_range_to_grid_range(
f"{col}{minrow}:{col}{maxrow}",
sheet_id=worksheet.id,
)
return dict(rng=range1, val=cell_value)
def set_vertical_alignment(self, ranges: (str, list),
alignment_type: VerticalAlignment) -> None:
requests = []
for current_range in [ranges] if isinstance(ranges, str) else ranges:
grid_range = a1_range_to_grid_range(current_range, self.sheet.id)
request = {
"repeatCell": {
"cell": {
"userEnteredFormat": {
"verticalAlignment": alignment_type
}
},
"range": grid_range,
"fields": "userEnteredFormat.verticalAlignment"
}
}
requests.append(request)
self.sheet.spreadsheet.batch_update({"requests": requests})
def set_wrap_strategy(self, ranges: (str, list),
wrap_strategy: WrapStrategy) -> None:
requests = []
for current_range in [ranges] if isinstance(ranges, str) else ranges:
grid_range = a1_range_to_grid_range(current_range, self.sheet.id)
request = {
"repeatCell": {
"cell": {
"userEnteredFormat": {
"wrapStrategy": wrap_strategy
}
},
"range": grid_range,
"fields": "userEnteredFormat.wrapStrategy"
}
}
requests.append(request)
self.sheet.spreadsheet.batch_update({"requests": requests})
def add_conditional_format(self, name: str, cond_type: str,
cond_values: List[Union[int, float, str]],
cond_format: dict):
"""条件付き書式を追加する
:param name: 条件付き書式を設定するレンジ
:type name: str
:param cond_type: 条件のタイプ
:type cond_type: str
:param cond_values: 条件の値
:type cond_values: List[Union[int, float, str]]
:param cond_format: 書式
:type cond_format: dict
"""
grid_range = gsutils.a1_range_to_grid_range(name, self.id)
cv = []
for v in cond_values:
if v in WorksheetEx.relativedate.__dict__.keys():
cv.append({'relativeDate': v})
elif type(v) is str:
cv.append({'userEnteredValue': v})
else:
cv.append({'userEnteredValue': str(v)})
self.spreadsheet.batch_update({
"requests": [{
"addConditionalFormatRule": {
"rule": {
'ranges': [grid_range],
'booleanRule': {
'condition': {
'type': cond_type,
'values': cv
},
'format': cond_format
}
},
'index': 0
}
}]
})
def unmerge_cells(self, a1_range):
grid_range = a1_range_to_grid_range(a1_range, self.sheet.id)
body = {"requests": [{"unmergeCells": {"range": grid_range}}]}
self.sheet.spreadsheet.batch_update(body)
def add_protected_range(
self,
name: str,
editor_users_emails: List[str] = None,
editor_groups_emails: List[str] = None,
description: str = None,
warning_only: bool = False,
requesting_user_can_edit: bool = False,
):
"""保護された範囲を追加する
:param name: 範囲の名前
:type name: str
:param editor_users_emails: 編集を許可するユーザーのメールアドレスの list, defaults to None
:type editor_users_emails: List[str], optional
:param editor_groups_emails: 編集を許可するグループのメールアドレスの list, defaults to None
:type editor_groups_emails: List[str], optional
:param description: 説明, defaults to None
:type description: str, optional
:param warning_only: 編集を警告するだけか, defaults to False
:type warning_only: bool, optional
:param requesting_user_can_edit: ユーザーが編集をリクエストできるか, defaults to False
:type requesting_user_can_edit: bool, optional
:raises PermissionError: spreadsheet に対して権限のないユーザーを指定しようとした場合に例外を発生
"""
permitted_email_address = [
permission.get('emailAddress')
for permission in self.client.list_permissions(self.spreadsheet.id)
if permission.get('emailAddress')
]
editors_emails = editor_users_emails or []
for email in editors_emails:
if email not in permitted_email_address:
raise PermissionError(
f'{email} is not permitted to edit this spreadsheet.')
editor_groups_emails = editor_groups_emails or []
for email in editor_groups_emails:
if email not in permitted_email_address:
raise PermissionError(
f'{email} is not permitted to edit this spreadsheet.')
grid_range = gsutils.a1_range_to_grid_range(name, self.id)
body = {
"requests": [{
"addProtectedRange": {
'protectedRange': {
"range": grid_range,
"description": description,
"warningOnly": warning_only,
"requestingUserCanEdit": requesting_user_can_edit,
"editors": {
"users": editors_emails,
"groups": editor_groups_emails,
},
}
}
}]
}
self.spreadsheet.batch_update(body)