Iteratively filter a Pivot Table in google App script

[Matteo Borgato]

I had a code that was helping me save as pdf a list of invoices. I was doing that by iterating through the list of our clients and filtering the "invoice pivot" with the client id and then saving as pdf the resulting pivot table.

Since last week my flow is not working anymore. I did some testing and ultimately understood that what is not working is this function updateFilteredPivot(). The filter is only applied in the first run of the iteration when the pivot table has no filter active. Any idea on what could have changed and how to work around this issue?

function updateFilteredPivotTable(lastRow, filter, pivotTableSheetName) {
  var lastRow = lastRow
  var filter = filter.toString()
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var pivotTableSheetName = pivotTableSheetName
  var pivotTableSheetId = ss.getSheetByName(pivotTableSheetName).getSheetId();
  var fields = "sheets(properties.sheetId,data.rowData.values.pivotTable)";
  var sheets = Sheets.Spreadsheets.get(ss.getId(), {fields: fields}).sheets; 
  for (var i in sheets) {
    if (sheets[i].properties.sheetId == pivotTableSheetId) {
      var pivotTableParams = sheets[i].data[0].rowData[0].values[0].pivotTable;
      break;
    }
  }
  
  // Update source range:
  pivotTableParams.source.endRowIndex = lastRow  ; // improve get last row with data in column A
  pivotTableParams.criteria = { 26: {"visibleValues": [filter]}}; // set filter field column 26 equal to filter
  
  // Send back the updated params
  var request = {
    "updateCells": {
      "rows": {
        "values": [{
          "pivotTable": pivotTableParams
        }]
      },
      "start": {
        "sheetId": pivotTableSheetId
      },
      "fields": "pivotTable",     
    }
  };
  
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

[Darren D'Mello]

Any APIs depreciated? Anyone in the group may please help us know.

I too came across a similar situation using PivotTables.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8c86e91c-0d54-45b2-aa68-3227ad183950o%40googlegroups.com.

--

Best,

Darren