Combining two onEdit() in single Google Sheets App Script-CodePudding

I am trying to combine two onEdit functions in a single App Script. I have read through other posts on this subject and feel like I have integrated their suggestions but it is still not working.

Function A (timeStamp) places a time stamp in column B when columnn A is edited Function B (arhviveRow) copies the edited row to sheet "Inactive" when a check box is marked True on sheet "Active" and then deletes the row from sheet "Active".

Both functions work correctly when run separately, but only the second function works when combined.

function onEdit(e) {
  timeStamp();
  archiveRow();


//Automatically stamp date and time on line
function timeStamp() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Active" ) { //checks that we're on Sheet1 or not
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === "" ) //checks if the adjacent cell is empty or not?
nextCell.setValue(new Date()   new (Time));
}
}
}


//Archive and delete line
function archiveRow(){
//ColumnNumberToWatch defines which column on sheet to monitor for an edit
    var ss = e.source,
        sheet = ss.getActiveSheet(),
        range = e.range,
        targetSheet,
        columnNumberToWatch = 9; // column A = 1, B = 2, etc
        
// "sheet.getName() ===" defines which sheet to watch for the edit
// "targetSheet =" defines sheet to copy data to
    if (sheet.getName() === "Active" && e.value === "TRUE" && e.range.columnStart === columnNumberToWatch) {
        targetSheet = "Inactive"
    } else if (sheet.getName() === "Inactive" && e.value === "FALSE" && e.range.columnStart === columnNumberToWatch) {
        targetSheet = "Active"
    }
    
 //Copies data to last row in targetSheet and then deletes from source sheet
    ss.getSheetByName(targetSheet)
        .appendRow(sheet.getRange(e.range.rowStart, 1, 1, sheet.getLastColumn())
            .getValues()[0])
    sheet.deleteRow(e.range.rowStart);
}

}

Any insights into where I went wrong would be greatly appreciated.

Thanks!

CodePudding user response：

The new Date() object should already contain the time, and there is no Javascript Object for new Date(Time).

It should look like this:

function timeStamp() {
var s = SpreadsheetApp.getActiveSheet();
Logger.log(s);
  if( s.getName() == "Active" ) { //checks that we're on Sheet1 or not
  var r = s.getActiveCell();
    if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
    var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === "" ) {//checks if the adjacent cell is empty or not?
     
      nextCell.setValue(new Date().toLocaleString());
      }
}
}
}

Sample output for the date: Active Sheet

Once the checkbox was clicked setting the value to TRUE, appends the row to the "Inactive" sheet.

Inactive Sheet

CodePudding user response：

function onEdit(e) {
  const sh = e.range.getSheet();
  const name = sh.getName():
  if (name == "Active" && e.range.columnStart == 1) {
    e.range.offset(0, 1).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
  }
  if (name == 'Active' && e.range.columnStart == 9 && e.value == "TRUE") {
    let tsh = e.source.getSheetByName('Inactive');
    tsh.appendRow(sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).getValues()[0]);
    sh.deleteRow(e.range.rowStart())
  } else if (name = 'Inactive' && e.range.columnStart == 9 && e.value == "FALSE") {
    let tsh = e.source.getSheetByName('Active');
    tsh.appendRow(sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).getValues()[0]);
    sh.deleteRow(e.range.rowStart());
  }
}