How to get the cell's font color using python-CodePudding

I'm using Googlesheet API with Python to get the cell data. I'm using the following code to get the value of a cell in my google sheet:

client = gspread.service_account_from_dict(creds)
workbook = client.open('HR Data')
sheet = workbook.get_worksheet(0)
Cell1 = sheet.acell('B3').value

How can I get the font color of that cell? There is no option like sheet.acell('B3').font, sheet.acell('B3').format or sheet.acell('B3').color

I tried the following as suggested:

gspread_formatting.CellFormat(sheet.acell('H4')) Gives me the output: <CellFormat numberFormat=<Cell R4C8 ' 0.86%'>>

gspread_formatting.CellFormat(sheet.acell('H4')).textFormat has no output. gspread_formatting.CellFormat(sheet.acell('H4')).textFormat.foregroundColor gives me the error: AttributeError: 'NoneType' object has no attribute 'foregroundColor'

CodePudding user response：

According to the API docs, a cell's formatting information is stored in the CellFormat (or possibly cellFormat) object. From that, you can retrieve textFormat.foregroundColor.

Aside from that, there is also the gspread-formatting module. Info on receiving, comparing, and composing cellFormat objects is here.

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the text format in a cell.
You want to achieve this using python.

In this case, how about the following patterns?

Pattern 1:

In this pattern, gspread_formatting is used.

import gspread
from gspread_formatting import *

client = gspread.oauth(credentials_filename="###", authorized_user_filename="###") # Please use your script here.

spreadsheet = client.open('HR Data')
sheet = spreadsheet.get_worksheet(0)
res = get_user_entered_format(sheet, "B3")
fontColor = res.textFormat.foregroundColorStyle
print(fontColor)

In the current stage, it seems that in this case, when the text style of a part of the text in a cell is changed, this cannot be retrieved.

Pattern 2:

In this pattern, googleapis for python is used.

import gspread
from googleapiclient.discovery import build

client = gspread.oauth(credentials_filename="###", authorized_user_filename="###") # Please use your script here.

spreadsheet_id = "###" # Please set your Spreadsheet ID.
range = "'Sheet1'!B3" # Please set the cell coordinate as A1Notation.
service = build("sheets", "v4", credentials=client.auth)
res = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=[range], fields="sheets(data(rowData(values(userEnteredFormat,formattedValue,textFormatRuns))))").execute()
print(res)

In the current stage, when the text style of a part of the text in a cell is changed, that is included in textFormatRuns.

Pattern 1:

Pattern 2:

References: