Add support for Conditional Formatting

[andrewgross]

Hey,

Thanks for making this library, it has significantly lowered the bar to working with Google Sheets for me. It would be awesome to have support for Conditional Formatting on columns.

https://developers.google.com/sheets/api/samples/conditional-formatting

It looks like it could fit well in to the range style notation, with some kwargs for the formatting options. If I get a chance I will create a PR.

[mathben]

I am interested to this feature too. I think this issue is blocked by https://github.com/burnash/gspread/issues/482

[andrewgross]

Gspread 2.0 should be on APIv4.

[alugowski]

FYI, you can easily make your own calls into the API from within gspread. Given a worksheet reference, i.e. wks = sh.sheet1 or somesuch:

body = {'requests': [
    {'repeatCell': {
        'range': {'startRowIndex': 0, 'endRowIndex': 1},
        'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}
wks.spreadsheet.batch_update(body)

Where body is the request as you see in your sample link. The conditional formatting one is long, so instead the above body makes the first row bold.

You can copy/paste from Google's examples, just remove the sheetId keys as gspread populates those for you.

[big-c-note]

FYI, you can easily make your own calls into the API from within gspread. Given a worksheet reference, i.e. wks = sh.sheet1 or somesuch:

body = {'requests': [
    {'repeatCell': {
        'range': {'startRowIndex': 0, 'endRowIndex': 1},
        'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}
wks.spreadsheet.batch_update(body)

Where body is the request as you see in your sample link. The conditional formatting one is long, so instead the above body makes the first row bold.

You can copy/paste from Google's examples, just remove the sheetId keys as gspread populates those for you.

Thanks! I was getting this error:

No grid with id: 0

Realized that I needed to add:

body = {'requests': [
    {'repeatCell': {
        'range': {'sheetId': sheetId, 'startRowIndex': 0, 'endRowIndex': 1},
        'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}

where sheetId = https://docs.google.com/spreadsheets/d/{spreadsheetId}/edit#gid={sheetId} https://stackoverflow.com/questions/46696168/google-sheets-api-addprotectedrange-error-no-grid-with-id-0

[vikrant-pune]

FYI, you can easily make your own calls into the API from within gspread. Given a worksheet reference, i.e. wks = sh.sheet1 or somesuch:

body = {'requests': [
    {'repeatCell': {
        'range': {'startRowIndex': 0, 'endRowIndex': 1},
        'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}
wks.spreadsheet.batch_update(body)

Where body is the request as you see in your sample link. The conditional formatting one is long, so instead the above body makes the first row bold.

You can copy/paste from Google's examples, just remove the sheetId keys as gspread populates those for you.

Maybe this is too old,

but while trying to use this I'm getting error as . TypeError: string indices must be integers with stack tace as

  File "../venv/lib/python3.8/site-packages/gspread/utils.py", line 592, in wrapper
    return f(*args, **kwargs)
  File "../venv/lib/python3.8/site-packages/gspread/models.py", line 1186, in batch_update
    data = [
  File "../venv/lib/python3.8/site-packages/gspread/models.py", line 1187, in <listcomp>
    dict(vr, range=absolute_range_name(self.title, vr['range']))`
for

uploaded = d2g.upload(df, spreadsheet_key, wks_name, credentials=creds, row_names=False) uploaded.batch_update(body)`

where uploaded is of type <class 'gspread.models.Worksheet'>

[lavigne958]

Hi @andrewgross, this is definitely something that can be done with gspread. You can do it yourself like mentioned by @alugowski but a helper function in theWorksheet object could be nice.

Hi @vikrant-pune, we you mention is a different issue and is getting off-topic. Please open a new issue with you message, backtrack and you code snippet and we'll help you from there. Thank you.

[lavigne958]

In order to add conditional formating, one could make a new PR and introduce this feature. This can be done using other requests as example and using the following documentation:

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addconditionalformatrulerequest