Script to Update Multiple Google Sheet Filter View Ranges

user_14446164 0
Viewed 671

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());  
}
google-apps-script google-sheets
1 Answers

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:

user_71086530
Related