如何删除Google Spreadsheet中的所有过滤器? [英] How can I remove all filters in a Google Spreadsheet?

问题描述

我想编写一个脚本，每天删除一次电子表格中的所有过滤器.

I want to write a script that removes all filters in a spreadsheet once a day.

关于如何处理Google Apps脚本中的过滤，我找不到任何好的文档.

I haven't been able to find any good documentation on how to handle filtering in Google Apps Script.

_{如果可能的话，我也愿意使用Python API进行此操作.}

推荐答案

此答案已过时，请参阅@Davide的答案

---

有可能，只需稍作作弊"即可:)

This answer is outdated, please see @Davide's answer

---

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

(更新后的答案:有一项新的Google服务允许使用该服务，请参见编号2)

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

1-简单的方法

好吧，我设法做到了:

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

//insert a row before the filters
Sheet.insertRowBefore(row);
row++; 

//move the filter row to the new row (this will move only content)
var Line = Sheet.getRange(row + ":" + row); 
Line.moveTo(Sheet.getRange(rowBefore + ":" + rowBefore)); 

//delete the old row, which contains the filters - this clears the filters      
Sheet.deleteRow(row); 

//if the row was frozen before deletion, freeze the new row    
Sheet.setFrozenRows(rowBefore); 

---

2-使用表格服务:

(复制自 https://issuetracker.google.com/issues/36753410 ，评论#172)

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

(摘自下面的@AndreLung的评论)

(Copied from @AndreLung's comment below)

转到资源"，然后高级Google服务"，找到"Google Sheets API"并启用.另外，请转到 console.cloud.google.com/apis/dashboard 并启用"; Sheets API"

Go to Resources, then "Advanced Google Services", locate "Google Sheets API and enable. also, go to console.cloud.google.com/apis/dashboard and enable "Sheets API"

---

3-艰难的方式:(在这里保留它是因为我在想得更多之前就写了它)

---

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

1-删除第一行(或过滤按钮所在的行)

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

Sheet.deleteRow(1);

2-再次插入:)

Sheet.insertRowBefore(1);

3-设置其标题

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-再次设置线条颜色:

4 - Set the line color again:

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

5-设置字体样式:

 //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-如果之前已冻结，请再次冻结:

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

Sheet.setFrozenRows(1);

7-最后，如果他们有NamedRanges，请考虑命名整个列而不是单个单元格，这样可以使您的名称不受损害.

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

这篇关于如何删除Google Spreadsheet中的所有过滤器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！