符合条件时隐藏行 [英] Hide Rows when a criteria is met
本文介绍了符合条件时隐藏行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下面的脚本,该脚本将在"S:S"范围内搜索"Dead"或"Booked",并在找到该行时将其隐藏.如果在隐藏的行中找到实时",则应取消隐藏该行. 下面的脚本正在运行,但是它将取消隐藏所有行,然后完成该功能,当搜索超过1000行时可能需要一段时间.
I have the below script that is to search for 'Dead' or 'Booked' in range "S:S" and hide the row if it is found. If 'Live' is found in a hidden row it should unhide the Row. The below script is working however, it unhides all rows and then completes the function, which can take a while when over 1000 rows are being searched.
function RowHide(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht2 = ss.getSheetByName("Log");
var row = sht2.getRange("S:S").getDisplayValues();
sht2.showRows(1, sht2.getMaxRows()); {
for (var i = 0; i < row.length; i++) {
if (row[i][0] == 'Dead') {
sht2.hideRows(i + 1, 1);
}
if (row[i][0] == 'Booked') {
sht2.hideRows(i + 1, 1);
} else if (row[i][0] == 'Live') {
sht2.showRows(i + 1, 1);
}
}
}
}
我需要将脚本更改为仅需要操作的操作行,而不是全部.这可能吗?
I need to alter the script to only action rows which require action rather than all of them. Is this possible?
推荐答案
使用onEdit触发器隐藏行
function rowHideProbablyOnEdit(e){
var ss=e.source;
var rg=e.range;
var sh=rg.getSheet();
if(sh.getName()=="Log"){
var datR=sh.getDataRange();
var valA=datR.getValues();
for(var i=0;i<valA.length;i++){
if(valA[i][18]=='Dead' || valA[i][18]=='Booked'){
sh.hideRows(i+1);
}
}
}
}
使用onEdit触发器隐藏和显示
function rowShowHideProbablyOnEdit(e){
var ss=e.source;
var rg=e.range;
var sh=rg.getSheet();
if(sh.getName()=="Log"){
var datR=sh.getDataRange();
var valA=datR.getValues();
for(var i=0;i<valA.length;i++){
if(valA[i][18]=='Dead' || valA[i][18]=='Booked'){
sh.hideRows(i+1);
}
if(valA[i][18]=='Live'){
Logger.log('Sheet: %s',sh.getName());
sh.showRows(i+1);
}
}
}
}
设置和测试功能:
Setup and Testing Functions:
function rowLiveSetup(){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Log');
var rg=sh.getDataRange();
var vA=rg.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][18]=='Booked'){
vA[i][18]='Live';
}
}
rg.setValues(vA);
}
function rowBookedDeadSetup(){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Log');
var rg=sh.getRange(1,1,180,20);
var vA=rg.getValues();
for(var i=0;i<180;i++){
if(i%3==0){
vA[i][18]='Booked';
}
if(i%3==1){
vA[i][18]='Dead';
}
}
rg.setValues(vA);
}
查看全文