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=**worksheetgd.set_with_dataframe(worksheet=ws,dataframe=df_car,include_index=False,include_column_header=True,resize=True)
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:
References:
gspread-formatting (Conditional Formatting)
gspread-dataframe