使用onEdit-根据单元格的内容隐藏行 [英] Hide Rows Based on Content of a Cell -issue with onEdit-

问题描述

我正在尝试编辑脚本，但是遇到了一些问题:

function onEdit(e) {
  Logger.log('e.value: ' + e.value);

  var cellEdited = e.range.getA1Notation();
  Logger.log('cellEdited: ' + cellEdited);

  if (cellEdited === "A10" && e.value !== "CTR") {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var theSheet = ss.getActiveSheet();

    theSheet.hideRows(12, 2);
  };
   if (cellEdited === "A10" && e.value === "CTR") {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var theSheet = ss.getActiveSheet();

    theSheet.showRows(12, 2);
     }
}

从帖子中可以看到，我的目的是根据单元格A10中的输入隐藏第12行和第13行.第12和第12行仅当A10为点击率"时，13才可见.

我的问题是A10充满了vlookup函数，根据我当前的脚本结果，似乎函数"onedit"不是由vlookup的更改触发的.

所以我希望你们能为我提供2个解决方案之一.
1即使它已由公式更改，也要检查单元格a10.
2.由于单元格B10的个人更改编辑"(B10与B10& C10& E10合并)，因此vlookup发生变化，因此我认为脚本可以更改为对B10进行编辑，但要检查内容A10? /p>

此外，我如何将其指定为不是活动工作表，而是指定给一个特定工作表?

解决方案

查看是否可行(但首先将 Sheetname 替换为您要对该脚本进行处理的工作表/标签的名称).

function onEdit(e) {
var sheet = e.source.getActiveSheet()
if (sheet.getName() !== 'Sheetname' || e.range.getA1Notation() !== 'B10') return;
e.range.offset(0, -1).getValue() === 'CTR' ? sheet.showRows(12, 2) : sheet.hideRows(12, 2)
}

I am trying to edit a script however ran over some issues:

function onEdit(e) {
  Logger.log('e.value: ' + e.value);

  var cellEdited = e.range.getA1Notation();
  Logger.log('cellEdited: ' + cellEdited);

  if (cellEdited === "A10" && e.value !== "CTR") {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var theSheet = ss.getActiveSheet();

    theSheet.hideRows(12, 2);
  };
   if (cellEdited === "A10" && e.value === "CTR") {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var theSheet = ss.getActiveSheet();

    theSheet.showRows(12, 2);
     }
}

As you can see from the post my purpose is to hide the row 12 and 13 based on the input in cell A10. Rows 12& 13 should only be visible if A10 is "CTR"

My problem is that A10 is filled with a vlookup function, and based on my current results with the script it seems that the function "onedit" is not triggered by a change of the vlookup.

So I hope you guys can help me with 1 of 2 solutions.
1 make it check cell a10 even if it changed by the formula.
2. the vlookup is changing to due to a Personal change "edit" of cell B10 (B10 merged with B10&C10&E10) So I reckon the script could be altered to be on edit of B10 but to check the content A10?

In addition how would I specify this not to active sheet but to one specific sheet?

解决方案

See if this works (but first replace Sheetname with the name of the sheet/tab you want the script to work on).

function onEdit(e) {
var sheet = e.source.getActiveSheet()
if (sheet.getName() !== 'Sheetname' || e.range.getA1Notation() !== 'B10') return;
e.range.offset(0, -1).getValue() === 'CTR' ? sheet.showRows(12, 2) : sheet.hideRows(12, 2)
}

这篇关于使用onEdit-根据单元格的内容隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！