コード例 #1
0
ファイル: chart.py プロジェクト: TriceB/Unlock-Academy-ZTM
 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)
コード例 #2
0
ファイル: datarange.py プロジェクト: lpouvreau/pygsheets
    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
コード例 #3
0
    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)
コード例 #4
0
ファイル: cell.py プロジェクト: nithinmurali/pygsheets
    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)
コード例 #5
0
ファイル: chart.py プロジェクト: nithinmurali/pygsheets
 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
コード例 #6
0
ファイル: chart.py プロジェクト: TriceB/Unlock-Academy-ZTM
 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
コード例 #7
0
ファイル: chart.py プロジェクト: nithinmurali/pygsheets
    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
コード例 #8
0
ファイル: chart.py プロジェクト: TriceB/Unlock-Academy-ZTM
    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
コード例 #9
0
    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
コード例 #10
0
ファイル: cell.py プロジェクト: nithinmurali/pygsheets
 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')
コード例 #11
0
ファイル: cell.py プロジェクト: nithinmurali/pygsheets
 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')
コード例 #12
0
 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')
コード例 #13
0
 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')
コード例 #14
0
 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')
コード例 #15
0
ファイル: cell.py プロジェクト: nithinmurali/pygsheets
 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')
コード例 #16
0
ファイル: sheet.py プロジェクト: nithinmurali/pygsheets
    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)
コード例 #17
0
ファイル: sheet.py プロジェクト: seantibor/uorganisms
    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)
コード例 #18
0
ファイル: chart.py プロジェクト: TriceB/Unlock-Academy-ZTM
 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
コード例 #19
0
ファイル: chart.py プロジェクト: nithinmurali/pygsheets
 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
コード例 #20
0
ファイル: chart.py プロジェクト: nithinmurali/pygsheets
 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)
コード例 #21
0
    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)
コード例 #22
0
ファイル: chart.py プロジェクト: nithinmurali/pygsheets
 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}
コード例 #23
0
ファイル: chart.py プロジェクト: TriceB/Unlock-Academy-ZTM
 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
             }
コード例 #24
0
 def start(self, value):
     value = utils.format_addr(value, 'tuple')
     self._start = value
     self._update_label()
コード例 #25
0
 def end(self, value):
     value = utils.format_addr(value, 'tuple')
     self._end = value
     self._update_label()
コード例 #26
0
 def range(self):
     """Range in format A1:C5"""
     return format_addr(self._start_addr) + ':' + format_addr(
         self._end_addr)
コード例 #27
0
 def end_addr(self, addr):
     self._end_addr = format_addr(addr, 'tuple')
     self.update_named_range()
コード例 #28
0
ファイル: datarange.py プロジェクト: lpouvreau/pygsheets
 def start_addr(self, addr):
     self._start_addr = format_addr(addr, 'tuple')
     if self._linked:
         self.update_named_range()