def delete_sheet(workbook: Spreadsheet, sheet: Union[Worksheet, None]) -> dict:
"""シートを削除する
Args:
workbook (Spreadsheet): `gspread`で定義されている`Spreadsheet`モデル
sheet (Union[Worksheet, None]): `gspread`で定義されている`Worksheet`モデル
Returns:
dict: 削除結果
e.g. {'action:': action, 'result': 'Success', 'message': 'Deleted a sheet: {sheet title}, 'data': workbook}
"""
action = 'delete_sheet'
response = init_response(action)
if sheet:
workbook.del_worksheet(sheet)
response = {
'action:': action,
'result': 'Success',
'message': 'Deleted a sheet: {}'.format(sheet.title),
'data': workbook
}
else:
response = {
'action:': action,
'result': 'Failure',
'message': 'The sheet does not exist.',
'data': workbook
}
return response
def open(self, title):
"""
Opens a spreadsheet.
:param title: A title of a spreadsheet.
:type title: str
:returns: a :class:`~gspread.models.Spreadsheet` instance.
If there's more than one spreadsheet with same title the first one
will be opened.
:raises gspread.SpreadsheetNotFound: if no spreadsheet with
specified `title` is found.
>>> c = gspread.authorize(credentials)
>>> c.open('My fancy spreadsheet')
"""
try:
properties = finditem(
lambda x: x["name"] == title, self.list_spreadsheet_files(title)
)
# Drive uses different terminology
properties["title"] = properties["name"]
return Spreadsheet(self, properties)
except StopIteration:
raise SpreadsheetNotFound
def get_sheets(workbook: Spreadsheet) -> dict:
"""Googleスプレッドシート内にあるシートのリストを取得する
Args:
workbook (Spreadsheet): `gspread`で定義されている`Spreadsheet`モデル
Returns:
dict: 取得結果 `data`は`gspread`で定義されている`Worksheet`モデルのリスト
e.g. {'action:': create_sheet, 'result': 'Success', 'message': 'Created a sheet: {sheet title}', 'data': list}
"""
action = 'get_sheet_titles'
response = init_response(action)
sheets = workbook.worksheets()
if sheets:
response = {
'action:': action,
'result': 'Success',
'message': 'Got the sheets.',
'data': sheets
}
else:
response = {
'action:': action,
'result': 'Failure',
'message': 'Any sheet does not exist.',
'data': None
}
return response
def get_sheet(workbook: Spreadsheet, sheet_identifier: Union[str,
int]) -> dict:
"""シートをWorksheetとして取得する。
Args:
workbook (Spreadsheet): `gspread`で定義されている`Spreadsheet`モデル
sheet_identifier (Union[str, int]): シート識別子 `str`の場合はシートの名前で、`int`の場合は何番目のシートかを指定して取得する
e.g. 'シート1' or 0
Returns:
dict: 取得結果 `data`は`gspread`で定義されている`Worksheet`モデル
e.g. {'action:': action, 'result': 'Success', 'message': 'Got a sheet.', 'data': Worksheet}
"""
action = 'get_sheet'
response = init_response(action)
try:
if type(sheet_identifier) == str:
sheet = workbook.worksheet(sheet_identifier)
response = {
'action:': action,
'result': 'Success',
'message': 'Got a sheet.',
'data': sheet
}
elif type(sheet_identifier) == int:
sheet = workbook.get_worksheet(sheet_identifier)
response = {
'action:': action,
'result': 'Success',
'message': 'Got a sheet.',
'data': sheet
}
else:
{
'action:': action,
'result': 'Failure',
'message': 'The sheet does not exist.',
'data': None
}
except Exception:
{
'action:': action,
'result': 'Failure',
'message': 'Unknown error.',
'data': None
}
return response
def create_sheet(workbook: Spreadsheet, title: str, size: list) -> dict:
"""新しいシートを作成する
Args:
workbook (Spreadsheet): `gspread`で定義されている`Spreadsheet`モデル
title (str): 作成するシートのタイトル
size (list): 作成するシートのサイズ[row, col]
e.g. [10, 20]
Returns:
dict: 作成結果 `data`は`gspread`で定義されている`Worksheet`モデル
e.g. {'action:': create_sheet, 'result': 'Success', 'message': 'Created a sheet: {sheet title}', 'data': sheet}
"""
action = 'create_sheet'
response = init_response(action)
sheet_titles = get_sheet_titles(workbook)
if title not in sheet_titles and len(size) == 2:
workbook.add_worksheet(title=title, rows=size[0], cols=size[1])
new_sheet = get_sheet(workbook, title)
response = {
'action:': action,
'result': 'Success',
'message': 'Created a sheet: {}'.format(title),
'data': new_sheet
}
elif title in sheet_titles:
sheet = get_sheet(workbook, title)
response = {
'action:': action,
'result': 'Failure',
'message': 'The sheet already exists.',
'data': sheet
}
elif len(size) != 2:
response = {
'action:': action,
'result': 'Failure',
'message': 'The size specification is wrong.',
'data': None
}
else:
response = {
'action:': action,
'result': 'Failure',
'message': 'Undefined error',
'data': None
}
return response
def test_share_01(self, mocker):
"""Ensure the document is not shared with invalid contributors."""
contributors = ['*****@*****.**']
g = GSheets(
self.fake.file_path(depth=1, category=None, extension='json'),
contributors)
g.spreadsheet = Spreadsheet(None, None)
g.spreadsheet.share = mocker.MagicMock()
g.share()
assert not g.spreadsheet.share.called
def test_share_00(self, mocker):
"""Ensure the document is shared with valid contributors."""
contributors = [
'[email protected]:user:writer', '[email protected]:user:reader'
]
g = GSheets(
self.fake.file_path(depth=1, category=None, extension='json'),
contributors)
g.spreadsheet = Spreadsheet(None, None)
g.spreadsheet.share = mocker.MagicMock()
g.share()
assert g.spreadsheet.share.call_count == len(contributors)
def test_add_csv_data_00(self, mocker):
"""Ensure the data is appended to the worksheet."""
fake_fields = self.fake.pylist(10, True, str)
fake_data = []
for _ in range(self.fake.random_digit()):
fake_entry = {}
for field in fake_fields:
fake_entry[field] = self.fake.word()
fake_data.append(fake_entry)
g = GSheets(
self.fake.file_path(depth=1, category=None, extension='json'), [])
g.spreadsheet = Spreadsheet(None, None)
g.worksheet = mocker.MagicMock()
g.worksheet.append_row = mocker.MagicMock()
g.add_csv_data(fake_fields, fake_data)
assert not g.worksheet.append_row.call_count == len(fake_data)
def build_sheet_dict(wb_name: str, wb: Spreadsheet) -> Tuple[str, SheetDict]:
global COUNTER
print(f"Building sheet_dict for \'{wb.title}\'...")
sheet_dict: SheetDict = {}
worksheets: List[Worksheet] = wb.worksheets()
for i, ws in enumerate(worksheets):
entries_dict = {}
entries_dict['sheet'] = ws
entries_dict['sheet_idx'] = i
sheet_dict[ws.title] = entries_dict
with open(SHEET_DICT_TEMPLATE, 'r') as f:
contents = f.read()
template_dict: SheetDict = json.loads(contents)
for sheet in sheet_dict:
sheet_dict[sheet]['header_info_dict'] = template_dict[sheet][
'header_info_dict']
COUNTER += 1
print(f"{COUNTER}. \'{wb.title}\' template built!")
return wb_name, sheet_dict