def __init__(self,
worksheet,
domain=None,
ranges=None,
chart_type=None,
title='',
anchor_cell=None,
json_obj=None):
self._title = title
self._chart_type = chart_type
self._domain = ()
if domain:
self._domain = (format_addr(domain[0], 'tuple'),
format_addr(domain[1], 'tuple'))
self._ranges = []
if ranges:
for i in range(len(ranges)):
self._ranges.append(
(format_addr(ranges[i][0],
'tuple'), format_addr(ranges[i][1], 'tuple')))
self._worksheet = worksheet
self._title_font_family = 'Roboto'
self._font_name = 'Roboto'
self._legend_position = 'RIGHT_LEGEND'
self._chart_id = None
self._anchor_cell = anchor_cell
if json_obj is None:
self._create_chart()
else:
self.set_json(json_obj)
def __init__(self, start=None, end=None, worksheet=None, name='', data=None, name_id=None, namedjson=None, protect_id=None, protectedjson=None):
self._worksheet = worksheet
if namedjson:
start = (namedjson['range'].get('startRowIndex', 0)+1, namedjson['range'].get('startColumnIndex', 0)+1)
# @TODO this won't scale if the sheet size is changed
end = (namedjson['range'].get('endRowIndex', self._worksheet.cols),
namedjson['range'].get('endColumnIndex', self._worksheet.rows))
name_id = namedjson['namedRangeId']
if protectedjson:
start = (protectedjson['range'].get('startRowIndex', 0)+1, protectedjson['range'].get('startColumnIndex', 0)+1)
# @TODO this won't scale if the sheet size is changed
end = (protectedjson['range'].get('endRowIndex', self._worksheet.cols),
protectedjson['range'].get('endColumnIndex', self._worksheet.rows))
protect_id = protectedjson['protectedRangeId']
self._start_addr = format_addr(start, 'tuple')
self._end_addr = format_addr(end, 'tuple')
if data:
if len(data) == self._end_addr[0] - self._start_addr[0] + 1 and \
len(data[0]) == self._end_addr[1] - self._start_addr[1] + 1:
self._data = data
else:
self.fetch()
else:
self.fetch()
self._linked = True
self._name_id = name_id
self._protect_id = protect_id
self._name = name
self.protected_properties = ProtectedRange()
self._banned = False
def __init__(self, pos, val='', worksheet=None, cell_data=None):
self._worksheet = worksheet
if type(pos) == str:
pos = format_addr(pos, 'tuple')
self._row, self._col = pos
self._label = format_addr(pos, 'label')
self._value = val # formatted value
self._unformated_value = val # un-formatted value
self._formula = ''
self._note = None
if self._worksheet is None:
self._linked = False
else:
self._linked = True
self._color = (None, None, None, None)
self._simplecell = True # if format, notes etc wont be fetched on each update
self.format = (None, None) # number format
self.text_format = None # the text format as json
self.text_rotation = None # the text rotation as json
self._horizontal_alignment = None
self._vertical_alignment = None
self.borders = None
"""Border Properties as dictionary.
Reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#borders."""
self.parse_value = True
"""Determines how values are interpreted by Google Sheets (True: USER_ENTERED; False: RAW).
Reference: https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption"""
self._wrap_strategy = None
if cell_data is not None:
self.set_json(cell_data)
def __init__(self, pos, val='', worksheet=None, cell_data=None):
self._worksheet = worksheet
if type(pos) == str:
pos = format_addr(pos, 'tuple')
self._row, self._col = pos
self._label = format_addr(pos, 'label')
self._value = val # formatted value
self._unformated_value = val # un-formatted value
self._formula = ''
self._note = None
if self._worksheet is None:
self._linked = False
else:
self._linked = True
self._parent = None
self._color = (None, None, None, None)
self._simplecell = True # if format, notes etc wont be fetched on each update
self.format = (None, None) # number format
self.text_format = {} # the text format as json
self.text_rotation = None # the text rotation as json
self._horizontal_alignment = None
self._vertical_alignment = None
self.borders = None
"""Border Properties as dictionary.
Reference: `api object <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#borders>`__."""
self.parse_value = True
"""Determines how values are interpreted by Google Sheets (True: USER_ENTERED; False: RAW).
Reference: `sheets api <https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption>`__"""
self._wrap_strategy = None
self.is_dirty = True
if cell_data is not None:
self.set_json(cell_data)
def domain(self, new_domain):
new_domain = (format_addr(new_domain[0], 'tuple'), format_addr(new_domain[1], 'tuple'))
temp = self._domain
self._domain = new_domain
try:
self.update_chart()
except:
self._domain = temp
def domain(self, new_domain):
new_domain = (format_addr(new_domain[0], 'tuple'),
format_addr(new_domain[1], 'tuple'))
temp = self._domain
self._domain = new_domain
try:
self.update_chart()
except:
self._domain = temp
def ranges(self, new_ranges):
if type(new_ranges) is tuple:
new_ranges = [new_ranges]
for i in range(len(new_ranges)):
new_ranges[i] = (format_addr(new_ranges[i][0], 'tuple'), format_addr(new_ranges[i][1], 'tuple'))
temp = self._ranges
self._ranges = new_ranges
try:
self.update_chart()
except:
self._ranges = temp
def ranges(self, new_ranges):
if type(new_ranges) is tuple:
new_ranges = [new_ranges]
for i in range(len(new_ranges)):
new_ranges[i] = (format_addr(new_ranges[i][0], 'tuple'),
format_addr(new_ranges[i][1], 'tuple'))
temp = self._ranges
self._ranges = new_ranges
try:
self.update_chart()
except:
self._ranges = temp
def __init__(self,
start=None,
end=None,
worksheet=None,
name='',
data=None,
name_id=None,
namedjson=None,
protectedjson=None):
self._worksheet = worksheet
self.logger = logging.getLogger(__name__)
self._protected_properties = ProtectedRangeProperties()
if namedjson:
start = (namedjson['range'].get('startRowIndex', 0) + 1,
namedjson['range'].get('startColumnIndex', 0) + 1)
# @TODO this won't scale if the sheet size is changed
end = (namedjson['range'].get('endRowIndex', self._worksheet.cols),
namedjson['range'].get('endColumnIndex',
self._worksheet.rows))
name_id = namedjson['namedRangeId']
if protectedjson:
# TODO dosent consider backing named range
start = (protectedjson['range'].get('startRowIndex', 0) + 1,
protectedjson['range'].get('startColumnIndex', 0) + 1)
# @TODO this won't scale if the sheet size is changed
end = (protectedjson['range'].get('endRowIndex',
self._worksheet.cols),
protectedjson['range'].get('endColumnIndex',
self._worksheet.rows))
name_id = protectedjson.get('namedRangeId',
'') # @TODO get the name also
self._protected_properties = ProtectedRangeProperties(
protectedjson)
self._start_addr = format_addr(start, 'tuple')
self._end_addr = format_addr(end, 'tuple')
if data:
if len(data) == self._end_addr[0] - self._start_addr[0] + 1 and \
len(data[0]) == self._end_addr[1] - self._start_addr[1] + 1:
self._data = data
else:
self.fetch()
else:
self.fetch()
self._linked = True
self._name_id = name_id
self._name = name
def label(self, label):
if self._linked:
ncell = self._worksheet.cell(label)
self.__dict__.update(ncell.__dict__)
else:
self._label = label
self._row, self._col = format_addr(label, 'tuple')
def col(self, col):
if self._linked:
ncell = self._worksheet.cell((self._row, col))
self.__dict__.update(ncell.__dict__)
else:
self._col = col
self._label = format_addr((self._row, self._col), 'label')
def label(self, label):
if self._linked:
ncell = self._worksheet.cell(label)
self.__dict__.update(ncell.__dict__)
else:
self._label = label
self._row, self._col = format_addr(label, 'tuple')
def row(self, row):
if self._linked:
ncell = self._worksheet.cell((row, self.col))
self.__dict__.update(ncell.__dict__)
else:
self._row = row
self._label = format_addr((self._row, self._col), 'label')
def col(self, col):
if self._linked:
ncell = self._worksheet.cell((self._row, col))
self.__dict__.update(ncell.__dict__)
else:
self._col = col
self._label = format_addr((self._row, self._col), 'label')
def row(self, row):
if self._linked:
ncell = self._worksheet.cell((row, self.col))
self.__dict__.update(ncell.__dict__)
else:
self._row = row
self._label = format_addr((self._row, self._col), 'label')
def values_batch_update(self, spreadsheet_id, body, parse=True):
"""
Impliments batch update
:param spreadsheet_id: id of spreadsheet
:param body: body of request
:param parse:
"""
cformat = 'USER_ENTERED' if parse else 'RAW'
batch_limit = GOOGLE_SHEET_CELL_UPDATES_LIMIT
lengths = [len(x) for x in body['values']]
avg_row_length = (min(lengths) + max(lengths))/2
avg_row_length = 1 if avg_row_length == 0 else avg_row_length
if body['majorDimension'] == 'ROWS':
batch_length = int(batch_limit / avg_row_length) # num of rows to include in a batch
num_rows = len(body['values'])
else:
batch_length = int(batch_limit / len(body['values'])) # num of rows to include in a batch
num_rows = len(body['values'][0])
if len(body['values']) * len(body['values'][0]) <= batch_limit:
request = self.service.spreadsheets().values().update(spreadsheetId=spreadsheet_id,
range=body['range'],
valueInputOption=cformat, body=body)
self._execute_requests(request)
else:
if batch_length == 0:
raise AssertionError("num_columns < " + str(GOOGLE_SHEET_CELL_UPDATES_LIMIT))
values = body['values']
title, value_range = body['range'].split('!')
value_range_start, value_range_end = value_range.split(':')
value_range_end = list(format_addr(str(value_range_end), output='tuple'))
value_range_start = list(format_addr(str(value_range_start), output='tuple'))
max_rows = value_range_end[0]
start_row = value_range_start[0]
for batch_start in range(0, num_rows, batch_length):
if body['majorDimension'] == 'ROWS':
body['values'] = values[batch_start:batch_start + batch_length]
else:
body['values'] = [col[batch_start:batch_start + batch_length] for col in values]
value_range_start[0] = batch_start + start_row
value_range_end[0] = min(batch_start + batch_length, max_rows) + start_row
body['range'] = title + '!' + format_addr(tuple(value_range_start), output='label') + ':' + \
format_addr(tuple(value_range_end), output='label')
request = self.service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, body=body,
range=body['range'],
valueInputOption=cformat)
self._execute_requests(request)
def values_batch_update(self, spreadsheet_id, body, parse=True):
"""
Impliments batch update
:param spreadsheet_id: id of spreadsheet
:param body: body of request
:param parse:
"""
cformat = 'USER_ENTERED' if parse else 'RAW'
batch_limit = GOOGLE_SHEET_CELL_UPDATES_LIMIT
lengths = [len(x) for x in body['values']]
avg_row_length = (min(lengths) + max(lengths))/2
avg_row_length = 1 if avg_row_length == 0 else avg_row_length
if body['majorDimension'] == 'ROWS':
batch_length = int(batch_limit / avg_row_length) # num of rows to include in a batch
num_rows = len(body['values'])
else:
batch_length = int(batch_limit / len(body['values'])) # num of rows to include in a batch
num_rows = len(body['values'][0])
if len(body['values']) * len(body['values'][0]) <= batch_limit:
request = self.service.spreadsheets().values().update(spreadsheetId=spreadsheet_id,
range=body['range'],
valueInputOption=cformat, body=body)
self._execute_requests(request)
else:
if batch_length == 0:
raise AssertionError("num_columns < " + str(GOOGLE_SHEET_CELL_UPDATES_LIMIT))
values = body['values']
title, value_range = body['range'].split('!')
value_range_start, value_range_end = value_range.split(':')
value_range_end = list(format_addr(str(value_range_end), output='tuple'))
value_range_start = list(format_addr(str(value_range_start), output='tuple'))
max_rows = value_range_end[0]
start_row = value_range_start[0]
for batch_start in range(0, num_rows, batch_length):
if body['majorDimension'] == 'ROWS':
body['values'] = values[batch_start:batch_start + batch_length]
else:
body['values'] = [col[batch_start:batch_start + batch_length] for col in values]
value_range_start[0] = batch_start + start_row
value_range_end[0] = min(batch_start + batch_length, max_rows) + start_row
body['range'] = title + '!' + format_addr(tuple(value_range_start), output='label') + ':' + \
format_addr(tuple(value_range_end), output='label')
request = self.service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, body=body,
range=body['range'],
valueInputOption=cformat)
self._execute_requests(request)
def anchor_cell(self, new_anchor_cell):
temp = self._anchor_cell
try:
if type(new_anchor_cell) is Cell:
self._anchor_cell = (new_anchor_cell.row, new_anchor_cell.col)
self._update_position()
else:
self._anchor_cell = format_addr(new_anchor_cell, 'tuple')
self._update_position()
except:
self._anchor_cell = temp
def anchor_cell(self, new_anchor_cell):
temp = self._anchor_cell
try:
if type(new_anchor_cell) is Cell:
self._anchor_cell = (new_anchor_cell.row, new_anchor_cell.col)
self._update_position()
else:
self._anchor_cell = format_addr(new_anchor_cell, 'tuple')
self._update_position()
except:
self._anchor_cell = temp
def __init__(self, worksheet, domain=None, ranges=None, chart_type=None, title='', anchor_cell=None, json_obj=None):
self._title = title
self._chart_type = chart_type
self._domain = ()
if domain:
self._domain = (format_addr(domain[0], 'tuple'), format_addr(domain[1], 'tuple'))
self._ranges = []
if ranges:
for i in range(len(ranges)):
self._ranges.append((format_addr(ranges[i][0], 'tuple'), format_addr(ranges[i][1], 'tuple')))
self._worksheet = worksheet
self._title_font_family = 'Roboto'
self._font_name = 'Roboto'
self._legend_position = 'RIGHT_LEGEND'
self._chart_id = None
self._anchor_cell = anchor_cell
if json_obj is None:
self._create_chart()
else:
self.set_json(json_obj)
def sort(self, basecolumnindex=0, sortorder="ASCENDING"):
"""sort the values in the datarange
:param basecolumnindex: Index of the base column in which sorting is to be done (Integer).
The index here is the index of the column in range (first columen is 0).
:param sortorder: either "ASCENDING" or "DESCENDING" (String)
"""
self._worksheet.sort_range(self._start_addr,
self._end_addr,
basecolumnindex=basecolumnindex +
format_addr(self._start_addr, 'tuple')[1] -
1,
sortorder=sortorder)
def _get_anchor_cell(self):
if self._anchor_cell is None:
return {
"columnIndex": self._domain[1][1]-1,
"rowIndex": self._domain[1][0], "sheetId": self._worksheet.id}
else:
if type(self._anchor_cell) is Cell:
return {
"columnIndex": self._anchor_cell.col-1,
"rowIndex": self._anchor_cell.row-1, "sheetId": self._worksheet.id}
else:
cell = format_addr(self._anchor_cell, 'tuple')
return {
"columnIndex": cell[1]-1,
"rowIndex": cell[0]-1, "sheetId": self._worksheet.id}
def _get_anchor_cell(self):
if self._anchor_cell is None:
return {
"columnIndex": self._domain[1][1] - 1,
"rowIndex": self._domain[1][0],
"sheetId": self._worksheet.id
}
else:
if type(self._anchor_cell) is Cell:
return {
"columnIndex": self._anchor_cell.col - 1,
"rowIndex": self._anchor_cell.row - 1,
"sheetId": self._worksheet.id
}
else:
cell = format_addr(self._anchor_cell, 'tuple')
return {
"columnIndex": cell[1] - 1,
"rowIndex": cell[0] - 1,
"sheetId": self._worksheet.id
}
def start(self, value):
value = utils.format_addr(value, 'tuple')
self._start = value
self._update_label()
def end(self, value):
value = utils.format_addr(value, 'tuple')
self._end = value
self._update_label()
def range(self):
"""Range in format A1:C5"""
return format_addr(self._start_addr) + ':' + format_addr(
self._end_addr)
def end_addr(self, addr):
self._end_addr = format_addr(addr, 'tuple')
self.update_named_range()
def start_addr(self, addr):
self._start_addr = format_addr(addr, 'tuple')
if self._linked:
self.update_named_range()