class TestRangeMaxRowColumn: http_sheets_mocks = mock_spreadsheet_instance() spreadsheet = SpreadsheetApp(http=http_sheets_mocks).open_by_id('some_id') sheet = spreadsheet.sheets[0] def test_max_coordinates_a1(self): cell = self.sheet.get_range_from_a1('A1') assert cell.get_row() == 1 assert cell.get_column() == 1 assert cell.get_max_row() == 1 assert cell.get_max_column() == 1 def test_max_coordinates_a1_b4(self): range_ = self.sheet.get_range_from_a1('A1:B4') assert range_.get_row() == 1 assert range_.get_column() == 1 assert range_.get_max_row() == 4 assert range_.get_max_column() == 2 def test_max_coordinates_a3_b4(self): range_ = self.sheet.get_range_from_a1('B3:D4') assert range_.get_row() == 3 assert range_.get_column() == 2 assert range_.get_max_row() == 4 assert range_.get_max_column() == 4
class TestSpreadsheet: http_sheets_mocks = mock_spreadsheet_instance( ["add_sheets.json", "duplicate_sheets.json"]) spreadsheet = SpreadsheetApp(http=http_sheets_mocks).open_by_id('some_id') def test_get_sheets(self): sheets = self.spreadsheet.sheets assert len(sheets) == 2 for sheet in sheets: assert isinstance(sheet, Sheet) def test_create_sheets(self): self.spreadsheet.create_sheets(["test_sheet", "test_sheet_2"]) assert len(self.spreadsheet.sheets) == 4 def test_duplicate_sheet(self): # Important! This test needs to be executed after test_create_sheets, as it clones that sheet # self.spreadsheet.duplicate_sheet(new_sheet_name="cloned_sheet", sheet_name="test_sheet") assert len(self.spreadsheet.sheets) == 5 def test_add_sheets_from_response(self): dummy_response_create = json.loads(open_fixture("add_sheets.json")) self.spreadsheet._add_sheets_from_response( response=dummy_response_create, reply_type="addSheet") assert len(self.spreadsheet.sheets) == 7 dummy_response_duplicate = json.loads( open_fixture("duplicate_sheets.json")) self.spreadsheet._add_sheets_from_response( response=dummy_response_duplicate, reply_type="duplicateSheet") assert len(self.spreadsheet.sheets) == 8
class TestDatetimeValues: http_mocks = mock_spreadsheet_instance( fixtures=["get_values_datetime.json"]) client = SpreadsheetApp(http=http_mocks) spreadsheet = client.open_by_id('some_id') sheet = spreadsheet.get_sheet_by_name("people") data_range = sheet.get_range_from_a1("A1:B1") values = data_range.get_values() def test_length_values(self): assert len(self.values) == 1 assert len(self.values[0]) == 2 def test_first_value(self): assert self.values[0][0] == "foo" def test_is_datetime(self): assert isinstance(self.values[0][1], datetime.datetime) def test_datetime_value(self): test_date = self.values[0][1] assert test_date.year == 2021 assert test_date.month == 5 assert test_date.day == 1
def create_values_fixture(path_to_secret, spreadsheet_id, target_range, fixture_name): client = SpreadsheetApp(path_to_secret) request = client.sheet_service.spreadsheets().values().get( spreadsheetId=spreadsheet_id, range=target_range) folder = os.path.join(os.path.dirname(__file__), 'fixtures') response = request.execute() with open(os.path.join(folder, fixture_name), 'w') as f: content = json.dumps(response) f.write(content)
def create_fixture(path_to_secret, spreadsheet_id, target_range, field_mask, fixture_name): client = SpreadsheetApp(path_to_secret) request = client.sheet_service.spreadsheets().get( spreadsheetId=spreadsheet_id, includeGridData=True, ranges=[target_range], fields=field_mask) folder = os.path.join(os.path.dirname(__file__), 'fixtures') response = request.execute() with open(os.path.join(folder, fixture_name), 'w') as f: content = json.dumps(response) f.write(content)
class TestCellRange: http_sheets_mocks = mock_range_instance() client = SpreadsheetApp(http=http_sheets_mocks) data_range = client.open_by_id('spreadsheet id').get_sheet_by_name('people').get_data_range() def test_a1(self): assert self.data_range.a1 == 'people!A1:D21' def test_get_cell(self): assert self.data_range.get_cell(1, 1).a1 == 'people!A1' assert self.data_range.get_cell(1, 2).a1 == 'people!B1' assert self.data_range.get_cell(2, 1).a1 == 'people!A2'
class TestSheet: http_sheets_mocks = mock_range_instance() sheet = SpreadsheetApp(http=http_sheets_mocks).open_by_id("some_id").get_sheet_by_name("people") def test_sheet_properties(self): assert self.sheet.name == "people" assert self.sheet.sid == 0 assert type(self.sheet.grid_properties) == dict assert type(self.sheet.batches) == list assert len(self.sheet.batches) == 0 def test_max_rows_columns(self): assert self.sheet.get_max_rows() == 1000 assert self.sheet.get_max_columns() == 26
class TestGettersFromDataRange: fixtures = ['get_backgrounds.json', 'get_notes.json', 'get_fonts.json'] http_sheets_mocks = mock_range_instance(fixtures) client = SpreadsheetApp(http=http_sheets_mocks) data_range = client.open_by_id('spreadsheet id').get_sheet_by_name('people').get_data_range() def test_a1_notation_is_right(self): assert self.data_range.a1 == "people!A1:D21" def test_get_backgrounds(self): backgrounds = self.data_range.get_backgrounds() assert type(backgrounds) == list assert len(backgrounds) == self.data_range.coordinates.number_of_rows for row in backgrounds: assert len(row) == self.data_range.coordinates.number_of_columns
class TestSheetCreationMethodReturns: http_sheets_mocks = mock_spreadsheet_instance(["add_sheets.json", "duplicate_sheets.json"]) spreadsheet = SpreadsheetApp(http=http_sheets_mocks).open_by_id('some_id') def test_create_sheets_types(self): new_sheets = self.spreadsheet.create_sheets(["test_sheet", "test_sheet_2"]) assert isinstance(new_sheets, list) assert isinstance(new_sheets[0], Sheet) assert new_sheets[0].name == 'test_sheet' assert isinstance(new_sheets[1], Sheet) assert new_sheets[1].name == 'test_sheet_2' def test_duplicate_sheet_type(self): # Important! This test needs to be executed after test_create_sheets, as it clones that sheet # duplicated_sheet = self.spreadsheet.duplicate_sheet(new_sheet_name="cloned_sheet", sheet_name="test_sheet") assert isinstance(duplicated_sheet, Sheet) assert duplicated_sheet.name == 'cloned_sheet'
class TestWrongRowAndColumnValues: http_sheets_mocks = mock_range_instance() client = SpreadsheetApp(http=http_sheets_mocks) spreadsheet = client.open_by_id('some_id') sheet = spreadsheet.get_sheet_by_name("people") def test_invalid_row(self): with pytest.raises(RowOrColumnEqualsZeroError): self.sheet.get_range(row=0, column=1) def test_invalid_column(self): with pytest.raises(RowOrColumnEqualsZeroError): self.sheet.get_range(row=1, column=0) def test_invalid_row_and_column(self): with pytest.raises(RowOrColumnEqualsZeroError): self.sheet.get_range(row=0, column=0)
class TestModelClients: http_sheets_mocks = mock_range_instance() client = SpreadsheetApp(http=http_sheets_mocks) spreadsheet = client.open_by_id('some_id') sheet = spreadsheet.get_sheet_by_name("people") my_range = sheet.get_range(1, 1) def test_instances(self): assert isinstance(self.client, SpreadsheetApp) assert isinstance(self.spreadsheet, Spreadsheet) assert isinstance(self.sheet, Sheet) assert isinstance(self.my_range, Range) def test_client_is_in_every_object(self): assert self.client is self.spreadsheet.client assert self.client is self.sheet.client assert self.client is self.my_range.client
class TestGridRange: http_sheets_mocks = mock_spreadsheet_instance() spreadsheet = SpreadsheetApp(http=http_sheets_mocks).open_by_id('some_id') sheet = spreadsheet.sheets[0] def test_grid_range_one_cell(self): cell = self.sheet.get_range_from_a1('A1') assert cell.a1 == 'people!A1' cell_grid_range = cell.get_grid_range() assert cell_grid_range['startRowIndex'] == 0 assert cell_grid_range['endRowIndex'] == 1 assert cell_grid_range['startColumnIndex'] == 0 assert cell_grid_range['endColumnIndex'] == 1 def test_grid_range_multiple_cells(self): range_ = self.sheet.get_range_from_a1('A3:B4') assert range_.a1 == 'people!A3:B4' grid_range = range_.get_grid_range() assert grid_range['startRowIndex'] == 2 assert grid_range['endRowIndex'] == 4 assert grid_range['startColumnIndex'] == 0 assert grid_range['endColumnIndex'] == 2 def test_offsets(self): range = self.sheet.get_range_from_a1('C5:E20') assert range.coordinates.row == 5 assert range.coordinates.column == 3 assert range.coordinates.number_of_rows == 16 assert range.coordinates.number_of_columns == 3 bottom_offset = range.offset(row_offset=5, column_offset=5) assert bottom_offset.coordinates.row == 10 assert bottom_offset.coordinates.column == 8 assert bottom_offset.coordinates.number_of_rows == 16 assert bottom_offset.coordinates.number_of_columns == 3 assert bottom_offset.a1 == "people!H10:J25" top_offset = range.offset(row_offset=-3, column_offset=-2) assert top_offset.coordinates.row == 2 assert top_offset.coordinates.column == 1 assert top_offset.coordinates.number_of_rows == 16 assert top_offset.coordinates.number_of_columns == 3 assert top_offset.a1 == "people!A2:C17" def test_offset_trims(self): range = self.sheet.get_range_from_a1('B1:D20') assert range.coordinates.row == 1 assert range.coordinates.column == 2 assert range.coordinates.number_of_rows == 20 assert range.coordinates.number_of_columns == 3 top_trimmed_range = range.offset(row_offset=2, column_offset=0, num_rows=18, num_columns=3) assert top_trimmed_range.coordinates.row == 3 assert top_trimmed_range.coordinates.column == 2 assert top_trimmed_range.coordinates.number_of_rows == 18 assert top_trimmed_range.coordinates.number_of_columns == 3 assert top_trimmed_range.a1 == "people!B3:D20" bottom_trimmed_range = range.offset(row_offset=0, column_offset=0, num_rows=15, num_columns=2) assert bottom_trimmed_range.coordinates.row == 1 assert bottom_trimmed_range.coordinates.column == 2 assert bottom_trimmed_range.coordinates.number_of_rows == 15 assert bottom_trimmed_range.coordinates.number_of_columns == 2 assert bottom_trimmed_range.a1 == "people!B1:C15" sides_trimmed_range = range.offset( row_offset=0, column_offset=1, num_rows=range.coordinates.number_of_rows, num_columns=1) assert sides_trimmed_range.coordinates.row == 1 assert sides_trimmed_range.coordinates.column == 3 assert sides_trimmed_range.coordinates.number_of_rows == 20 assert sides_trimmed_range.coordinates.number_of_columns == 1 assert sides_trimmed_range.a1 == "people!C1:C20" def test_invalid_offset_ranges(self): range = self.sheet.get_range_from_a1('A5:B10') assert range.coordinates.row == 5 assert range.coordinates.column == 1 assert range.coordinates.number_of_rows == 6 assert range.coordinates.number_of_columns == 2 with pytest.raises(ValueError): range.offset(row_offset=-5, column_offset=0) with pytest.raises(ValueError): range.offset(row_offset=0, column_offset=-1) with pytest.raises(ValueError): range.offset(row_offset=0, column_offset=0, num_rows=0, num_columns=2) with pytest.raises(ValueError): range.offset(row_offset=0, column_offset=0, num_rows=6, num_columns=-2)