0

This is my first post here and I am new to coding. I have been tasked with creating an automated report which will send a google form submitter a graph to help them monitor their production versus their daily goal. To do this I am using the new developer Google sheets script to refresh a pivot table. I found this code online, and it works great, however, I want to add a line which will filter based to the unique submitter's data. Here is the code I have so far:

function updatePivotTable() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var pivotTableSheetName = "Lunch Chart";
  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 = 40;

// Send back the updated params
var request = {
"updateCells": {
  "rows": {
    "values": [{
      "pivotTable": pivotTableParams
    }]
  },
  "start": {
    "sheetId": pivotTableSheetId
  },
  "fields": "pivotTable"
}
};

Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

Is this possible? Where would I add in the filter piece? I found this on the google developer site, but I am very new to coding so I don't really know where to put it or how to make it conditional. https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria

Thank you!

Rubén
  • 34,714
  • 9
  • 70
  • 166
TMaybee
  • 1
  • 2
  • You may check this [post](https://stackoverflow.com/questions/44575213/pivot-filter-criteria-not-properly-applied) which submitted an [issue](https://issuetracker.google.com/issues/64207799) regarding pivot filter criteria. You may want to follow this to be updated. – abielita Aug 23 '17 at 17:46
  • Thank you for the link, I haven't made my filter code yet, but it seems like it won't matter because it is not working properly anyways. Hopefully, someone smarter than me can find a work around on this issue. – TMaybee Aug 24 '17 at 19:04

2 Answers2

1

I am not sure if this is still relevant to you but you can add a filter using following piece of code-

"criteria": {
            <col_index>: {"visibleValues": <filter criteria>},
            <col_index>: {"visibleValues": <filter criteria>},
Alpesh Jain
  • 89
  • 5
0

I've had the same problem and didn't find an easy explanation or code. Here is what I've done and it works:

function updatePivotTable() {
  var ss = SpreadsheetApp.openById(SHEET_ID);
  var pivotTableSheetName = "Pivot";
  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 = 111;
  pivotTableParams.criteria = { 3: {"visibleValues": ["foo"]}};
  
  // Send back the updated params
  var request = {
    "updateCells": {
      "rows": {
        "values": [{
          "pivotTable": pivotTableParams
        }]
      },
      "start": {
        "sheetId": pivotTableSheetId
      },
      "fields": "pivotTable",     
    }
  };
  
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

So basically you need to pass the criteria as a pivotTableParams where the key of the object is the index of the column that you want to query and the value should be passed as another object with the format {"visibleValues": ["foo"]}.

Gabriel Cocenza
  • 1