Update Google Sheet "Filter View" w/ New Range

1
votes

I have a spreadsheet with various filter views set in place. Normally this works great but occasionally new rows are added and I have to manually update the range in each filter view. I've tried searching for solutions online and came up with the following code that might update the range:

function UpdateFilterView() {
    var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
    //for each (var dataSheet in sheets){ 
      var lastRow = dataSheet.getLastRow();
      var lastColumn = dataSheet.getLastColumn();
      var sheetId = dataSheet.getSheetId();
      var filterSettings = {
        "filterViewId": "319575141",
        "range":{
        "sheetId": sheetId,
        "startRowIndex": 1,
        "endRowIndex": lastRow,
        "startColumnIndex": 1,
        "endColumnIndex": lastColumn
        }
       //}
      };


var requests = [{
  "fields": "*",
  "updateFilterView":{
  "filter": filterSettings
  }
 }];
 
  
 Sheets.Spreadsheets.batchUpdate({"requests":requests},sheetId);

 }

I took most of the code from a post I found (here) but for some reason it won't update the specified filter. Am I missing something? Also, I get the following error when I run the code "ReferenceError: Sheets is not defined (line 32, file "Code")" of which is referencing the line "Sheets.Spreadsheets.....".

Any thoughts on if this is even possible?

1
google-apps-scriptgoogle-sheetsdynamicfiltergoogle-sheets-api
From your error message, I thought that Sheets API might not be enabled. So can you confirm whether Sheets API has already been enabled at Advanced Google services? Ref If it is not enabled, please enable it and test it again. - Tanaike
Is your question resolved after enabling the Sheets API? - Cooper
Silly me, I guess I definitely needed to turn on Sheets API. SO I ran the script and received the following error: GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload Any thoughts? @Tanaike - JPadilla_1990
@Cooper, I get the following error message: GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload Any thoughts? - JPadilla_1990
More Specifically: GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload received. Unknown name "fields" at 'requests[0]': Cannot find field. (line 31, file "Code") - JPadilla_1990

1 Answers

2
votes

From your replying, I could confirm that in your current situation, Sheets API was enabled at Advanced Google services. By this, I could confirm your error message was changed. In order to resolve the new issue, how about this modification?

Modification points:

  • Please modify sheetId of Sheets.Spreadsheets.batchUpdate({"requests":requests},sheetId); to the spreadsheet ID.
  • Please include "fields": "*" in the object of updateFilterView.

When your script is modified, please modify as follows.

Modified script:

function UpdateFilterView() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  // Added
  var dataSheet = ss.getSheetByName('Data');  // Modified
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();
  var filterSettings = {
    "filterViewId": "319575141",
    "range":{
      "sheetId": sheetId,
      "startRowIndex": 1,
      "endRowIndex": lastRow,
      "startColumnIndex": 1,
      "endColumnIndex": lastColumn
    }
  };
  var requests = [{
    "updateFilterView":{
      "filter": filterSettings,
      "fields": "*",  // Modified
    }
  }];
  Sheets.Spreadsheets.batchUpdate({"requests":requests}, ss.getId());  // Modified
}

Note:

  • When filterViewId of 319575141 is not correct, an error like Invalid value at 'requests[0].update_filter_view.filter.filter_view_id occurs. Please be careful this.

References: