-2

Here is a simplified example of my codes and the screenshot of the results I want to get in google spreadsheet. I hope to either save the dataframe style to google spreadsheet as applying table style to excel using python. Or use the gspread-formatting to high-light the cell background when the value above the threshold.

Could anyone give me an example how to do this? Thank You!

cars = {'Brand': ['Honda Civic','Ferrari','Toyota Corolla','Ford Focus','Audi A4','TESLA','Ford Fusion','BENZ'],
        'Price': [22000,625000,25000,27000,35000,55000,28000,51000]}
df_car = pd.DataFrame(cars, columns = ['Brand', 'Price'])

def _color_if_above_budget(s):
    return ['background-color: red' if val >50000 else '' for val in s]
s=df_car.style.apply(_color_if_above_budget, subset=['Price'])
ws=**worksheet
        gd.set_with_dataframe(worksheet=ws,dataframe=df_car,include_index=False,include_column_header=True,resize=True)

enter image description here

Tanaike
  • 181,128
  • 11
  • 97
  • 165
user7264299
  • 9
  • 3

1 Answers1

1

Use gspread_dataframe to set data to sheets and gspread_formatting to format the content of sheets with condition.

Try this code below:

import gspread
import pandas as pd
from gspread_dataframe import set_with_dataframe
from gspread_formatting import *

gc = gspread.service_account()
sh = gc.open("example").sheet1
cars = {'Brand': ['Honda Civic','Ferrari','Toyota Corolla','Ford Focus','Audi A4','TESLA','Ford Fusion','BENZ'],
        'Price': [22000,625000,25000,27000,35000,55000,28000,51000]}
df_car = pd.DataFrame(cars, columns = ['Brand', 'Price'])
set_with_dataframe(sh, df_car)

rule = ConditionalFormatRule(
    ranges=[GridRange.from_a1_range('B2:B', sh)],
    booleanRule=BooleanRule(
        condition=BooleanCondition('NUMBER_GREATER', ['50000']),
        format=CellFormat(textFormat=textFormat(bold=True), backgroundColor=Color(1,0,0))
    )
)
rules = get_conditional_format_rules(sh)
rules.append(rule)
rules.save()

Output:

example image

References:

gspread-formatting (Conditional Formatting)

gspread-dataframe

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
  • this is very helpful! thanks. I have a question on this 'GridRange.from_a1_range('B2:B', sh)]'. is there a way to use column name rather than letter 'B'? Because I need to apply this conditional formatting to many columns, it's difficult to tell the letters ('B' or 'AZ' or 'CZ') for each column. If not, is it possible to detect what letters are corresponding to every column name? thanks – user7264299 Dec 22 '20 at 03:32
  • Unfortunately, [ConditionalFormatRule](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#ConditionalFormatRule) only accepts [GridRange](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#GridRange) as value for `ranges` – Nikko J. Dec 22 '20 at 15:36