0

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.

Mary
  • 231
  • 1
  • 3
  • 12
  • Does this answer your question? [Python - GSPREAD - Copy text and format from one google sheet to another one](https://stackoverflow.com/questions/67042868/python-gspread-copy-text-and-format-from-one-google-sheet-to-another-one) – Giselle Valladares Sep 15 '22 at 17:26

2 Answers2

2

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.")
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • You answer is correct! It works for normal cases. 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? – Mary Sep 16 '22 at 13:01
  • @Mary Thank you for replying. From your reply, I added one more sample script. Could you please confirm it? – Tanaike Sep 17 '22 at 01:01
  • @Tanaike Don't use `range` as a variable name. It shadows the built-in `range()` function, which could lead to unexpected problems down the road. – MattDMo Sep 17 '22 at 14:39
  • @MattDMo Thank you for your comment. About `Don't use range as a variable name. It shadows the built-in range() function, which could lead to unexpected problems down the road.`, yes. I have forgot it. Now, I could modify it. – Tanaike Sep 17 '22 at 23:32
  • @tanaike The output is a lot of information rather than a single value. I updated it in the question. Could you please help? – Mary Sep 19 '22 at 19:57
  • @Mary Thank you for replying. First, I deeply apologize that my added script was not useful. I think that this is due to my very poor skill. I deeply apologize for this again. About your updated question, your added values are only ranges. It seems that in this case, the conditional format rule is not included. So, it is required to know your sample Spreadsheet to correctly replicate your current situation. So, can you provide your sample Spreadsheet for correctly replicating your current situation? By this, I would like to confirm it. And also, can you provide your expected values? – Tanaike Sep 20 '22 at 00:42
1

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.

MattDMo
  • 100,794
  • 21
  • 241
  • 231
  • Thank you. I tried them as you suggested but still cannot get the font color. I updated the info in my question. Could you please kindly advice? – Mary Sep 15 '22 at 17:39