如何删除 Google 电子表格中的所有过滤器? [英] 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 的答案

---

这是可能的，有点作弊":)

(更新答案:有一项新的 Google 服务允许这样做，请参阅第 2 条)

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

1 - 简单的方法

好吧，我设法这样做了:

Well, I managed to do it this way:

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 并启用";表格 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 - 设置字体样式:

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 - 如果之前冷冻过，再冷冻一次:

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 电子表格中的所有过滤器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！