Froze a column inside a pivot table with api sheet

Question

Do you know how to freeze a column using FrozenColumnCount property (or another), inside a pivot table ?

request_body ={
        "requests" : {
            "updateCells" : {
                "rows" : {
                    "values" : [
                        {
                            "pivotTable" : {
                                #Data Source
                                "source" : {
                                    "sheetId": "0",
                                    "startRowIndex" : 0,
                                    "startColumnIndex" : 0,
                                },
                                #Row Field(s)
                                "rows" : [ 
                                    #field 2
                                    {
                                        "sourceColumnOffset" : 5, #Type Preneur
                                        "showTotals" : False,
                                        "sortOrder" : "ASCENDING"
                                    },
                                ],
                                #Columns Field(s)
                                "columns" : [
                                     #Field 2
                                    {
                                        "sourceColumnOffset" : 18, #Année
                                        "sortOrder" : "ASCENDING",
                                        "showTotals" : True
                                    }
                                ],
                                "criteria" : {
                                    6:{
                                        'visibleValues' : ['Solde Créditeur']
                                    },
                                }, 
                                "filterSpecs": [
                                    {
                                        "filterCriteria": {
                                            'visibleValues' : ['Solde Créditeur']
                                        },
                                        "columnOffsetIndex": 6
                                    }
                                ],                                    
                                #Values Field(s)
                                "values" : [
                                    {
                                        "sourceColumnOffset" : 21, #PNS BRUT PPSO
                                        "summarizeFunction" : "SUM",
                                        "name" : ""
                                    }
                                ],
                                "valueLayout" : "HORIZONTAL"
                            }
                        }
                    ]
                },
                "start": {
                    "sheetId" : id_new_sheet,
                    "rowIndex" : 0,
                    "columnIndex" : 0
                },
                'fields' : 'pivotTable'         
            }
        },
        "properties": {
            "gridProperpties" : {
            "frozenColumnCount": 1,
            }
        } 
    }

The pivot table works. I would like to freeze the first column. Here the error message I get : Invalid JSON payload received. Unknown name "properties": Cannot find field.". Details: "[{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'description': 'Invalid JSON payload received. Unknown name "properties": Cannot find field.'}]}]">.

score 0 · Accepted Answer · answered Jan 12 '22 at 12:06

Modification points:

frozenColumnCount is for the property of UpdateSheetPropertiesRequest. I thought that this is the reason of your issue.
Property of requests is required to be an array.

When these points are reflected in the request body, it becomes as follows.

Modified request body:

{
  "requests": [
    {
      "updateCells": {
        "rows": [
          {
            "values": [
              {
                "pivotTable": {
                  "source": {
                    "sheetId": 0,
                    "startRowIndex": 0,
                    "startColumnIndex": 0
                  },
                  "rows": [
                    {
                      "sourceColumnOffset": 5,
                      "showTotals": false,
                      "sortOrder": "ASCENDING"
                    }
                  ],
                  "columns": [
                    {
                      "sourceColumnOffset": 18,
                      "sortOrder": "ASCENDING",
                      "showTotals": true
                    }
                  ],
                  "criteria": {
                    "6": {
                      "visibleValues": [
                        "Solde Créditeur"
                      ]
                    }
                  },
                  "filterSpecs": [
                    {
                      "filterCriteria": {
                        "visibleValues": [
                          "Solde Créditeur"
                        ]
                      },
                      "columnOffsetIndex": 6
                    }
                  ],
                  "values": [
                    {
                      "sourceColumnOffset": 21,
                      "summarizeFunction": "SUM",
                      "name": ""
                    }
                  ],
                  "valueLayout": "HORIZONTAL"
                }
              }
            ]
          }
        ],
        "start": {
          "sheetId": id_new_sheet,
          "rowIndex": 0,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    },
    {
      "updateSheetProperties": {
        "properties": {
          "gridProperties": {
            "frozenColumnCount": 1
          },
          "sheetId": id_new_sheet
        },
        "fields": "gridProperties.frozenColumnCount"
      }
    }
  ]
}

In this modified request body, frozenColumnCount is set to the sheet id_new_sheet. If you want to change the sheet, please modify this.
From The pivot table works., I didn't modify the parameters of updateCells.

Reference:

UpdateSheetPropertiesRequest

score 0 · Answer 2 · answered Jan 12 '22 at 16:11

As you are already using batchUpdate to make requests, the easiest way to accomplish your goal is to make two requests on the same call. To accomplish this, simply transform the requests field and make it a list containing your two requests: the updateCells and a updateSheetProperties. You can populate the last one with the following:

"updateSheetProperties": {
    "fields":"gridProperties.frozenColumnCount",
    "properties": {
        "sheetId":0, #A 0 if this is the first sheet
        "gridProperties":{"frozenColumnCount":1}
        }
    }

You can read more about how those requests are structured here.

Froze a column inside a pivot table with api sheet

2 Answers2

Modification points:

Modified request body:

Reference: