```from pygsheets.exceptions import InvalidArgumentValue, IncorrectCellLabel
import re

"""
Represents the address of a cell.
This can also be unbound in an axes. So 'A' is also a valid address but this
requires explict setting of param `allow_non_single`.
Integer Indexes start from 1.

>>> a.label
A2
>>> a[0]
1
>>> a[1]
2
>>> a.label
A2
>>> a + (0,1)
>>> a == (2, 2)
True
>>> a = Address((None, 1), True)
"""

_MAGIC_NUMBER = 64

def __init__(self, value, allow_non_single=False):
self._is_single = True
self.allow_non_single = allow_non_single

if isinstance(value, str):
self._value = self._label_to_coordinates(value)
elif isinstance(value, tuple) or isinstance(value, list):
assert len(value) == 2, 'tuple should be of length 2'
assert type(value[0]) is int or value[0] is None, 'address row should be int'
assert type(value[1]) is int or value[1] is None, 'address col should be int'
self._value = tuple(value)
self._validate()
elif not value and self.allow_non_single:
self._value = (None, None)
self._validate()
self._value = self._label_to_coordinates(value.label)
else:
raise IncorrectCellLabel('Only labels in A1 notation, coordinates as a tuple or '

@property
def label(self):
""" Label of the current address in A1 format."""
return self._value_as_label()

@property
def row(self):
return self._value[0]

@property
def col(self):
return self._value[1]

@row.setter
def row(self, value):
self._value = value, self._value[1]

@col.setter
def col(self, value):
self._value = self._value[0], value

@property
def index(self):
"""Current Address in tuple format. Both axes starts at 1."""
return tuple(self._value)

def _validate(self):
if not self.allow_non_single and (self._value[0] is None or self._value[0] is None):
raise InvalidArgumentValue("Address cannot be unbounded if allow_non_single is not set.")

if self._value[0]:
row = int(self._value[0])
if row < 1:
raise InvalidArgumentValue('Address coordinates may not be below zero: ' + repr(self._value))

if self._value[1]:
col = int(self._value[1])
if col < 1:
raise InvalidArgumentValue('Address coordinates may not be below zero: ' + repr(self._value))

def _value_as_label(self):
"""Transforms tuple coordinates into a label of the form A1."""
self._validate()

row_label, column_label = '', ''
if self._value[0]:
row_label = str(self._value[0])

if self._value[1]:
col = int(self._value[1])
div = col
column_label = ''
while div:
(div, mod) = divmod(div, 26)
if mod == 0:
mod = 26
div -= 1
column_label = chr(mod + self._MAGIC_NUMBER) + column_label

return '{}{}'.format(column_label, row_label)

def _label_to_coordinates(self, label):
"""Transforms a label in A1 notation into numeric coordinates and returns them as tuple."""
m = re.match(r'([A-Za-z]*)(\d*)', label)
if m:
column_label = m.group(1).upper()
row, col = m.group(2), 0
if column_label:
for i, c in enumerate(reversed(column_label)):
col += (ord(c) - self._MAGIC_NUMBER) * (26 ** i)
col = int(col)
else:
col = None
row = int(row) if row else None
if not m or (not self.allow_non_single and not (row and col)):
raise IncorrectCellLabel('Not a valid cell label format: {}.'.format(label))
return row, col

def __repr__(self):
return '<%s %s>' % (self.__class__.__name__, str(self.label))

def __iter__(self):
return iter(self._value)

def __getitem__(self, item):
return self._value[item]

def __setitem__(self, key, value):
current_value = list(self._value)
current_value[key] = value
self._value = tuple(current_value)

if type(other) is tuple or isinstance(other, Address):
return Address((self._value[0] + other[0], self._value[1] + other[1]))
else:
raise NotImplementedError

def __sub__(self, other):
if type(other) is tuple or isinstance(other, Address):
return Address((self._value[0] - other[0], self._value[1] - other[1]))
else:
raise NotImplementedError

def __eq__(self, other):
return self.label == other.label
elif type(other) is str:
return self.label == other
elif type(other) is tuple or type(other) is list:
return self._value == tuple(other)
else:

def __ne__(self, other):
return not self.__eq__(other)

def __bool__(self):
return not (self._value[0] is None and self._value[1] is None)

__nonzero__ = __bool__

[docs]class GridRange(object):
"""
Represents a rectangular (can be unbounded) range of adresses on a sheet.
All indexes are one-based and are closed, ie the start index and the end index is inclusive
Missing indexes indicate the range is unbounded on that side.

A:B, A1:B3, 1:2 are all valid index, but A:1, 2:D are not

grange.start = (1, None) will make the range unbounded on column
grange.indexes = ((None, None), (None, None)) will make the range completely unbounded, ie. whole sheet

Example:

>>> grange = GridRange(worksheet=wks, start='A1', end='D4')
>>> grange
<GridRange Sheet1!A1:D4>
>>> grange.start = 'A' # will remove bounding in rows
<GridRange Sheet1!A:D>
>>> grange.start = 'A1' # cannot add bounding at just start
<GridRange Sheet1!A:D>
>>> grange.indexes = ('A1', 'D4') # cannot add bounding at just start
<GridRange Sheet1!A1:D4>
>>> grange.end = (3, 5) # tuples will also work
<GridRange Sheet1!A1:C5>
>>> grange.end = (None, 5) # make unbounded on rows
<GridRange Sheet1!1:5>
>>> grange.end = (None, None) # make it unbounded on one index
<GridRange Sheet1!1:1>
>>> grange.start = None # make it unbounded on both indexes
<GridRange Sheet1>
>>> grange.start = 'A1' # make it unbounded on single index,now AZ100 is bottom right cell of worksheet
<GridRange Sheet1:A1:AZ100>

"""

def __init__(self, label=None, worksheet=None, start=None, end=None, worksheet_title=None,
worksheet_id=None, propertiesjson=None):
"""
:param label: label in A1 format
:param worksheet: worksheet object this grange belongs to
:param start: start address of the range
:param end: end address of the range
:param worksheet_title: worksheet title if worksheet not given
:param worksheet_id: worksheet id if worksheet not given
:param propertiesjson: json with all range properties
"""
self._worksheet_title = worksheet_title
self._worksheet_id = worksheet_id
self._worksheet = worksheet
# if fill_bounds and self._start and not self._end:
#     if not worksheet:
#         raise InvalidArgumentValue('worksheet need to fill bounds.')
#     self._end = Address((worksheet.rows, worksheet.cols), True)
# if fill_bounds and self._end and not self._start:
if propertiesjson:
self.set_json(propertiesjson)
elif label:
else:
self._apply_index_constraints()
self._calculate_label()

@property
def start(self):
""" address of top left cell (index). """
if not self._start and self._end:
top_index = [1, 1]
if not self._end[0]:
top_index[0] = None
if not self._end[1]:
top_index[1] = None
return self._start

@start.setter
def start(self, value):
# prev = self._start, self._end
self._apply_index_constraints()
self._calculate_label()

@property
def end(self):
""" address of bottom right cell (index) """
if not self._end and self._start:
if not self._worksheet:
raise InvalidArgumentValue('worksheet is required for unbounded ranges')
bottom_index = [self._worksheet.rows, self._worksheet.cols]
if not self._start[0]:
bottom_index[0] = None
if not self._start[1]:
bottom_index[1] = None
return self._end

@end.setter
def end(self, value):
# prev = self._start, self._end
self._apply_index_constraints()
self._calculate_label()

@property
def indexes(self):
""" Indexes of this range as a tuple """
return self.start, self.end

@indexes.setter
def indexes(self, value):
if type(value) is not tuple:
self._apply_index_constraints()
self._calculate_label()

@property
def label(self):
""" Label in A1 notation format """
return self._calculate_label()

@label.setter
def label(self, value):
if type(value) is not str:
raise InvalidArgumentValue('non string value for label')

@property
def worksheet_id(self):
""" Id of woksheet this range belongs to """
if self._worksheet:
return self._worksheet.id
return self._worksheet_id

@worksheet_id.setter
def worksheet_id(self, value):
if self._worksheet:
if self._worksheet.id == value:
return
else:
raise InvalidArgumentValue("This range already has a worksheet with different id set.")
self._worksheet_id = value

@property
def worksheet_title(self):
""" Title of woksheet this range belongs to """
if self._worksheet:
return self._worksheet.title
return self._worksheet_title

@worksheet_title.setter
def worksheet_title(self, value):
if not value:
return
if self._worksheet:
if self._worksheet.title == value:
return
else:
raise InvalidArgumentValue("This range already has a worksheet with different title set.")
self._worksheet_title = value
self._calculate_label()

[docs]    @staticmethod
def create(data, wks=None):
"""
create a Gridrange from various type of data
:param data: can be string in A format,tuple or list, dict in GridRange format, GridRange object
:param wks: worksheet to link to (optional)
:return: GridRange object
"""
if isinstance(data, GridRange):
grange = data
elif isinstance(data, str):
grange = GridRange(label=data, worksheet=wks)
elif isinstance(data, tuple) or isinstance(data, list):
if len(data) < 2: raise InvalidArgumentValue("start and end required")
grange = GridRange(start=data[0], end=data[1], worksheet=wks)
elif isinstance(data, dict):
grange = GridRange(propertiesjson=data, worksheet=wks)
else:
raise InvalidArgumentValue(data)
if wks:
grange.set_worksheet(wks)
return grange

[docs]    def set_worksheet(self, value):
""" set the worksheet of this grid range. """
self._worksheet = value
self._worksheet_id = value.id
self._worksheet_title = value.title
self._calculate_label()

def _apply_index_constraints(self):
if not self._start or not self._end:
return

# # If range was single celled, and one is set to none, make both unbound
# if prev and prev[0] == prev[1]:
#     if not self._start:
#         self._end = self._start
#         return
#     if not self._end:
#         self._start = self._end
#         return
# # if range is not single celled, and one index is unbounded make it single celled
# if not self._end:
#     self._end = self._start
# if not self._start:
#     self._start = self._end

# Check if unbound on different axes
if ((self._start[0] and not self._start[1]) and (not self._end[0] and self._end[1])) or \
(not self._start[0] and self._start[1]) and (self._end[0] and not self._end[1]):
raise InvalidArgumentValue('Invalid indexes set. Indexes should be unbounded at same axes.')

# If one axes is unbounded on an index, make other index also unbounded on same axes
if self._start[0] is None or self._end[0] is None:
self._start[0], self._end[0] = None, None
elif self._start[1] is None or self._end[1] is None:
self._start[1], self._end[1] = None, None

# verify
# if (self._start[0] and not self._end[0]) or (not self._start[0] and self._end[0]) or \
#    (self._start[1] and not self._end[1]) or (not self._start[1] and self._end[1]):
#     raise InvalidArgumentValue('Invalid start and end set for this range')

if self._start and self._end:
if self._start[0]:
assert self._start[0] <= self._end[0]
if self._start[1]:
assert self._start[1] <= self._end[1]

self._calculate_label()

def _calculate_label(self):
"""update label from values """
label = self.worksheet_title
label = '' if label is None else label
if self.start and self.end:
label += "!" + self.start.label + ":" + self.end.label
return label

""" update values from label """
if len(label.split('!')) > 1:
self.worksheet_title = label.split('!')[0]
rem = label.split('!')[1]
if ":" in rem:
else:
else:
pass
self._apply_index_constraints()

[docs]    def to_json(self):
""" Get json representation of this grid range. """
if self.worksheet_id is None:
raise Exception("worksheet id not set for this range.")
return_dict = {"sheetId": self.worksheet_id}
if self.start[0]:
return_dict["startRowIndex"] = self.start[0] - 1
if self.start[1]:
return_dict["startColumnIndex"] = self.start[1] - 1
if self.end[0]:
return_dict["endRowIndex"] = self.end[0]
if self.end[1]:
return_dict["endColumnIndex"] = self.end[1]
return return_dict

[docs]    def set_json(self, namedjson):
"""
Apply a Gridrange json to this named range.

:param namedjson: json object of the GridRange format

"""
if 'sheetId' in namedjson:
self.worksheet_id = namedjson['sheetId']
start_row_idx = namedjson.get('startRowIndex', None)
end_row_idx = namedjson.get('endRowIndex', None)
start_col_idx = namedjson.get('startColumnIndex', None)
end_col_idx = namedjson.get('endColumnIndex', None)

start_row_idx = start_row_idx + 1 if start_row_idx is not None else start_row_idx
start_col_idx = start_col_idx + 1 if start_col_idx is not None else start_col_idx

self._calculate_label()

[docs]    def get_bounded_indexes(self):
""" get bounded indexes of this range based on worksheet size, if the indexes are unbounded """
start_r, start_c = tuple(iter(self.start)) if self.start else (None, None)
end_r, end_c = tuple(iter(self.end)) if self.end else (None, None)
start_r = start_r if start_r else 1
start_c = start_c if start_c else 1
if not self._worksheet and not (end_r or end_c):
raise InvalidArgumentValue('Worksheet not set for calculating size.')
end_r = end_r if end_r else self._worksheet.rows
end_c = end_c if end_c else self._worksheet.cols

@property
def height(self):
""" Height of this gridrange """
start, end = self.get_bounded_indexes()
return end[0] - start[0] + 1

@property
def width(self):
""" Width of this gridrange """
start, end = self.get_bounded_indexes()
return end[1] - start[1] + 1

def __repr__(self):
return '<%s %s>' % (self.__class__.__name__, str(self.label))

def __eq__(self, other):
if isinstance(other, GridRange):
return self.label == other.label
elif type(other) is str:
return self.label == other
else:
return super(GridRange, self).__eq__(other)

def __ne__(self, other):
return not self.__eq__(other)
```