CODEWITHSUNDEEP
pythongoogle-sheets-apigoogle-api-python-client
jwillis0720
jwillis0720

Reputation: 4477

Update single row formatting for entire sheet

I want to just apply a formatting from a JSON Entry. The first thing I did was make my desirable format on my spreadsheet for the second row of all columns. I then retrieved them with a .get request (from A2 to AO3).

request = google_api.service.spreadsheets().get(
    spreadsheetId=ss_id,
    ranges="Tab1!A2:AO3",
    includeGridData=True).execute()

The next thing I did was collect each of the formats for each column and record them in a dictionary.

my_dictionary_of_formats = {}
row_values = row_1['sheets'][0]['data'][0]['rowData'][0]['values']
for column in range(0, len(row_values)):
    my_dictionary_of_formats[column] = row_values[column]['effectiveFormat']

Now I have a dictionray of all my effective formats for all my columns. I'm having trouble now applying that format to all rows in each column. I tried a batchUpdate request:

cell_data = {
  "effectiveFormat": my_dictionary_of_formats[0]}

row_data = {
  "values": [
      cell_data
  ]
}

update_cell = {
  "rows": [
       row_data
  ],
  "fields": "*",
  "range": 
    {
      "sheetId": input_master.tab_id,
      "startRowIndex": 2,
      "startColumnIndex": 0,
      "endColumnsIndex": 1
    }
}

request_body = {
  "requests": [
      {"updateCells": update_cell}],
  "includeSpreadsheetInResponse": True,
  "responseIncludeGridData": True}

service.spreadsheets().batchUpdate(spreadsheetId=my_id, body=request_body).execute()

This wiped out everything and I'm not sure why. I don't think I understand the fields='* attribute.

TL;DR I want to apply a format to all rows in a single column. Much like if I used the "Paint Format" tool on the second row, first column and dragged it all the way down to the last row.

-----Update

Hi, thanks to the comments this was my solution:

###collect all formats from second row
import json
row_2 = goolge_api.service.spreadsheets().get(
    spreadsheetId=spreadsheet_id,
    ranges="tab1!A2:AO2",
    includeGridData=True).execute()
my_dictionary = {}
row_values = row_2['sheets'][0]['data'][0]['rowData'][0]['values']
for column in range(0,len(row_values)):
    my_dictionary[column] = row_values[column]
json.dumps(my_dictionary,open('config/format.json','w'))

###Part 2, apply formats
requests = []
my_dict = json.load(open('config/format.json'))
for column in my_dict:
    requests.append(

    {
      "repeatCell": {
        "range": {
            "sheetId": tab_id,
            "startRowIndex": str(1),
            "startColumnIndex":str(column),
            "endColumnIndex":str(int(column)+1)
    },
       "cell": {
          "userEnteredFormat": my_dict[column]
    },
        'fields': "userEnteredFormat({})".format(",".join(my_dict[column].keys()))
        }
    })

body = {"requests": requests}
google_api.service.spreadsheets().batchUpdate(spreadsheetId=s.spreadsheet_id,body=body).execute()

Upvotes: 2

Views: 370

Answers (1)

tehhowch
tehhowch

Reputation: 9872

When you include fields as a part of the request, you indicate to the API endpoint that it should overwrite the specified fields in the targeted range with the information found in your uploaded resource. fields="*" correspondingly is interpreted as "This request specifies the entire data and metadata of the given range. Remove any previous data and metadata from the range and use what is supplied instead."

Thus, anything not specified in your updateCells requests will be removed from the range supplied in the request (e.g. values, formulas, data validation, etc.).

You can learn more in the guide to batchUpdate

For an updateCell request, the fields parameter is as described:

The fields of CellData that should be updated. At least one field must be specified. The root is the CellData; 'row.values.' should not be specified. A single "*" can be used as short-hand for listing every field.

If you then view the resource description of CellData, you observe the following fields:

  • "userEnteredValue"
  • "effectiveValue"
  • "formattedValue"
  • "userEnteredFormat"
  • "effectiveFormat"
  • "hyperlink"
  • "note"
  • "textFormatRuns"
  • "dataValidation"
  • "pivotTable"

Thus, the proper fields specification for your request is likely to be fields="effectiveFormat", since this is the only field you supply in your row_data property.

Consider also using the repeatCell request if you are just specifying a single format.

Upvotes: 1

Related Questions