CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-api
Ethernic
Ethernic

Reputation: 23

Google Sheets alternating colors via API

Using the sheets API with Python I'm attempting to format a sheet using alternating colors. In the UI this is found at Format > Alternating colors...

From what I've been able to find this is done via the API using banding. Unfortunately, I haven't been able to find a working example of how this is done. Below is the values dictionary I've constructed, color values aren't important at the moment, I'd just like it to colorize the sheet.

requests = {
  'bandedRange': {
    'bandedRangeId': 1,
    'range': {
      'sheetId': 0,
      'startRowIndex': 0,
      'endRowIndex': len(values),
      'startColumnIndex': 0,
      'endColumnIndex': 4,
    },
    'rowProperties': {
      'headerColor': {
        'red': 1,
        'green': 0,
        'blue': 1,
        'alpha': 1,
      },
      'firstBandColor': {
        'red': 1,
        'green': 0,
        'blue': 0,
        'alpha': 0,
      },
      'secondBandColor': {
        'red': 0,
        'green': 1,
        'blue': 0,
        'alpha': 0,
      }
    },
  },
  'fields': '*',
}
body = {'requests': requests}
response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

This fails with the following error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/_helpers.py", line 133, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/googleapiclient/http.py", line 840, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/$spreadsheet_id:batchUpdate?alt=json returned "Invalid JSON payload received. Unknown name "banded_range" at 'requests': Cannot find field.">

I'm fairly certain my issue is the fields value, but I can't find a valid example of what to use here. I get the same error if I omit the fields key entirely.

Upvotes: 2

Views: 1642

Answers (1)

Sam Berlin
Sam Berlin

Reputation: 3773

Per the reference docs for batchUpdate, requests takes an array of Request objects. Each Request must have exactly one field set, the available fields for banding being:

  "updateBanding": {
    object(UpdateBandingRequest)
  },
  "addBanding": {
    object(AddBandingRequest)
  },
  "deleteBanding": {
    object(DeleteBandingRequest)
  },

There is no field bandedRange, which is what you're trying to set. That's what the error message (Unknown name "banded_range" at 'requests': Cannot find field.) is saying... though I have no idea why it translated bandedRange to snake_case.

Depending on if you want to add or update the banded range, you'd set either updateBanding with an UpdateBandingRequest object, or addBanding with an AddBandingRequest object.

By adding addBanding to your JSON format. As explained above you will end up creating the below JSON. Also, the key fields is optional.

  {'addBanding': {
      'bandedRange': {
        'bandedRangeId': 1,
        'range': {
           'sheetId': 0,
            'startRowIndex': 0,
            'endRowIndex': len(values),
            'startColumnIndex': 0,
            'endColumnIndex': 4,
        },
        'rowProperties': {
          'headerColor': {
            'red': 1,
            'green': 0,
            'blue': 1,
            'alpha': 1,
          },
          'firstBandColor': {
            'red': 1,
            'green': 0,
            'blue': 0,
            'alpha': 0,
          },
          'secondBandColor': {
            'red': 0,
            'green': 1,
            'blue': 0,
            'alpha': 0,
          }
        },
      },
    },
   },

Upvotes: 3

Related Questions