How to write a Google Script that auto updates the Filter Range for personal filters

Question

I have a very large sheet that has multiple tabs for multiple teams, each of these tabs has personalized filter's for each individual & all of these are currently updated regularly and manually as new line items are added to the sheet. How can I write up a Google Script that recognizes the last row with data (ANY) in it, and the last column--then extends the range from A1 to the new end cell and updates each of the filter ranges on this worksheet to this?

Most of the sample code that I have used for this just creates a new filter view for the new range and deletes the previous - but I have between 5 and 20 personal filters on each worksheet that I would like to get updated on a regular basis. Edit I've provided sample code that I wrote for this, this is not copied but rather using what the UpdateFilterView Request documentation had available. I do not know if I have the filter field set up correctly referencing "filterSettings", and did not know what to use in the "fields" section. Additionally, I cannot tell if the batchUpdate was set up correctly.

EDIT #2 7.24.2019 - Current code is setup as seen below, getting an invalid JSON payload error currently that says

API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload received. Unknown name "fields" at 'requests[0]': Cannot find field.
Invalid value at 'requests[0].update_filter_view.filter.filter_view_id' (TYPE_INT32), "*" Invalid JSON payload received. 
Unknown name "sheet_id" at 'requests[0].update_filter_view.filter.range': Cannot find field. (line 27, file "macros")

function UpdateFilterView() {
    var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();  
    for each (var dataSheet in sheets){ 
      var lastRow = dataSheet.getLastRow();
      var lastColumn = dataSheet.getLastColumn();
      var sheetId = dataSheet.getSheetId();
      var filterSettings = {
        "filterViewId": "*",
        "range":{
        "sheetID": sheetId,
        "startRowIndex": 0,
        "endRowIndex": lastRow,
        "startColumnIndex": 0,
        "endColumnIndex": lastColumn
        }
       }
      };


var requests = [{
  "fields": "*",
  "updateFilterView":{
  "filter": filterSettings
  }
 }];

 Sheets.Spreadsheets.batchUpdate({"requests":requests},sheetId);

 }

I expect that each personalized filter will update OnEdit() for each of the personalized filters on each worksheet.

Have you tried anything at all? Do you have any code already? — ross
@ross added some sample code to this, its horrendous - but thank you for taking a look — Joe

ziganotschka ziganotschka · Accepted Answer · 2019-07-24T08:58:39

You are getting the last row and column of a specific sheet called dataSheet (which is not defined in your code btw) instead of doing it for each sheet of interest.

Instead you need to loop through all sheets and update your filter for each of sheet accessing its range inside the loop:

  var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();  
  for each (var dataSheet in sheets){ 
    var lastRow = dataSheet.getLastRow();
    var lastColumn = dataSheet.getLastColumn();
    var sheetId = dataSheet.getSheetId();
    var filterSettings = {
...

Also, keep in mind that the correct syntax is getSheetId() and not getSheetID(), just as the key word in filterSettings is "sheetId" and not "sheetID". You also need to specify "filterViewId" in filterSettings and "fields" in "updateFilterView" (can be set to "*").

How to write a Google Script that auto updates the Filter Range for personal filters

1 Answers

Instead you need to loop through all sheets and update your filter for each of sheet accessing its range inside the loop: