How do I rotate text in a Google Sheet using pygsheets?

Question

I'm trying to rotate the text of some long header names in a Google Sheet using pygsheets in python. The code below shows a slight modification of the examples provided in pygsheets which according to the docs should work. The text is written to the correct cells, cell A1 is bold, but cells B1 and C1 are not rotated. No error message is surfaced. Any idea why this isn't working?

import pygsheets

sheet_url = "https://docs.google.com/spreadsheets/d/1t3nYyvkVkf6E36vTRucRQ35XQWbeRC--6U5h1chtikI"
gc = pygsheets.authorize(service_file = "credentials.json")
sh = gc.open_by_url(sheet_url)
wks = sh.worksheet("title", "Sheet1")

header = wks.cell('A1')
header.value = 'Bold Text'
header.set_text_format('bold', True)
header.update()

header2 = wks.cell('B1')
header2.value = '45 Degree rotated text'
header2.set_text_rotation('angle', 45)
header2.update()

header3 = wks.cell('C1')
header3.value = 'Vertical Text'
header3.set_text_rotation('vertical', True)
header3.update()

Result: resulting spreadsheet

Tanaike · Accepted Answer · 2019-06-18T06:53:36.787

You want to use "textRotation" of Sheets API with pyghseets.

If my understanding is correct, how about this answer? In my environment, I could also confirm that the same issue with you occurred. So when I saw the script of "pygsheets", I noticed that the modification point of the script.

Although the parameters for textRotation are set, unfortunately, textRotation is not included in the request body. By this, textRotation doesn't work.

Modified script:

When you use this modification, please modify get_json() of cell.py in the directory of the installed "pygsheets" as follows. I think that there might be more simple modification. So please think of this as just one of several answers.

def get_json(self):
    """Returns the cell as a dictionary structured like the Google Sheets API v4."""
    try:
        nformat, pattern = self.format
    except TypeError:
        nformat, pattern = self.format, ""

    if self._formula != '':
        value = self._formula
        value_key = 'formulaValue'
    elif is_number(self._value):
        value = self._value
        value_key = 'numberValue'
    elif type(self._value) is bool:
        value = self._value
        value_key = 'boolValue'
    elif type(self._value) is str or type(self._value) is unicode:
        value = self._value
        value_key = 'stringValue'
    else:   # @TODO errorValue key not handled
        value = self._value
        value_key = 'errorValue'

    ret_json = dict()
    ret_json["userEnteredFormat"] = dict()

    if self.format[0] is not None:
        ret_json["userEnteredFormat"]["numberFormat"] = {"type": getattr(nformat, 'value', nformat),
                                                         "pattern": pattern}
    if self._color[0] is not None:
        ret_json["userEnteredFormat"]["backgroundColor"] = {"red": self._color[0], "green": self._color[1],
                                                            "blue": self._color[2], "alpha": self._color[3]}
    if self.text_format is not None:
        ret_json["userEnteredFormat"]["textFormat"] = self.text_format
        fg = ret_json["userEnteredFormat"]["textFormat"].get('foregroundColor', None)
        if fg:
            ret_json["userEnteredFormat"]["textFormat"]['foregroundColor'] = {"red": fg[0], "green": fg[1],
                                                                              "blue": fg[2], "alpha": fg[3]}

    if self.borders is not None:
        ret_json["userEnteredFormat"]["borders"] = self.borders
    if self._horizontal_alignment is not None:
        ret_json["userEnteredFormat"]["horizontalAlignment"] = self._horizontal_alignment.value
    if self._vertical_alignment is not None:
        ret_json["userEnteredFormat"]["verticalAlignment"] = self._vertical_alignment.value
    if self._wrap_strategy is not None:
        ret_json["userEnteredFormat"]["wrapStrategy"] = self._wrap_strategy

    ### Added -- begin
    if self.text_rotation is not None:
        ret_json["userEnteredFormat"]["textRotation"] = self.text_rotation
    ### Added -- end

    if self._note is not None:
        ret_json["note"] = self._note
    ret_json["userEnteredValue"] = {value_key: value}

    return ret_json

Result:

When above modification is reflected, your script obtains the following result.

Note:

This modified script supposes that Sheets API has already been able to be used.
I investigated above modification after I updated pygsheets to "pygsheets-2.0.1".

References:

In my environment, I could confirm that by "textRotation" worked by above modification. But if this didn't work in your environment, I apologize.

Worked great. Thanks for the fix Tanaike! – skullmaracas Jun 21 '19 at 15:14 — skullmaracas, Jun 21 '19 at 15:14

How do I rotate text in a Google Sheet using pygsheets?

1 Answers1

Modified script:

Result:

Note:

References: