1from pygsheets.utils import format_addr 2from pygsheets.cell import Cell 3from pygsheets.custom_types import ChartType 4from pygsheets.exceptions import InvalidArgumentValue 5 6 7class Chart(object): 8 """ 9 Represents a chart in a sheet. 10 11 :param worksheet: Worksheet object in which the chart resides 12 :param domain: Cell range of the desired chart domain in the form of tuple of tuples 13 :param ranges: Cell ranges of the desired ranges in the form of list of tuple of tuples 14 :param chart_type: An instance of :class:`ChartType` Enum. 15 :param title: Title of the chart 16 :param anchor_cell: Position of the left corner of the chart in the form of cell address or cell object 17 :param json_obj: Represents a json structure of the chart as given in `api <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#BasicChartSpec>`__. 18 """ 19 def __init__(self, worksheet, domain=None, ranges=None, chart_type=None, title='', anchor_cell=None, json_obj=None): 20 self._title = title 21 self._chart_type = chart_type 22 self._domain = () 23 if domain: 24 self._domain = (format_addr(domain[0], 'tuple'), format_addr(domain[1], 'tuple')) 25 self._ranges = [] 26 if ranges: 27 for i in range(len(ranges)): 28 self._ranges.append((format_addr(ranges[i][0], 'tuple'), format_addr(ranges[i][1], 'tuple'))) 29 self._worksheet = worksheet 30 self._title_font_family = 'Roboto' 31 self._font_name = 'Roboto' 32 self._legend_position = 'RIGHT_LEGEND' 33 self._chart_id = None 34 self._anchor_cell = anchor_cell 35 if json_obj is None: 36 self._create_chart() 37 else: 38 self.set_json(json_obj) 39 40 @property 41 def title(self): 42 """Title of the chart""" 43 return self._title 44 45 @title.setter 46 def title(self, new_title): 47 temp = self._title 48 self._title = new_title 49 try: 50 self.update_chart() 51 except: 52 self._title = temp 53 54 @property 55 def domain(self): 56 """ 57 Domain of the chart. 58 The domain takes the cell range in the form of tuple of cell adresses. Where first adress is the 59 top cell of the column and 2nd element the last adress of the column. 60 61 Example: ((1,1),(6,1)) or ('A1','A6') 62 """ 63 return self._domain 64 65 @domain.setter 66 def domain(self, new_domain): 67 new_domain = (format_addr(new_domain[0], 'tuple'), format_addr(new_domain[1], 'tuple')) 68 temp = self._domain 69 self._domain = new_domain 70 try: 71 self.update_chart() 72 except: 73 self._domain = temp 74 75 @property 76 def chart_type(self): 77 """Type of the chart 78 The specificed as enum of type :class:'ChartType' 79 80 The available chart types are given in the `api docs <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#BasicChartType>`__ . 81 """ 82 return self._chart_type 83 84 @chart_type.setter 85 def chart_type(self, new_chart_type): 86 if not isinstance(new_chart_type, ChartType): 87 raise InvalidArgumentValue 88 temp = self._chart_type 89 self._chart_type = new_chart_type 90 try: 91 self.update_chart() 92 except: 93 self._chart_type = temp 94 95 @property 96 def ranges(self): 97 """ 98 Ranges of the chart (y values) 99 A chart can have multiple columns as range. So you can provide them as a list. The ranges are 100 taken in the form of list of tuple of cell adresses. where each tuple inside the list represents 101 a column as staring and ending cell. 102 103 Example: 104 [((1,2),(6,2)), ((1,3),(6,3))] or [('B1','B6'), ('C1','C6')] 105 """ 106 return self._ranges 107 108 @ranges.setter 109 def ranges(self, new_ranges): 110 if type(new_ranges) is tuple: 111 new_ranges = [new_ranges] 112 113 for i in range(len(new_ranges)): 114 new_ranges[i] = (format_addr(new_ranges[i][0], 'tuple'), format_addr(new_ranges[i][1], 'tuple')) 115 116 temp = self._ranges 117 self._ranges = new_ranges 118 try: 119 self.update_chart() 120 except: 121 self._ranges = temp 122 123 @property 124 def title_font_family(self): 125 """ 126 Font family of the title. (Default: 'Roboto') 127 """ 128 return self._title_font_family 129 130 @title_font_family.setter 131 def title_font_family(self, new_title_font_family): 132 temp = self._title_font_family 133 self._title_font_family = new_title_font_family 134 try: 135 self.update_chart() 136 except: 137 self._title_font_family = temp 138 139 @property 140 def font_name(self): 141 """ 142 Font name for the chart. (Default: 'Roboto') 143 """ 144 return self._font_name 145 146 @font_name.setter 147 def font_name(self, new_font_name): 148 temp = self._font_name 149 self._font_name = new_font_name 150 try: 151 self.update_chart() 152 except: 153 self._font_name = temp 154 155 @property 156 def legend_position(self): 157 """ 158 Legend postion of the chart. (Default: 'RIGHT_LEGEND') 159 The available options are given in the `api docs <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#BasicChartLegendPosition>`__. 160 """ 161 return self._legend_position 162 163 @legend_position.setter 164 def legend_position(self, new_legend_position): 165 temp = self._legend_position 166 self._legend_position = new_legend_position 167 try: 168 self.update_chart() 169 except: 170 self._legend_position = temp 171 172 @property 173 def id(self): 174 """Id of the this chart.""" 175 return self._chart_id 176 177 @property 178 def anchor_cell(self): 179 """Position of the left corner of the chart in the form of cell address or cell object, 180 Changing this will move the chart. 181 """ 182 return self._anchor_cell 183 184 @anchor_cell.setter 185 def anchor_cell(self, new_anchor_cell): 186 temp = self._anchor_cell 187 try: 188 if type(new_anchor_cell) is Cell: 189 self._anchor_cell = (new_anchor_cell.row, new_anchor_cell.col) 190 self._update_position() 191 else: 192 self._anchor_cell = format_addr(new_anchor_cell, 'tuple') 193 self._update_position() 194 except: 195 self._anchor_cell = temp 196 197 def delete(self): 198 """ 199 Deletes the chart. 200 201 .. warning:: 202 Once the chart is deleted the objects of that chart still exist and should not be used. 203 """ 204 request = { 205 "deleteEmbeddedObject": { 206 "objectId": self._chart_id 207 } 208 } 209 self._worksheet.client.sheet.batch_update(self._worksheet.spreadsheet.id, request) 210 211 def refresh(self): 212 """Refreshes the object to incorporate the changes made in the chart through other objects or Google sheet""" 213 chart_data = self._worksheet.client.sheet.get(self._worksheet.spreadsheet.id, fields='sheets(charts,properties)') 214 sheet_list = chart_data.get('sheets') 215 for sheet in sheet_list: 216 if sheet.get('properties', {}).get('sheetId', None) is self._worksheet.id: 217 chart_list = sheet.get('charts') 218 if chart_list: 219 for chart in chart_list: 220 if chart.get('chartId') == self._chart_id: 221 self.set_json(chart) 222 223 def _get_anchor_cell(self): 224 if self._anchor_cell is None: 225 if self._domain: 226 return { 227 "columnIndex": self._domain[1][1]-1, 228 "rowIndex": self._domain[1][0], "sheetId": self._worksheet.id} 229 else: 230 return {"columnIndex": 0, "rowIndex": 0, "sheetId": self._worksheet.id} 231 232 else: 233 if type(self._anchor_cell) is Cell: 234 return { 235 "columnIndex": self._anchor_cell.col-1, 236 "rowIndex": self._anchor_cell.row-1, "sheetId": self._worksheet.id} 237 else: 238 cell = format_addr(self._anchor_cell, 'tuple') 239 return { 240 "columnIndex": cell[1]-1, 241 "rowIndex": cell[0]-1, "sheetId": self._worksheet.id} 242 243 def _get_ranges_request(self): 244 ranges_request_list = [] 245 for i in range(len(self._ranges)): 246 req = { 247 'series': { 248 'sourceRange': { 249 'sources': [self._worksheet.get_gridrange(self._ranges[i][0], self._ranges[i][1])] 250 } 251 }, 252 } 253 ranges_request_list.append(req) 254 return ranges_request_list 255 256 def _create_chart(self): 257 domains = [] 258 if self._domain: 259 domains.append({ 260 "domain": { 261 "sourceRange": { 262 "sources": [self._worksheet.get_gridrange(self._domain[0], self._domain[1])] 263 } 264 } 265 }) 266 267 request = { 268 "addChart": { 269 "chart": { 270 "spec": { 271 "title": self._title, 272 "basicChart": { 273 "chartType": self._chart_type.value, 274 "domains": domains, 275 "series": self._get_ranges_request() 276 } 277 }, 278 "position": { 279 "overlayPosition": { 280 "anchorCell": self._get_anchor_cell() 281 } 282 } 283 } 284 } 285 } 286 response = self._worksheet.client.sheet.batch_update(self._worksheet.spreadsheet.id, request) 287 chart_data_list = response.get('replies') 288 chart_json = chart_data_list[0].get('addChart',{}).get('chart') 289 self.set_json(chart_json) 290 291 def _update_position(self): 292 request = { 293 "updateEmbeddedObjectPosition": { 294 "objectId": self._chart_id, 295 "newPosition": { 296 "overlayPosition": { 297 "anchorCell": { 298 "sheetId": self._worksheet.id, 299 "rowIndex": self._anchor_cell[0]-1, 300 "columnIndex": self._anchor_cell[1]-1 301 } 302 } 303 }, 304 "fields": "*" 305 }} 306 self._worksheet.client.sheet.batch_update(self._worksheet.spreadsheet.id, request) 307 308 def update_chart(self): 309 """updates the applied changes to the sheet.""" 310 request = { 311 'updateChartSpec':{ 312 'chartId': self._chart_id, "spec": self.get_json()} 313 } 314 self._worksheet.client.sheet.batch_update(self._worksheet.spreadsheet.id, request) 315 316 def get_json(self): 317 """Returns the chart as a dictionary structured like the Google Sheets API v4.""" 318 319 domains = [{'domain': {'sourceRange': {'sources': [ 320 self._worksheet.get_gridrange(self._domain[0], self._domain[1])]}}}] 321 ranges = self._get_ranges_request() 322 spec = dict() 323 spec['title'] = self._title 324 spec['basicChart'] = dict() 325 spec['titleTextFormat'] = dict() 326 spec['basicChart']['chartType'] = self._chart_type.value 327 spec['basicChart']['legendPosition'] = self._legend_position 328 spec['titleTextFormat']['fontFamily'] = self._title_font_family 329 spec['fontName'] = self._font_name 330 spec['basicChart']['domains'] = domains 331 spec['basicChart']['series'] = ranges 332 return spec 333 334 def set_json(self, chart_data): 335 """ 336 Reads a json-dictionary returned by the Google Sheets API v4 and initialize all the properties from it. 337 338 :param chart_data: The chart data as json specified in sheets api. 339 """ 340 anchor_cell_data = chart_data.get('position',{}).get('overlayPosition',{}).get('anchorCell') 341 self._anchor_cell = (anchor_cell_data.get('rowIndex',0)+1, anchor_cell_data.get('columnIndex',0)+1) 342 self._title = chart_data.get('spec',{}).get('title',None) 343 self._chart_id = chart_data.get('chartId',None) 344 self._title_font_family = chart_data.get('spec',{}).get('titleTextFormat',{}).get('fontFamily',None) 345 self._font_name = chart_data.get('spec',{}).get('titleTextFormat',{}).get('fontFamily',None) 346 basic_chart = chart_data.get('spec',{}).get('basicChart', None) 347 self._chart_type = ChartType(basic_chart.get('chartType', None)) 348 self._legend_position = basic_chart.get('legendPosition', None) 349 domain_list = basic_chart.get('domains', []) 350 for d in domain_list: 351 source_list = d.get('domain', {}).get('sourceRange', {}).get('sources', None) 352 for source in source_list: 353 start_row = source.get('startRowIndex',0) 354 end_row = source.get('endRowIndex',0) 355 start_column = source.get('startColumnIndex',0) 356 end_column = source.get('endColumnIndex',0) 357 self._domain = [(start_row+1, start_column+1),(end_row, end_column)] 358 range_list = basic_chart.get('series', []) 359 self._ranges = [] 360 for r in range_list: 361 source_list = r.get('series',{}).get('sourceRange',{}).get('sources',None) 362 for source in source_list: 363 start_row = source.get('startRowIndex',0) 364 end_row = source.get('endRowIndex',0) 365 start_column = source.get('startColumnIndex',0) 366 end_column = source.get('endColumnIndex',0) 367 self._ranges.append([(start_row+1, start_column+1), (end_row, end_column)]) 368 369 def __repr__(self): 370 return '<%s %s %s>' % (self.__class__.__name__, self.chart_type.value, repr(self.title)) 371