How to duplicate a Google Sheets filter view using Google Apps Script?

Question

This should be an easy question, and yet I can't find any examples online. (This is much more complicated than what I need.)

Situation: I have a spreadsheet with two tabs. One is called "MAIN" and the other is called "NEW". The spreadsheet called "MAIN" has a bunch of filter views. I want to use a script which, when called, copies the filter views of MAIN onto NEW (while also deleting any preexisting filter views on NEW).

My attempt:

function resetFilterViews(){

  // locate files
  var source = SpreadsheetApp.getActive().getSheetByName("MAIN");
  var dest = SpreadsheetApp.getActive().getSheetByName("NEW");

  // remove existing filter views from NEW
  dest.getFilter().remove();

  // copy all filter views from MAIN onto NEW
  dest.DuplicateFilterViewRequest(source.getFilter());
  
}

I can't figure out why dest.getFilter().remove() doesn't remove the filters on MAIN. And I'm definitely not using DuplicateFilterViewRequest in the correct way (see here).

Answer 1

There are several filters in Google Spreadsheet. For example, getFilter() can be used for the basic filter. I think that this might be an answer to I can't figure out why dest.getFilter().remove() doesn't remove the filters on MAIN..

In the case of How to duplicate a Google Sheets filter view using Google Apps Script?, when filter view you think is the filter view, unfortunately, in the current stage, it seems that the filter views cannot be managed by Spreadsheet service (SpreadsheetApp). So, in this case, it is required to use Sheets API. I guessed that in this case, this sample script might be able to be used. In this answer, I would like to propose a sample script by modifying that sample script.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  const srcSheetName = "MAIN"; // This is from your question.
  const dstSheetName = "NEW"; // This is from your question.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dstSheetId = ss.getSheetByName(dstSheetName).getSheetId();
  const spreadsheetId = ss.getId();
  const [srcSheet, dstSheet] = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [srcSheetName, dstSheetName], fields: "sheets(filterViews)" }).sheets;
  const dstFilterViews = dstSheet.filterViews ? dstSheet.filterViews.map(({ filterViewId }) => ({ deleteFilterView: { filterId: filterViewId } })) : [];
  const requests = [
    ...dstFilterViews,
    ...srcSheet.filterViews.map(filter => {
      filter.range.sheetId = dstSheetId;
      delete filter.filterViewId;
      return { addFilterView: { filter } };
    })
  ];
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

• When this script is run, all filter views of "MAIN" sheet are copied to "NEW" sheet.

• Added: I include the following function.

  I would like to delete the filter views in "NEW" (and then add to it the filter views in "MAIN"). Right now, the code adds the ones in "MAIN" to "NEW", but without removing the old filter views from "NEW".

Note:

• From Can you keep your original answer visible as well? That way there are two versions: one that deletes the old filter views, and one that keep them?, my 1st proposed script is as follows. In this case, the filter views are copied from "MAIN" sheet to "NEW" sheet without removing the filter views of "NEW" sheet.

  function myFunction() {
    const srcSheetName = "MAIN"; // This is from your question.
    const dstSheetName = "NEW"; // This is from your question.
  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const dstSheetId = ss.getSheetByName(dstSheetName).getSheetId();
    var spreadsheetId = ss.getId();
    var filterViews = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [srcSheetName], fields: "sheets(filterViews)" }).sheets[0].filterViews;
    var requests = filterViews.map(filter => {
      filter.range.sheetId = dstSheetId;
      delete filter.filterViewId;
      return { addFilterView: { filter } };
    });
    Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
  }
  

References:

• Method: spreadsheets.get
• Method: spreadsheets.batchUpdate
• AddFilterViewRequest