Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-api

Update all filter view ranges

Is there a app script I can run that will update the ranges of all the filter views on a sheet at once? I have hundreds of filter views, and it would be laborious to do it manually.

The filter views are all on a sheet called "Data'. I need to change the range from A1:AB3116 TO A1:AB9011

Thanks for any help.

Solution

  • I believe your goal is as follows.

    • You want to change the range of filter views.
    • You want to change from "A1:AB3116" to "A1:AB9011" of all filter views in "data" sheet.

    In this case, how about the following sample script?

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet and enable Sheets API at Advanced Google services, and save the script.

    Please confirm sheetName and obj. In this sample, your provided information is used.

    function myFunction() {
  var sheetName = "data"; // This is from your question.
  var obj = [{ before: "A1:AB3116", after: "A1:AB9011" }]; // This is from your question.

  // Retrieve spreadsheet and sheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = ss.getId();
  var sheet = ss.getSheetByName(sheetName);
  var sheetId = sheet.getSheetId();

  // Convert a1Notation to gridRange.
  var o = obj.map(({ before, after }) =>
    [before, after].map(r => {
      var rng = sheet.getRange(r);
      var rowStart = rng.getRow() - 1;
      var rowEnd = rowStart + rng.getNumRows();
      var colStart = rng.getColumn() - 1;
      var colEnd = colStart + rng.getNumColumns();
      return { sheetId, startRowIndex: rowStart, endRowIndex: rowEnd, startColumnIndex: colStart, endColumnIndex: colEnd };
    })
  );

  // Create request body for using the batchUpdate of Sheets API.
  var filterViews = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [sheetName], fields: "sheets(filterViews)" }).sheets[0].filterViews;
  var requests = filterViews.reduce((ar, { range, ...e }) => {
    var check = o.find(([{ startRowIndex, endRowIndex, startColumnIndex, endColumnIndex }]) => range.startRowIndex == startRowIndex && range.endRowIndex == endRowIndex && range.startColumnIndex == startColumnIndex && range.endColumnIndex == endColumnIndex);
    if (check) {
      ar.push({ updateFilterView: { filter: { filterViewId: e.filterViewId, range: check[1] }, fields: "*" } });
    }
    return ar;
  }, []);

  // Reuest Sheets API using the created request body.
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

    • When this script is run, the filter views are retrieved from "data" sheet. And, the range of A1:AB3116 is searched from the retrieved filter views, and when it is found, the range is changed to A1:AB9011 and update the filter views.

    • In this sample, when you change multiple changes of ranges, you can use them in obj.

    References: