问用于清除/删除过滤器的Google Sheets脚本
EN

Stack Overflow用户

提问于 2016-07-08 15:14:14

回答 0查看 1.9K关注 0票数 1

我在谷歌工作表中有一个脚本，在打开时清除/删除工作表中所有选项卡上的过滤器。它通过抓取第一行，然后删除它，然后在没有过滤器的情况下替换它来实现这一点。

该脚本可以工作，但是，它似乎太多的工作表处理的6个标签。当它运行时，它会留下2-3个完全灰色的选项卡，没有显示任何数据。当您双击一个灰色单元格时，它会返回以下消息：“这些单元格当前正在加载中。请在加载完成后重试”。无论我等多长时间，数据都不会显示。如果我再刷新页面3到4次，所有选项卡最终都会显示数据。

我希望要么清理代码，要么想出一个新的脚本，这样就不必刷新页面。我在一个只有两个选项卡的工作表上使用了这个脚本，它工作得很好，所以我不认为它与数据量有关。

警告:我是一个极端的初学者，所以我确信代码和格式是相当丑陋的。

以下是工作表副本的链接：

https://docs.google.com/spreadsheets/d/1cg4wVIHPumahQgf5gorc4JQXsG_6l4eg3OhONABx8gs/edit#gid=369791415

下面是该脚本的代码(它是每个选项卡的重复代码块)：

function myFunction() 
{
var row2 = 1 //the row with filter 
var rowBefore2 = row2 
var Sheet2 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet2.getSheets()[1]);

Sheet2.insertRowBefore(row2); //inserts a line before the filter
row2++;

var Line2 = Sheet2.getRange(row2 + ":" + row2); //gets the filter line
Line2.moveTo(Sheet2.getRange(rowBefore2 + ":" + rowBefore2)); //move to
new line
Sheet2.deleteRow(row2); //deletes the filter line - this clears the 
filter

var row3 = 1
var rowBefore3 = row3 
var Sheet3 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet3.getSheets()[2]);

Sheet3.insertRowBefore(row3);
row3++;

var Line3 = Sheet3.getRange(row3 + ":" + row3);
Line3.moveTo(Sheet3.getRange(rowBefore3 + ":" + rowBefore3));
Sheet3.deleteRow(row3);

var row4 = 1 //the row with filter 
var rowBefore4 = row4 
var Sheet4 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet4.getSheets()[3]);

Sheet4.insertRowBefore(row4);
row4++;

var Line4 = Sheet4.getRange(row4 + ":" + row4);
Line4.moveTo(Sheet4.getRange(rowBefore4 + ":" + rowBefore4));
Sheet4.deleteRow(row4);

var row5 = 1 //the row with filter 
var rowBefore5 = row5 
var Sheet5 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet5.getSheets()[4]);

Sheet5.insertRowBefore(row5);
row5++;

var Line5 = Sheet5.getRange(row5 + ":" + row5);
Line5.moveTo(Sheet5.getRange(rowBefore5 + ":" + rowBefore5));
Sheet5.deleteRow(row5);

var row6 = 1
var rowBefore6 = row6 
var Sheet6 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet6.getSheets()[5]);

Sheet6.insertRowBefore(row6);
row6++;

var Line6 = Sheet6.getRange(row6 + ":" + row6);
Line6.moveTo(Sheet6.getRange(rowBefore6 + ":" + rowBefore6));

Sheet6.deleteRow(row6);

var row7 = 1 
var rowBefore7 = row7 
var Sheet7 = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(Sheet7.getSheets()[6]);

Sheet7.insertRowBefore(row7);
row7++;

var Line7 = Sheet7.getRange(row7 + ":" + row7);
Line7.moveTo(Sheet7.getRange(rowBefore7 + ":" + rowBefore7));

Sheet7.deleteRow(row7);