2

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?

Tanaike
  • 181,128
  • 11
  • 97
  • 165
JPadilla_1990
  • 35
  • 5
  • 1
    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](https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services) If it is not enabled, please enable it and test it again. – Tanaike Jun 25 '20 at 06:59
  • Is your question resolved after enabling the Sheets API? – Cooper Jun 25 '20 at 15:11
  • 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 Jun 26 '20 at 04:43
  • @Cooper, I get the following error message: GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload Any thoughts? – JPadilla_1990 Jun 26 '20 at 04:46
  • 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 Jun 26 '20 at 04:56
  • Thank you for replying. For your new issue, I proposed a modified script as an answer. Could you please confirm it? – Tanaike Jun 26 '20 at 05:49

1 Answers1

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • You are a GENUIS! That did exactly what I wanted it to do, thank you SO MUCH! You won't believe how much my head has hurt the last few days trying to figure this out. – JPadilla_1990 Jun 26 '20 at 07:02
  • @JPadilla_1990 Thank you for replying. I'm glad your issue was resolved. Thank you, too. – Tanaike Jun 26 '20 at 07:04