-3

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
comiconor
  • 71
  • 5
  • 1
    Does this answer your question? [Script to Update Multiple Google Sheet Filter View Ranges](https://stackoverflow.com/questions/64346131/script-to-update-multiple-google-sheet-filter-view-ranges) – George Feb 13 '23 at 22:54
  • I'm afraid not. Not quite sure how to incorporate the range change that I need into that script? – comiconor Feb 13 '23 at 22:59
  • 1
    You can specify your desired range in the `range` field in the `updateFilterView` requests. You can indicate the start/end row index and start/end column index. You can visit this links for the full details on the request. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatefilterviewrequest https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#filterview – George Feb 14 '23 at 00:03
  • First, I apologize that my answer was not useful for your situation. About your question, I posted an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize again. – Tanaike Feb 14 '23 at 02:42

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165