如何使用Google Apps Script复制Google Sheets筛选视图?

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

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

问题

这应该是一个简单的问题,但我在网上找不到任何示例。(这里比我需要的复杂得多。)

情况: 我有一个包含两个标签页的电子表格。一个名为“MAIN”,另一个名为“NEW”。名为“MAIN”的电子表格有一堆筛选视图。我想要使用一个脚本,当调用时,将MAIN的筛选视图复制到NEW(同时删除NEW上的任何现有筛选视图)。

我的尝试:

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

我无法弄清楚为什么 dest.getFilter().remove() 不会移除MAIN上的筛选器。而且我明确没有正确使用 DuplicateFilterViewRequest(请参见这里)。

英文:

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).

答案1

得分: 2

以下是您要求的内容的中文翻译:

在Google电子表格中有几种筛选器。例如,getFilter() 可以用于基本筛选。我认为这可能是回答我不明白为什么dest.getFilter().remove()不会删除MAIN上的筛选器。的答案。

如何使用Google Apps Script复制Google Sheets筛选器视图?这种情况下,当你认为filter view是筛选器视图时,不幸的是,在当前阶段,似乎无法通过电子表格服务(SpreadsheetApp)管理筛选器视图。因此,在这种情况下,需要使用Sheets API。我猜想在这种情况下,这个示例脚本可能可以使用。在这个答案中,我想通过修改那个示例脚本提出一个示例脚本。

示例脚本:

在使用此脚本之前,请启用高级Google服务中的Sheets API

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);
}
  • 运行此脚本时,将复制“MAIN”工作表的所有筛选器视图到“NEW”工作表。

  • 添加:我包括以下功能。

    我想删除“NEW”中的筛选器视图(然后将“MAIN”中的筛选器视图添加到其中)。目前,该代码将“MAIN”中的筛选器视图添加到“NEW”中,但不会删除“NEW”中的旧筛选器视图。

注意:

  • 你能保留原始答案可见吗?这样就有两个版本:一个删除旧的筛选器视图,一个保留它们?,我的第一个提议的脚本如下。在这种情况下,筛选器视图将从“MAIN”工作表复制到“NEW”工作表,而不会删除“NEW”工作表的筛选器视图。
function myFunction() {
  const srcSheetName = "MAIN"; // 这是来自你的问题。
  const dstSheetName = "NEW"; // 这是来自你的问题。

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

参考:

英文:

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:

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

发表评论

匿名网友

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

确定