Home Duplicating a Google Sheets filter view with Google Apps Script; reversing direction

Questions

Duplicating a Google Sheets filter view with Google Apps Script; reversing direction

June 12, 2023

I am using the code in this answer to import the filter views from a tab called "MAIN" to a tab called "NEW".

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);
}

The solution works great. But what I can’t understand is how to reverse the direction.

In order to import the filter views from "NEW" onto "MAIN", I figured I would just swap out the text in the code: replace "MAIN" with "NEW", and vice versa. But surprisingly, this doesn’t work. After having made that swap, the code ignores "NEW" entirely, and just duplicates on "MAIN" all the filter views which are already on "MAIN". So, if Filter1 was a filter view on "MAIN", now there are two filter views on "MAIN" called Filter1. What am I missing?

>Solution :

Modification points:

In the case of Sheets API, it seems that even when ranges: [srcSheetName, dstSheetName] is used with Sheets.Spreadsheets.get, the order of returned sheets is followed by the indexes of sheets. I thought that the reason for your current issue is due to this. From your question, I could remember this.

In this case, please modify as follows.

From:

const [srcSheet, dstSheet] = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [srcSheetName, dstSheetName], fields: "sheets(filterViews)" }).sheets;

To:

const { srcSheet, dstSheet } = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [srcSheetName, dstSheetName], fields: "sheets(properties(title),filterViews)" }).sheets.reduce((o, e) => (o[e.properties.title == srcSheetName ? "srcSheet" : "dstSheet"] = e, o), {});