CODEWITHSUNDEEP
google-sheetsspreadsheet
Jayson Roque
Jayson Roque

Reputation: 15

Auto hide/unhide rows Google Spreadsheet

I'm trying to make a google spreadsheet script.

When the value of a cell changes to a specified value I want it to trigger hide/unhide rows.

Here is my script so far:

function autoHide() {

  var ss = SpreadsheetApp.getActive()
  var sheet = SpreadsheetApp.getActiveSheet()
  var cell = ss.getActiveCell()
  var cell1 = ("C12");

  if (cell1 == "Others" ) {
    ss.getActiveSheet().showRows(14, 3);
  }

  else if (cell1 == "Additional Discount/s (over and above 25%)" ) {
    ss.getActiveSheet().showRows(17, 4);
  }

  else {
    ss.getActiveSheet().hideRows(14, 7)
  }
}

Image of sheet

Upvotes: 0

Views: 1048

Answers (1)

CalamitousCode
CalamitousCode

Reputation: 1414

Your question is a little unclear about some points so I assumed you wanted to track the value of cell C12 when C12 is edited.

function autoHide() {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell();
  var value = cell.getValue();

  var cellRef = cell.getA1Notation();

  if (cellRef !== "C12") return;
  // This isn't the cell we are looking for, stop executing the function.

  if (value === "Others" ) {

    sheet.showRows(14, 3);

  } else if (value === "Additional Discount/s (over and above 25%)" ) {

    sheet.showRows(17, 4);

  } else {
    sheet.hideRows(14, 7);
  }
}

If you want to run the function anytime any cell is edited, use this code.

Useful if C12 is a formula and is not updated manually.

function autoHide() {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();

  var cellRef = "C12";
  var cell = sheet.getRange(cellRef);
  var value = cell.getValue();

  if (value === "Others" ) {

    sheet.showRows(14, 3);

  } else if (value === "Additional Discount/s (over and above 25%)" ) {

    sheet.showRows(17, 4);

  } else {
    sheet.hideRows(14, 7);
  }
}

Upvotes: 1

Related Questions