Use Google App Script to change secondary sort column

Question

I have sheets that have a data range connected to a pivot table. The pivot table has a secondary row sor by the last column. I have queries that update the data range and add a column. I would like to be able to use Google App Script to sort by that new column. I can get the current pivot table and seem to be updating it but the changes do not seem to be happening. I used the method here. The original table

I used the method here. The original table is as follows: `

{
  "columns":[
    {
      "sortOrder":"ASCENDING",
      "sourceColumnOffset":2
    }
  ],
  "values":[
    {
      "summarizeFunction":"SUM",
      "sourceColumnOffset":4
    }
  ],
  "source":{
    "endColumnIndex":5,
    "startRowIndex":6,
    "endRowIndex":193,
    "sheetId":698433721,
    "startColumnIndex":0
    },
  "rows":[
    {
      "valueBucket":{
        "buckets":[
          {
            "stringValue":"6/26/2019"
          }
        ]
      },
      "showTotals":true,
      "sortOrder":"DESCENDING",
      "sourceColumnOffset":3
    }
  ]
}

' The 'stringValue" contains the text of the header of the current column. I assume that changing that would change the sort column but it has no effect.

When I read the pivot table again after making the change the new data shows up but the UI representation of the pivot table does not change and the sort column does not change.

DeeKay789 DeeKay789 · Accepted Answer · 2019-07-11T11:23:06

Are you replacing the whole pivot table? I think the documentation sets this as a requirement no? To quote >>'Essentially, editing a pivot table requires replacing it with a new one'<<.

Use Google App Script to change secondary sort column

1 Answers