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 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 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 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()