Collapsing a group using Google Sheets API

2019-07-11 07:40发布

问题:

So as a workaround to difficulties creating a new sheet with groups I am trying to create and collapse these groups in a separate call to batchUpdate. I can call request an addDimensionGroup successfully, but when I request updateDimensionGroup to collapse the group I just created, either in the same API call or in a separate one, I get this error:

{
  "error": {
    "code": 400,
    "message": "Invalid requests[1].updateDimensionGroup: dimensionGroup.depth must be \u003e 0",
    "status": "INVALID_ARGUMENT"
  }
}

But I'm passing depth as 0 as seen by the following JSON which I send in my request:

{
    "requests":[{
          "addDimensionGroup":{
            "range":{
                "dimension":"ROWS",
                "sheetId":0,
                "startIndex":2,
                "endIndex":5}
            }
        },{
         "updateDimensionGroup":{
           "dimensionGroup":{ 
               "range": {
                   "dimension":"ROWS",
                   "sheetId":0,
                   "startIndex":2,
                   "endIndex":5
               },
               "depth":0,
               "collapsed":true
           },
           "fields":"*"
        }
    }],
    "includeSpreadsheetInResponse":true}',
   ...

I'm not entirely sure what I am supposed to provide for "fields", the documentation for UpdateDimensionGroupRequest says it is supposed to be a string ("string ( FieldMask format)"), but the FieldMask definition itself shows the possibility of multiple paths, and doesn't tell me how they are supposed to be separated in a single string.

What am I doing wrong here?

回答1:

The error message is actually instructing you that the dimensionGroup.depth value must be > 0:

If you call spreadsheets.get() on your sheet, and request only the DimensionGroup data, you'll note that your created group is actually at depth 1:

GET https://sheets.googleapis.com/v4/spreadsheets/{SSID}?fields=sheets(rowGroups)&key={API_KEY}

This makes sense, since the depth is (per API spec):

depth number
The depth of the group, representing how many groups have a range that wholly contains the range of this group.

Note that any given particular DimensionGroup "wholly contains its own range" by definition.

If your goal is to change the status of the DimensionGroup, then you need to set its collapsed property:

{
  "requests": 
  [
    {
      "updateDimensionGroup": 
      {
        "dimensionGroup": 
        {
          "range": 
          {
            "sheetId": <your sheet id>,
            "dimension": "ROWS",
            "startIndex": 2,
            "endIndex": 5
          },
          "collapsed": true,
          "depth": 1
        },
        "fields": "collapsed"
      }
    }
  ]
}

For this particular Request, the only attribute you can set is collapsed - the other properties are used to identify the desired DimensionGroup to manipulate. Thus, specifying fields: "*" is equivalent to fields: "collapsed". This is not true for the majority of requests, so specifying fields: "*" and then omitting a non-required request parameter is interpreted as "Delete that missing parameter from the server's representation".

To change a DimensionGroup's depth, you must add or remove other DimensionGroups that encompass it.