pythonpython-3.xgoogle-sheets-apigspreadgspread-formatting

gspread: Retrieve dimensions (width/height) of columns and rows

I am trying to retrieve the dimensions of columns and rows in my spreadsheet via the gspread python library.

I am able to authenticate API access, and I can access/modify cell values. I am also able to resize rows and columns. However, I do not seem to be able to access the current dimensions of a row or column.

If I use get_effective_format(worksheet, label), I can see many properties of a cell such as colour, text formatting, border properties etc, but the response does not containing information about the width or height of the cell.

Can anyone help me figure this out?

Most questions I can find online refer to find the number of rows or column, or to changing the width of a column or height of a row. I am looking for help in simply accessing the current height or width.

Solution

  • In order to retrieve the row height and the column width from the spreadsheet, it is required to use the method of spreadsheets.get in Sheets API using the fields. However, unfortunately, I cannot find this method in gspread. So in this answer, I would like to propose the following flow.

    1. Retrieve the access token from the client of gspread.

      • From your tag, I guessed you might use gspread.

    2. Request the method of spreadsheets.get in Sheets API using googleapis for python.

    3. Retrieve the row height and the column width from the spreadsheet.

    Sample script:

    import gspread
from googleapiclient.discovery import build

client = gspread.oauth(###) # Please use your cliend

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.
cell = "A1" # Please set the cell A1Notation.

r, c = gspread.utils.a1_to_rowcol(cell)
fields = "sheets(data(rowMetadata(pixelSize),columnMetadata(pixelSize)))"
service = build("sheets", "v4", credentials=client.auth)
obj = service.spreadsheets().get(spreadsheetId=spreadsheetId, fields=fields, ranges=[sheetName]).execute()
data = obj["sheets"][0]["data"][0]

rowHeight = data["rowMetadata"][r - 1].get("pixelSize", 21)
colWidth = data["columnMetadata"][c - 1].get("pixelSize", 100)

print(f"Row height ({cell} of {sheetName}) is {rowHeight} pixels.")
print(f"Column width ({cell} of {sheetName}) is {colWidth} pixels.")

    Reference: