Hide Rows Based on Content of a Cell -issue with o

问题:

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?

回答1:

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)
}