CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
NomadicPeach
NomadicPeach

Reputation: 25

Script to Update Multiple Filter View Ranges for all sheets in workbook

Script to Update Multiple Google Sheet Filter View Ranges Similar but different question here.

I have several sheets in the same workbook where I would want to set up a trigger for the ranges to update in all of the filterviews for each sheet.

Is my best solution to make a file for each sheet in the workbook?

Upvotes: 1

Views: 161

Answers (1)

Tanaike
Tanaike

Reputation: 201503

In your situation, how about the following modified script? In this modification, I modified the sample script at this thread.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services. And, please set the sheet names you want to update.

function UpdateFilterView() {
  var sheetNames = ["Sheet1", "Sheet3",,,];  // Please set the sheet names you want to update.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = ss.getId();
  var sheets = Sheets.Spreadsheets.get(spreadsheetId, { ranges: sheetNames, fields: "sheets(filterViews)" }).sheets;
  var requests = sheets.flatMap((s, i) => {
    var dataSheet = ss.getSheetByName(sheetNames[i]);
    var endRowIndex = dataSheet.getLastRow();
    var endColumnIndex = dataSheet.getLastColumn();
    var sheetId = dataSheet.getSheetId();
    return s.filterViews.map(({ filterViewId }) => ({ updateFilterView: { filter: { filterViewId, range: { sheetId, startRowIndex: 0, endRowIndex, startColumnIndex: 0, endColumnIndex } }, fields: "*" } }));
  });
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);
}
  • When this script is run, all filter views from the specific sheets in a Google Spreadsheet are updated. In this case, one API is used.

Note:

  • For example, when you want to reflect this script in all sheets in a Google Spreadsheet, you can also the following sample script.

      function sample() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var spreadsheetId = ss.getId();
    var sheets = Sheets.Spreadsheets.get(spreadsheetId, { fields: "sheets(filterViews)" }).sheets;
    var allSheets = ss.getSheets();
    var requests = sheets.flatMap((s, i) => {
      var dataSheet = allSheets[i];
      var endRowIndex = dataSheet.getLastRow();
      var endColumnIndex = dataSheet.getLastColumn();
      var sheetId = dataSheet.getSheetId();
      return s.filterViews ? s.filterViews.map(({ filterViewId }) => ({ updateFilterView: { filter: { filterViewId, range: { sheetId, startRowIndex: 0, endRowIndex, startColumnIndex: 0, endColumnIndex } }, fields: "*" } })) : [];
    });
    if (requests.length == 0) return;
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);
  }

References:

Upvotes: 2

Related Questions