过滤或隐藏行 [英] Filter Or Hide Row
问题描述
我有一张谷歌表格，我用它将电子邮件发送给分包商。每个分包商都有自己的工作表，并且我还有一个名为MasterSchedule的工作表。
主计划使用对所有单独工作表的引用，以便所有分包商计划都可见。尽管如此，这是很重要的。因此，我在所有表格中都包含了一个帮助器列，它们只是简单地返回 true 或 false ，指示是否应在主版面上显示某行。
然而，该行仍然显示在主文件夹上，但帮助器列恰好会显示为false。所以我使用AutoFilter来隐藏它。
$ b
TLDR：
问题： Google Apps脚本没有自动过滤器的API。就像使用标准的VBA一样。所以我看到的唯一选择是隐藏行。但这非常缓慢。我知道这个想法是减少对Google服务的调用数量，Google建议制作一个数组，然后在数组上调用一个调用。我不知道如何做到这一点。
我需要一个高效的脚本/函数来查看一列和 false 的每个单元格，该功能将隐藏整行，并显示所有其他行。
最快的非脚本方法是使用Google Sheets版本的自动过滤器，并简单地取消选中 false 。
我试着做一个for循环来读取列中的每个单元格，并且如果单元格值为false，则每次迭代隐藏该行。
<$ c $ c> function MasterFilter（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = ss.getActiveSheet（）;
var maxRows = sheet.getMaxRows（）;
//显示所有行
sheet.showRows（1，maxRows）;
//从列B
获取数据var data = sheet.getRange（'B：B'）。getValues（）;
//迭代所有行
for（var i = 5; i< data.length; i ++）{
if（sheet.getRange（i，2）.getValue （）== false）{
sheet.hideRow（i）;
我必须更改原始代码的一行才能使其工作：
sheet.hideRow（sheet .getRange（I，2））; //在一个范围内运行
这样，我就在测试电子表格上计时了~200行一个均匀分布 true / false 个单元格。执行副本报告 [4.656秒总运行时间] 。
如您所怀疑的，您可以通过减少服务调用使用带有一组数据的数组（在这种情况下，是列B的全部）。下面是使用数组测试可见性的hide方法：
$ p $ function MasterFilter（）{
var headers = 4; //＃行跳过
var sheet = SpreadsheetApp.getActiveSheet（）;
var maxRows = sheet.getMaxRows（）;
//显示所有行
sheet.showRows（1，maxRows）;
//从列B
获取数据var data = sheet.getRange（'B：B'）。getValues（）;
//遍历所有行
for（var i = headers; i< data.length; i ++）{
if（data [i] [0] == false ）{
sheet.hideRow（sheet.getRange（i + 1,1））;
code
$ b根据执行记录，对于测试表，这花了 [总运行时间0.106秒] 。看着屏幕，它在刷新的时候看起来像是3到4秒。
这是另一种方法。在这一个中，我们读取主表中的所有数据，然后使用 Array.filter（） 将二维数组缩减为只有 true的行在列B中。在清除所有先前的内容之后，将这个较小的集合写入主表。根据执行转录本，执行时间减半， [总运行时间0.059秒] 。
// Array的评估函数.filter（）
函数isVisible（row）{
return（row [1] === true）; //检查列B
}
函数MasterFilter2（）{
var headers = 4; //＃行跳过
var sheet = SpreadsheetApp.getActiveSheet（）;
var data = sheet.getDataRange（）。getValues（）;
var headerData = data.splice（0，headers）; //跳过标题行
var filteredData = data.filter（isVisible）;
var outputData = headerData.concat（filteredData）; //将标题放回
sheet.clearContents（）; //清除内容，保留格式
//保存已过滤的值
sheet.getRange（1，1，outputData.length，outputData [0] .length）.setValues（outputData）;
}
[Back story] I have a Google Sheet I use for emailing schedules to subcontractors. Each subcontractor has their own sheet, and I also have one last sheet called MasterSchedule.
The master schedule uses references to all the individual sheets such that all subcontractor schedules are visible. This is reaaaaaal long though. So I included a helper column in all sheets that simply returns true or false indicating whether a row should be displayed or not on the master.
However the row is still displayed on the master, but the helper column just happens to say false. So I used the AutoFilter to Hide it.
TLDR:
Problem: Google Apps Script doesn't have API for the auto-Filter. Like VBA using criteria etc. So The only option I see is to hide rows. But this is very slow. I know the idea is to reduce the number of calls to the Google services, and Google suggests making an array then excuting a call on the array. I have no idea how to do this.
I need an efficient script/function to look at a column and every cell that reads false, the function will hide the entire row, and show all other rows.
The fastest non script method is to use Google Sheets version of auto-filter and simply un-check false.
I tried making a for loop that reads each cell in a column and per iteration hides the row if the cell value is false. It is incredibly slow.
See:
function MasterFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var maxRows = sheet.getMaxRows();
//show all the rows
sheet.showRows(1, maxRows);
//get data from column B
var data = sheet.getRange('B:B').getValues();
//iterate over all rows
for(var i=5; i< data.length; i++){
if(sheet.getRange(i,2).getValue() == false){
sheet.hideRow(i);
}
}
}
I had to change one line of your original code, to get it to work:
sheet.hideRow(sheet.getRange(i,2)); // Operates on a range
With that, I timed it on a test spreadsheet, ~200 rows with an even distribution of true / false cells. The execution transcript reported [4.656 seconds total runtime].
As you suspected, you can reduce the Service calls by using an array with a set of data (in this case, all of column B). Here's the "hide" method, using an array for testing visibility:
function MasterFilter() {
var headers = 4; // # rows to skip
var sheet = SpreadsheetApp.getActiveSheet();
var maxRows = sheet.getMaxRows();
//show all the rows
sheet.showRows(1, maxRows);
//get data from column B
var data = sheet.getRange('B:B').getValues();
//iterate over all rows
for(var i=headers; i< data.length; i++){
if(data[i][0] == false){
sheet.hideRow(sheet.getRange(i+1,1));
}
}
}
According to the execution transcript, this took [0.106 seconds total runtime] for the test sheet. Watching the screen, it looked like 3 or 4 seconds by the time it refreshed.
Here's another way to do it. In this one, we read all the data on the Master Sheet, then use Array.filter() to reduce the two-dimensional array to just the rows with true in column B. This smaller set is then written to the Master Sheet, after we clear all previous contents. This cut the execution time in half, [0.059 seconds total runtime] according to the execution transcript. Once again, delays in the refresh of the viewed copy made it look like a couple of seconds.
// Evaluation function for Array.filter()
function isVisible(row) {
return (row[1] === true); // Check column B
}
function MasterFilter2() {
var headers = 4; // # rows to skip
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var headerData = data.splice(0,headers); // Skip header rows
var filteredData = data.filter( isVisible );
var outputData = headerData.concat(filteredData); // Put headers back
sheet.clearContents(); // Clear content, keep format
// Save filtered values
sheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
}
这篇关于过滤或隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
