Google Script OnEdit based on cell value changed in a specified range

Any amount of help would be appreciated. I'm trying to figure out why the below onEdit() function isn't executing.

I have a sheet called "Steps" with UI completed through drop down menus. Anytime a dropdown menu is selected, if a specific topic is chosen (Trenching, Chemical Use, Fall Protection etc...) a sidebar will populate with an embeded Google form. I've got everything completed (sidebar functions, UI from form is recorded properly etc...), I just can't figure out why the OnEdit isn't triggering, or if it is why it's not triggering properly. Can anyone assist? Thanks.

function onEdit(e) {

var editRange = { // G13:K136
  top : 13,     //first row to check
  bottom : 136, //last row to check
  left : 7,     //first column to check (G)
  right : 11    // last column to check (K)
};

var myRow = e.range.getRow();
if (myRow >= editRange.top || myRow <= editRange.bottom) return;

var myCol = e.range.getColumn();
if (myCol >= editRange.left || myCol <= editRange.right) return;

if(e.Value == "Trenching") 
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Trenching.html").setTitle("Trenching / Excavation"));}
  else if (e.Value == "Pressurized Lines")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pressure.html").setTitle("Pressure Testing"));}
  else if (e.Value == "Chemical Use")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Chemical.html").setTitle("Chemical Use"));}
  else if (e.Value == "Fall Protection")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Falls.html").setTitle("Fall Protection"));}
  else if (e.Value == "Confined Space")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Confined.html").setTitle("Confined Spaces"));}
  else if (e.Value == "Lockout-Tagout")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("LOTO.html").setTitle("Lockout-Tagout"));}
  else if (e.Value == "Rigging")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Rig.html").setTitle("Rigging"));}
  else if (e.Value == "Heavy Machinery")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Heavy.html").setTitle("Heavy Machinery"));}
  else if (e.Value == "Hot Work")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Hot.html").setTitle("Hot Work"));}
  else if (e.Value == "Pipe Connections")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pipe.html").setTitle("Pipe Connections"));}
}

CodePudding user response：

I think your logic is reversed.

var myRow = e.range.getRow();
if (myRow < editRange.top || myRow > editRange.bottom) return;

var myCol = e.range.getColumn();
if (myCol < editRange.left || myCol > editRange.right) return;

CodePudding user response：

Should run within this range: G13:K136

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Your Sheet Name" && e.range.rowStart > 12 && e.range.rowStart < 137 && e.range.columnStart > 6 && e.range.columnStart < 11 && e.value) {
    const nA = ["Trenching", "Pressurized Lines", "Chemical Use""Fall Protection", "Confined Space", "Lockout-Tagout", "Rigging", "Heavy Machinery", "Hot Work", "Pipe Connections"];
    const nB = ["Trenching.html", "Pressure.html", "Chemical.html", "Falls.html", "Confined.html", "LOTO.html", "Rig.html", "Heavy.html", "Hot.html", "Pipe.html"];
    const nC = ["Trenching / Excavation", "Pressure Testing", "Chemical Use", "Fall Protection", "Confined Spaces", "Lockout-Tagout", "Rigging", "Heavy Machinery", "Pipe Connections"];
    const idx = nA.indexOf(e.value);
    if (~idx) {
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile(nB[idx]).setTitle(nC[idx]))
    }
  }
}

It would have been great to get those arrays from a spreadsheet and it would have been easier for you to manage them.

CodePudding user response：

To anyone who comes across this post. Here's what I SHOULD have put. I should have pulled the cell value e.range.getvalues() and defined it "cellContent" before running it through the If functions.

function onEdits(e) {

var editRange = { // G13:K136
  top : 13,     //first row to check
  bottom : 136, //last row to check
  left : 7,     //first column to check (G)
  right : 11    // last column to check (K)
};

var myRow = e.range.getRow();
if (myRow < editRange.top || myRow > editRange.bottom) return;

var myCol = e.range.getColumn();
if (myCol < editRange.left || myCol > editRange.right) return;

var cellContent = e.range.getValues()

if(cellContent == "Trenching") 
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Trenching.html").setTitle("Trenching / Excavation"));}
  else if (cellContent == "Pressurized Lines")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pressure.html").setTitle("Pressure Testing"));}
  else if (cellContent == "Chemical Use")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Chemical.html").setTitle("Chemical Use"));}
  else if (cellContent == "Fall Protection")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Falls.html").setTitle("Fall Protection"));}
  else if (cellContent == "Confined Space")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Confined.html").setTitle("Confined Spaces"));}
  else if (cellContent == "Lockout-Tagout")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("LOTO.html").setTitle("Lockout-Tagout"));}
  else if (cellContent == "Rigging")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Rig.html").setTitle("Rigging"));}
  else if (cellContent == "Heavy Machinery")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Heavy.html").setTitle("Heavy Machinery"));}
  else if (cellContent == "Hot Work")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Hot.html").setTitle("Hot Work"));}
  else if (cellContent == "Pipe Connections")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pipe.html").setTitle("Pipe Connections"));}
}