How to get the cell's font color using python

user0qZd1 0
Viewed 80

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? The cells are formatted using conditional formatting.

I was advised to use the following codes:

client = gspread.service_account_from_dict(creds)
spreadsheet_id = '1qlfDoQctCjfsO-bJkNbedi4t9P_8Uq242WxiOabZSA4'

rng = "WH Data!H2" # Please set the cell coordinate as A1Notation.
service = build("sheets", "v4", credentials=client.auth)
res = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=[rng], fields="sheets(data(rowData(values(userEnteredFormat,formattedValue,textFormatRuns))),conditionalFormats)").execute()
conditionalFormats = res["sheets"][0].get("conditionalFormats")
print(conditionalFormats)

The result returns a lot of information instead of a single value:

[{'ranges': [{'sheetId': 1781915778, 'startRowIndex': 0, 'endRowIndex': 1004, 'startColumnIndex': 7, 'endColumnIndex': 8}, {'sheetId': 1781915778, 'startRowIndex': 0, 'endRowIndex': 1004, 'startColumnIndex': 12, 'endColumnIndex': 13}, {'sheetId': 1781915778, 'startRowIndex': 1, 'endRowIndex': 1004, 'startColumnIndex': 3, 'endColumnIndex': 4}], 'booleanRule': {'condition': {'type': 'NUMBER_LESS', 'values': [{'userEnteredValue': '0'}]}, 'format': {'textFormat': {'foregroundColor': {'red': 1}, 'foregroundColorStyle': {'rgbColor': {'red': 1}}}}}}, {'ranges': [{'sheetId': 1781915778, 'startRowIndex': 0, 'endRowIndex': 1004, 'startColumnIndex': 7, 'endColumnIndex': 8}, {'sheetId': 1781915778, 'startRowIndex': 0, 'endRowIndex': 1004, 'startColumnIndex': 12, 'endColumnIndex': 13}, {'sheetId': 1781915778, 'startRowIndex': 1, 'endRowIndex': 1004, 'startColumnIndex': 3, 'endColumnIndex': 4}], 'booleanRule': {'condition': {'type': 'NUMBER_GREATER', 'values': [{'userEnteredValue': '0'}]}, 'format': {'textFormat': {'foregroundColor': {'red': 0.043137256, 'green': 0.5019608, 'blue': 0.2627451}, 'foregroundColorStyle': {'rgbColor': {'red': 0.043137256, 'green': 0.5019608, 'blue': 0.2627451}}}}}}]

And the value doesn't change no matter which cell I entered. Looks like it only returns the rule of the conditional format, but not the format result.

gspread google-sheets-api google-sheets python
2 Answers

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.

MattDMo0

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.
rng = "'Sheet1'!B3" # Please set the cell coordinate as A1Notation.
service = build("sheets", "v4", credentials=client.auth)
res = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=[rng], 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.

References:

Added:

From your following reply,

However, my cells are conditional formatting cells (The formatting rule is: Red if negative, green if positive ). I received the output error: 'NoneType' object has no attribute 'foregroundColorStyle'. Do you know how to read conditional formatting cells' font color?

When you want to retrieve the font color by setting it with the conditional formatting rules, unfortunately, in this case, it seems that unfortunately, gspread_formatting cannot be used. Please use "Method: spreadsheets.get". The sample script is as follows. fields is modified from the above script.

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.
rng = "'Sheet1'!B3" # Please set the cell coordinate as A1Notation.
service = build("sheets", "v4", credentials=client.auth)
res = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=[rng], fields="sheets(data(rowData(values(userEnteredFormat,formattedValue,textFormatRuns))),conditionalFormats)").execute()
conditionalFormats = res["sheets"][0].get("conditionalFormats")
if conditionalFormats:
    print(conditionalFormats)
else:
    print("# range doesn't include cells with conditionalFormats.")
Tanaike0
Related