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)