使用Google Apps Script复制Google Sheets的筛选视图;反向操作

huangapple go评论73阅读模式
英文:

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

问题

我正在使用[此答案][1]中的代码,将名为“MAIN”的选项卡中的筛选视图导入到名为“NEW”的选项卡中。

    function myFunction() {
      const srcSheetName = "MAIN"; // 这来自于你的问题。
      const dstSheetName = "NEW"; // 这来自于你的问题。
    
      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);
    }

这个解决方案非常有效。但我无法理解如何反转方向。

为了将来自“NEW”的筛选视图导入到“MAIN”中,我想我只需在代码中交换文本:将“MAIN”替换为“NEW”,反之亦然。但令人惊讶的是,这并不起作用。在进行了交换后,代码完全忽略了“NEW”,只是在“MAIN”上复制了已经存在于“MAIN”的所有筛选视图。因此,如果 `Filter1` 是“MAIN”上的一个筛选视图,现在在“MAIN”上将有两个名为 `Filter1` 的筛选视图。我漏掉了什么?
英文:

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?

答案1

得分: 2

修改点:

  • 在使用 Sheets API 的情况下,即使在使用 Sheets.Spreadsheets.get 时使用了 ranges: [srcSheetName, dstSheetName],返回的表格顺序仍然遵循表格的索引顺序。我认为您当前的问题出现的原因是这个。从您的问题中,我记得了这一点。

在这种情况下,请按照以下方式进行修改。

从:

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

到:

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), {});
  • 通过这个修改,脚本中可以忽略“MAIN”和“NEW”标签的顺序。
英文:

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), {});
  • By this modification, the order of tabs of "MAIN" and "NEW" can be ignored in this script.

huangapple
  • 本文由 发表于 2023年6月12日 16:31:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76454824.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定