2

I'm trying to use the below script to update the range on 50+ personal filter views in Sheets. I used the below code which I found here, (very helpful by the way!).

My only issue is this works great if I have each unique filterViewId. Is there a method here to modify this script to apply the updated range on ALL filter views within the sheet titled "MY SHEET"?

function UpdateFilterView() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var dataSheet = ss.getSheetByName('MY SHEET');  
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();
  var filterSettings = {
    "filterViewId": "1099738202",
    "range":{
      "sheetId": sheetId,
      "startRowIndex": 0,
      "endRowIndex": lastRow,
      "startColumnIndex": 0,
      "endColumnIndex": lastColumn
    }
  };
  var requests = [{
    "updateFilterView":{
      "filter": filterSettings,
      "fields": "*",  
    }
  }];
  Sheets.Spreadsheets.batchUpdate({"requests":requests}, ss.getId());  
}
Kbee
  • 27
  • 4
  • In your situation, for example, when there are 2 filter views, how do you want to do about the settings of filter for each filter views? – Tanaike Oct 14 '20 at 05:15
  • @tanaike I would like to apply the same settings to each filter view so that the range is the same for each (range needs to be updates to include the entire sheet). – Kbee Oct 14 '20 at 05:48
  • Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If that was not the direction you expect, I apologize. – Tanaike Oct 14 '20 at 07:48
  • Confirmed, the modified script you've provided works exactly as intended to update the range for all filterview ranges within a single sheet. You saved hours of my time thank you so much! – Kbee Oct 14 '20 at 13:55
  • Thank you for replying. I'm glad your issue was resolved. Thank you, too. – Tanaike Oct 14 '20 at 23:05

1 Answers1

3

I believe your goal as follows.

  • You want to update all filter views of a sheet ("MY SHEET") in a Google Spreadsheet using Google Apps Script.
  • The settings of all filter views are the same.

Modification points:

  • In your script, one filter view of the sheet "MY SHEET" is updated. So in this case, it is required to retrieve filterViewId of all filter views.
  • In order to retrieve filterViewId of all filter views, I used the method of "spreadsheets.get" in Sheets API.

When above points are reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services, and enable V8 runtime.

function UpdateFilterView() {
  var sheetName = "MY SHEET";  // Please set the sheet name.
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName(sheetName);
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();
  var spreadsheetId = ss.getId();
  var filterViews = Sheets.Spreadsheets.get(spreadsheetId, {ranges: [sheetName], fields: "sheets(filterViews)"}).sheets[0].filterViews;
  var requests = filterViews.map(e => ({
    updateFilterView: {
      filter: {
        filterViewId: e.filterViewId,
        range: {sheetId: sheetId, startRowIndex: 0, endRowIndex: lastRow, startColumnIndex: 0, endColumnIndex: lastColumn}
      },
      fields: "*",
    }
  }));
  Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheetId);
}
  • In this modified script, at first, filterViewId of all filter views are retrieved using the method of "spreadsheets.get" and create the request body, and then, the request body is used with the method of "spreadsheets.batchUpdate".

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165