如何使用Google Apps脚本移除Google电子表格中的所有过滤器？ [英] How can I remove all filters in a Google Spreadsheet using Google Apps Script?

问题描述

我想编写一个脚本，每天一次删除电子表格中的所有过滤器。我一直无法找到任何有关如何处理Google Apps脚本中的过滤的良好文档。

如果使用Python API这是可能的。

解决方案

有可能，有点作弊:)

（更新后的答案：有一个新的Google服务允许它，请参阅第2号）

---

1简单的方法

好吧，我设法这样做：

  var row = 1 //带有过滤器的行
 var rowBefore =行
 
 Sheet.insertRowBefore（row ）; //在过滤器之前插入一行
 row ++; //过滤器向下移动
 
 var Line = Sheet.getRange（row +：+ row）; //获取过滤行
 Line.moveTo（Sheet.getRange（rowBefore +：+ rowBefore））; //移动到新行
 Sheet.deleteRow（row）; //删除过滤器行 - 这将清除过滤器
 
 Sheet.setFrozenRows（rowBefore）; //如果行被冻结之前，再次冻结
  

---

2 - 使用表格服务：

（复制自 https://issuetracker.google.com/issues/36753410 ，评论＃172）

  function clearFilter（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var ssId = ss.getId（）; 
 var sheetId = ss.getActiveSheet（）。getSheetId（）; 
 var requests = [{
clearBasicFilter：{
sheetId：sheetId 
} 
}]; 
 Sheets.Spreadsheets.batchUpdate（{'requests'：requests}，ssId）; 
} 
  

---

3 - 很难的方法：（保留在这里是因为我在写一段代码之前先想一想）

1 - 删除第一行（或者过滤按钮所在的行）

  Sheet.deleteRow（1）; 
  

2 - 再次插入：）

  Sheet.insertRowBefore（1）; 
  

<3> - 设置其标题

  Sheet.getRange（A1）。setValue（Hi there）; 
 Sheet.getRange（B1）。setValue（Here I'm Again with no filter）; 
 
 //或
 var LineVals = Sheet.getRange（1：1）。getValues（）; 
 LineVals [0] [0] =你好; 
 LineVals [0] [1] =我在这里; 
 LineVals [0] [2] =没有过滤器; 
 Sheet.getRange（1：1）。setValues（LineVals）; 
 // getValues是为了保持数组与行的大小完全一致
  

4 - 再次设置线条颜色：

  //单元格颜色
 Sheet.getRange（1：1）。setBackground （ '＃ff3300'）; 
  

5 - 设置字体样式：

 //我没有测试这些... 
 Sheet.getRange（1：1）。setFontColor .... 
 Sheet.getRange（ 1：1）。setFontFamily .... 
 Sheet.getRange（1：1）。setFontSize .... 
 //实际上有很多可用的字体选项... 。
  

6 - 如果之前被冻结，请再次冻结：

  Sheet.setFrozenRows（1）; 
  

7 - 最后，如果他们有 NamedRanges ，考虑命名整个列而不是单个单元格，这将保持您的名字不受伤害。

I want to write a script that removes all filters in a spreadsheet once a day. I haven't been able to find any good documentation on how to handle filtering in Google Apps Script.

I'd also be open to doing this using the Python API if it was possible that way.

解决方案

It IS possible, with a little "cheating" :)

(Updated answer: there is a new Google service that allows it, see number 2)

---

1 - Easy way

Well, I managed to do it this way:

var row = 1 //the row with filter 
var rowBefore = row 

Sheet.insertRowBefore(row); //inserts a line before the filter
row++; //row with filters moved down

var Line = Sheet.getRange(row + ":" + row); //gets the filter line
Line.moveTo(Sheet.getRange(rowBefore + ":" + rowBefore)); //move to new line
Sheet.deleteRow(row); //deletes the filter line - this clears the filter

Sheet.setFrozenRows(rowBefore); //if row was frozen before, freeze it again

---

2 - Using the Sheets service:

(Copied from https://issuetracker.google.com/issues/36753410, comment #172)

function clearFilter() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ssId = ss.getId();
    var sheetId = ss.getActiveSheet().getSheetId();
    var requests = [{
        "clearBasicFilter": {
        "sheetId": sheetId
        }
    }];
    Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

---

3 - Hard way: (kept it here because I wrote it before thinking a little more)

1 - Remove first line (or line where filter buttons are)

Sheet.deleteRow(1);

2 - Insert it again :)

Sheet.insertRowBefore(1);

3 - Set its headers

Sheet.getRange("A1").setValue("Hi there");
Sheet.getRange("B1").setValue("Here I am Again with no filter");

//alternatively
var LineVals = Sheet.getRange("1:1").getValues(); 
LineVals[0][0] = "Hi there";
LineVals[0][1] = "Here I am again";
LineVals[0][2] = "With no filters";
Sheet.getRange("1:1").setValues(LineVals);
//getValues is meant to keep the array exactly the size of the row

4 - Set the line color again:

//Cell color                                   
Sheet.getRange("1:1").setBackground('#ff3300');

5 - Set font styles:

 //I didn't test these ones....
 Sheet.getRange("1:1").setFontColor....
 Sheet.getRange("1:1").setFontFamily....     
 Sheet.getRange("1:1").setFontSize....
 //Actually there are a lot of font options available....       

6 - If it was frozen before, freeze it again:

Sheet.setFrozenRows(1);

7 - And finally, if they had NamedRanges, consider naming the entire column instead of a single cell, that will preserve your names unharmed.

这篇关于如何使用Google Apps脚本移除Google电子表格中的所有过滤器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！