Google Apps Script refresh filter view along with a basic filter

Question

I use the following function to refresh basic filters (filtering and sorting) after my data changes:

function refreshDashboardFilter() {
  let {
    basicFilter
  } = Sheets.Spreadsheets.get(spreadsheetID, {
    ranges: ["Dashboard"],
    fields: "sheets(basicFilter)"
  }).sheets[0];

  if (basicFilter) {
    Sheets.Spreadsheets.batchUpdate({
      requests: [{
        setBasicFilter: {
          filter: basicFilter
        }
      }]
    }, spreadsheetID);
  }
}

Is it possible to refresh a filter view as well in a similar way? Probably, use the same function for both.

Answer 1

As another approach, in order to refresh both the basic filter and the filter view, I think that it can be achieved by 2 API calls. The modified script is as follows.

Modified script:

function refreshDashboardFilter() {
  const spreadsheetID = "###"; // Please set your Spreadsheet ID.
  const sheetNames = ["Dashboard"]; // Please set your sheet names. In this case, you can set multiple sheet names.

  // Retrieve all basicFilters and filterViews from sheets by Sheets API.
  const obj = Sheets.Spreadsheets.get(spreadsheetID, { ranges: sheetNames, fields: "sheets(basicFilter,filterViews)" });

  // Create a request body.
  const requests = obj.sheets.reduce((ar, { basicFilter, filterViews }) => {
    if (basicFilter) {
      ar.push({ setBasicFilter: { filter: basicFilter } })
    }
    if (filterViews) {
      ar.push(filterViews.map(filter => ({ updateFilterView: { filter, fields: "*" } })));
    }
    return ar;
  }, []);
  if (requests.length == 0) return;
  
  // Update all basicFilters and filterViews by Sheets API.
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetID);
}

• When this script is run, all basicFilters and filterViews of sheetNames are refreshed by 2 API calls.

References:

• Method: spreadsheets.get
• Method: spreadsheets.batchUpdate
• reduce()
• Related threads.
  • Update all filter view ranges
  • How to refresh a sorting with Google Apps Script?

Answer 2

Suggestion: call batchUpdate() with an updateFilterView request

You can use another Sheets.Spreadsheets.get() method to retrieve the filter views then another batchUpdate() to refresh each and every filter view that was retrieved. Each filter view has a unique ID so a separate batchUpdate() request is needed for each one.

Try this slightly modified version of your code:

function refreshDashboardFilter() {
  let {
    basicFilter
  } = Sheets.Spreadsheets.get(spreadsheetID, {
    ranges: ["Dashboard"],
    fields: "sheets(basicFilter)"
  }).sheets[0];

  //Retrieves all filter views in the sheet
  let {
    filterViews
  } = Sheets.Spreadsheets.get(spreadsheetID, {
    ranges: ["Dashboard"],
    fields: "sheets(filterViews)"
  }).sheets[0];

  if (basicFilter) {
    Sheets.Spreadsheets.batchUpdate({
      requests: [{
        setBasicFilter: {
          filter: basicFilter
        }
      }]
    }, spreadsheetID);
  }

  //Updates all fields of each filter view that was retrieved.
  if (filterViews) {
    filterViews.forEach(filterView => {
      Sheets.Spreadsheets.batchUpdate({
        requests: [{
          updateFilterView: {
            filter: filterView,
            fields: "*"
          }
        }]
      }, spreadsheetID);
    })
  }
}

Reference:

• updateFilterViewRequest