def test_get_available_sheets_to_column_index_to_name(self):
# To mock different return values depending on input args, we use side effects with this method
spreadsheet_id = "123"
sheet1 = "s1"
sheet1_first_row = ["1", "2", "3", "4"]
# Since pytest and unittest don't give a clean way to mock responses for exact input arguments,
# we use .side_effect to achieve this. This dict structure is spreadsheet_id -> includeGridData -> ranges
def mock_client_call(spreadsheetId, includeGridData, ranges=None):
if spreadsheetId != spreadsheet_id:
return None
# the spreadsheet only contains sheet1
elif not includeGridData and ranges is None:
mocked_return = Spreadsheet(spreadsheetId=spreadsheet_id, sheets=[Sheet(properties=SheetProperties(title=sheet1))])
elif includeGridData and ranges == f"{sheet1}!1:1":
mocked_return = Spreadsheet(
spreadsheetId=spreadsheet_id,
sheets=[Sheet(data=[GridData(rowData=[RowData(values=[CellData(formattedValue=v) for v in sheet1_first_row])])])],
)
m = Mock()
m.execute.return_value = mocked_return
return m
client = Mock()
client.get.side_effect = mock_client_call
with patch.object(GoogleSheetsClient, "__init__", lambda s, credentials, scopes: None):
sheet_client = GoogleSheetsClient({"fake": "credentials"}, ["auth_scopes"])
sheet_client.client = client
actual = Helpers.get_available_sheets_to_column_index_to_name(
sheet_client, spreadsheet_id, {sheet1: frozenset(sheet1_first_row), "doesnotexist": frozenset(["1", "2"])}
)
expected = {sheet1: {0: "1", 1: "2", 2: "3", 3: "4"}}
self.assertEqual(expected, actual)
def test_get_first_row(self):
spreadsheet_id = "123"
sheet = "s1"
expected_first_row = ["1", "2", "3", "4"]
fake_response = Spreadsheet(
spreadsheetId=spreadsheet_id,
sheets=[
Sheet(data=[
GridData(rowData=[
RowData(values=[
CellData(formattedValue=v)
for v in expected_first_row
])
])
])
],
)
client = Mock()
client.get.return_value.execute.return_value = fake_response
with patch.object(GoogleSheetsClient, "__init__",
lambda s, credentials, scopes: None):
sheet_client = GoogleSheetsClient({"fake": "credentials"},
["auth_scopes"])
sheet_client.client = client
actual = Helpers.get_first_row(sheet_client, spreadsheet_id, sheet)
self.assertEqual(expected_first_row, actual)
client.get.assert_called_with(spreadsheetId=spreadsheet_id,
includeGridData=True,
ranges=f"{sheet}!1:1")
def _create_spreadsheet(self, sheets_client: GoogleSheetsClient) -> str:
"""
:return: spreadsheetId
"""
request = {
"properties": {"title": "integration_test_spreadsheet"},
"sheets": [{"properties": {"title": "sheet1"}}, {"properties": {"title": "sheet2"}}],
}
spreadsheet = Spreadsheet.parse_obj(sheets_client.create(body=request))
spreadsheet_id = spreadsheet.spreadsheetId
rows = [["header1", "irrelevant", "header3", "", "ignored"]]
rows.extend([f"a{i}", "dontmindme", i] for i in range(320))
rows.append(["lonely_left_value", "", ""])
rows.append(["", "", "lonelyrightvalue"])
rows.append(["", "", ""])
rows.append(["orphan1", "orphan2", "orphan3"])
sheets_client.update_values(
spreadsheetId=spreadsheet_id,
body={"data": {"majorDimension": "ROWS", "values": rows, "range": "sheet1"}, "valueInputOption": "RAW"},
)
sheets_client.update_values(
spreadsheetId=spreadsheet_id,
body={"data": {"majorDimension": "ROWS", "values": rows, "range": "sheet2"}, "valueInputOption": "RAW"},
)
return spreadsheet_id
def test_get_sheets_in_spreadsheet(self):
spreadsheet_id = "id1"
expected_sheets = ["s1", "s2"]
client = Mock()
client.get.return_value.execute.return_value = Spreadsheet(
spreadsheetId=spreadsheet_id, sheets=[Sheet(properties=SheetProperties(title=t)) for t in expected_sheets]
)
with patch.object(GoogleSheetsClient, "__init__", lambda s, credentials, scopes: None):
sheet_client = GoogleSheetsClient({"fake": "credentials"}, ["auth_scopes"])
sheet_client.client = client
actual_sheets = Helpers.get_sheets_in_spreadsheet(sheet_client, spreadsheet_id)
self.assertEqual(expected_sheets, actual_sheets)
client.get.assert_called_with(spreadsheetId=spreadsheet_id, includeGridData=False)
def setup(self) -> None:
Path(self._get_tmp_dir()).mkdir(parents=True, exist_ok=True)
sheets_client = GoogleSheetsClient(self._get_creds(), SCOPES)
spreadsheet_id = self._create_spreadsheet(sheets_client)
self._write_spreadsheet_id(spreadsheet_id)
