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