Search code examples
pythongoogle-sheets-apigspread

Gradient markup with Gspread Formatting

I am using Gspread Formatting to set background color to cells in Google Sheets. Now I want to use the GradientRule to accomplish this:

Conditional formatting with GradientRule

I know I have to set a "minpoint" and a "maxpoint", but I am not sure how to do this.

This is what I got so far:

def color():
spr = client.open("Kleurtjes")
sheet = spr.worksheet("Tab3")

rule = ConditionalFormatRule(

    ranges=[GridRange.from_a1_range('A1:A10', sheet)],

    GradientRule=GradientRule(
        minpoint(format=CellFormat(backgroundColor=Color(255,255,255)), type='number'),
        maxpoint(format=CellFormat(backgroundColor=Color(0,128,0)), type='number')
    )
    )

rules = get_conditional_format_rules(sheet)
rules.append(rule)
rules.save()

Can you help me out?

Many thanks in advance!

Solution

  • I believe your goal as follows.

    • You want to achieve the following situation. (The image is from your question.)

    Modification points:

    • You can see the script of GradientRule() and InterpolationPoint() at here and here, respectively.
    • At ConditionalFormatRule(), it seems that the AddConditionalFormatRuleRequest of the batchUpdate method in Sheets API is used. In this case, the color is required to be set from 0 to 1. Please be careful this.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    Please modify your script as follows.

    From: 
    rule = ConditionalFormatRule(

    ranges=[GridRange.from_a1_range('A1:A10', sheet)],

    GradientRule=GradientRule(
        minpoint(format=CellFormat(backgroundColor=Color(255,255,255)), type='number'),
        maxpoint(format=CellFormat(backgroundColor=Color(0,128,0)), type='number')
    )
    )
    To: 
    rule = ConditionalFormatRule(
    ranges=[GridRange.from_a1_range('A1:A10', sheet)],
    gradientRule=GradientRule(
        maxpoint=InterpolationPoint(color=Color(1, 1, 1), type='MAX'),
        minpoint=InterpolationPoint(color=Color(0, 128 / 255, 0), type='MIN')
    )
)

    Note:

    • In this modification, it supposes that you have already been able to get and put values for Google Spreadsheet by using Sheets API with gspread and gspread_formatting. Please be careful this.
    • When you want to achieve the colors of above your image, for example, how about minpoint=InterpolationPoint(color=Color(0.34117648, 0.73333335, 0.5411765), type='MIN')?

    References: