Combine two Macros into one Google Sheet-CodePudding

I have sheet where i want to combine two Macro scripts so they both work on same sheet. One macro is to create blank row after data input. Other script is to lock whole row after same data input. Blank row script:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var editedrow = range.getRow();
  var editedvalue = e.value;
  
  if ( editedrow === 3 && editedvalue === "TRUE"){
    // the checkbox on row#2 was ticked.
    sheet.insertRowBefore(3);
    
  }
  return;
}

Lock row script:

function onEdit(e){
  const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (e.range.columnStart != 4) return;
  let protection = sh.getRange(e.range.rowStart,1,1,sh.getMaxColumns()).protect();
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Hope somebody can help.. Thank You!

CodePudding user response：

About 2 conditions of editedrow === 3 && editedvalue === "TRUE" and e.range.columnStart == 4, from your question, I couldn't understand your expected condition. So, in this answer, I would like to propose the following 3 patterns.

Pattern 1:

In this pattern, when the condition is e.range.columnStart == 4, the row is protected. And, when the condition is editedrow === 3 && editedvalue === "TRUE", a new row is inserted.

function onEdit(e) {
  const { range, value, source } = e;
  const sh = source.getActiveSheet();
  if (range.columnStart == 4) {
    let protection = sh.getRange(range.rowStart, 1, 1, sh.getMaxColumns()).protect();
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  }
  if (range.rowStart === 3 && value === "TRUE") {
    sh.insertRowBefore(3);
  }
}

In this case, when column "D" is edited. The row is protected. And, when "TRUE" is put to row 3, a new row is inserted.

Pattern 2:

In this pattern, when the condition is e.range.columnStart == 4, the row is protected. And, when the condition is editedrow === 3 && editedvalue === "TRUE", a new row is inserted.

function onEdit(e) {
  const { range, value, source } = e;
  const sh = source.getActiveSheet();
  if (range.columnStart == 4 && !(range.rowStart === 3 && value === "TRUE")) {
    let protection = sh.getRange(range.rowStart, 1, 1, sh.getMaxColumns()).protect();
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  }
  if (range.columnStart != 4 && range.rowStart === 3 && value === "TRUE") {
    sh.insertRowBefore(3);
  }
}

In this case, when column "D3" is "TRUE". No script is run. When column "D" is edited, the row is protected. And, when "TRUE" is put to the row 3 except for "D3", a new row is inserted.

Pattern 3:

In this pattern, when the condition is e.range.columnStart == 4 && editedrow === 3 && editedvalue === "TRUE", the row is protected, and a new row is inserted.

function onEdit(e) {
  const { range, value, source } = e;
  if (range.columnStart == 4 && range.rowStart === 3 && value === "TRUE") {
    const sh = source.getActiveSheet();
    let protection = sh.getRange(range.rowStart, 1, 1, sh.getMaxColumns()).protect();
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
    sh.insertRowBefore(3);
  }
}

In this case, when "TRUE" is put to the column "D3", the row is protected and a new row is inserted.
In this case, if you want to run the script range.columnStart == 4 or range.rowStart === 3 && value === "TRUE", please modify range.columnStart == 4 && range.rowStart === 3 && value === "TRUE" to range.columnStart == 4 || (range.rowStart === 3 && value === "TRUE").

CodePudding user response：

Try this:

function onEdit(e) {
  var sh = e.range.getSheet();  
  if ( e.range.rowStart == 3 && e.value == "TRUE"){
    sh.insertRowBefore(3);
  }
  if (e.range.columnStart == 4)
  let protection = sh.getRange(e.range.rowStart,1,1,sh.getMaxColumns()).protect();
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}