Cannot update alignment in google spreadsheets api, Unknown name "fields", python, how to fix?

1
votes

Try to put data, and update it alignment, but got that error.

'field': 'data[81]', 'description': 'Invalid JSON payload received. Unknown name "fields" at 'data[81]': Cannot find field.'

data = {
    "range": range_query,
    "values": range_query_body,
    "cell": {
        "userEnteredFormat": {
            "horizontalAlignment": "CENTER",
            "verticalAlignment": "MIDDLE",
        }
    },
    "fields": 'userEnteredFormat',

}
value_input_option = "RAW"
    
body = {
    'valueInputOption': value_input_option,
    'data': data
}


result = self.SHEET.values().batchUpdate(
spreadsheetId=spreadsheet_id, body=body).execute()

If remove cell, and field, it work.

1
pythongoogle-apigoogle-sheets-api
Thank you for replying. I deeply apologize for you code hard to read. This is due to my poor skill. I think that I have to study more. And when I could modify the script to more simple script, I would like to propose it again. So in the current stage, I would like to delete my answer, because I don't want to confuse other users. I deeply apologize for this again. – Tanaike
What exactly do you want to align? The position of values in cells? – ziganotschka

1 Answers

2
votes

Do not confuse the methods spreadsheets.batchUpdate and spreadsheets.values.batchUpdate

  • In your code you are trying to use the method spreadsheets.values.batchUpdate - this method only allows you to modify values, not the formatting!
  • Also, spreadsheets.values.batchUpdate does not accept the parameter fields (hence your error), nor cell
  • horizontalAlignment and verticalAlignment are properties belonging to CellFormat which in its turn is a nested property of cells
  • Consequently, to change the alignment, you need to perform the request UpdateCells, which is a valid request for the method spreadsheets.batchUpdate - NOT spreadsheets.values.batchUpdate
  • If you want to insert values AND format their alignment - you need to perform two different requests, luckily spreadsheets.batchUpdate allows you to perform several requests in one call
  • In your specific case you can even update both the cell format and cell values within the single request updateCells
  • You need to implement it as shown by Tanaike (make sure that you apply it to a spreadsheet object, not a sheet object) - his code is not hard to read, there is no way to implement it in a simplier manner.

In order to get a better understanding

  • Familiarize yourself with the cells resource
  • Familiarize yourself with the available requests for spreadsheets.values.batchUpdate
  • Use the Try this API feature that allows you easy testing from the UI by chosing allowed request body properties from a dropdown menu - with the correct syntax and nesting
  • Once you succesfully elaborate you request in the Try this API - you will gian an understanding of the request body structure and can easily implement it into your code.

enter image description here