Google Sheets – Why Doesn’t My onEdit Function Behave the Same as My onFormSubmit

copy/pastegoogle sheetsgoogle-apps-script

With the excellent help of this site I was able to get my onFormSubmit working.
However I need the same thing to happen on another sheet only it has to happen after a cell has been assigned one of two (potentially more) names.

I have a dummy Form feeding a dummy Spreadsheet with 3 sheets, those being "Luke" "Luke2" "James".

With onFormSubmit I have it working that if the responses contain "Luke" in Column B then the info is copied over to sheet "Luke2". This works great, but I want to be able to change the responses in column D to "James" and have the same set of responses copied over to the sheet names James. As you can imagine I'm trying to have work assigned after information is submitted.

I've tried various code that I've found here but none of it seems to take so I'm leaving it here:

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();

  if (values[row-1][1] == 'Luke') {  
    var targetSheet = ss.getSheetByName('Luke2');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);      
    targetRange.setValues([values[row-1]]);
  }   
}

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if(values[row-1][3] == 'James' && s.getSheetName == 'Luke') {
    var targetSheet = ss.getSheetByName('James');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);   
    targetRange.setValues([values[row-1]]);
  }
}

To summarise: the top half works it's the onEdit that doesn't work.
The if statement is: 

if(values[row-1][3] == 'James' && s.getSheetName == 'Luke') {  then  targetRange.setValues([values[row-1]]);  }  }

Best Answer

After spending every waking moment working on this and now much more since I have answered this question and I m sorry it took a while to update. here's where I have left off. it's clunky but functional:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var r = s.getActiveRange();
var c = s.getActiveCell();
var aRow = r.getRow();
var aCol = r.getColumn();
var sampleCol = 17;
var nameCol = 3;
var statusCol = 2;
var aSName = s.getName();


  function websiteSubmit(W){
    if( s.getName() == "External"){ 
    Logger.log("External")
    Logger.log(s.getName());
    var rowIndexW = W.range.getRow();
    var nameIt = "WebForm";
    Logger.log(nameIt);
    var leadRange = s.getRange(rowIndexW,1,1,24);
    var targetPage = ss.getSheetByName(nameIt);
       Logger.log(targetPage);
    var lastRow = targetPage.getLastRow();
       targetPage.insertRowAfter(lastRow);
    leadRange.copyValuesToRange(targetPage,1,24,lastRow+1,lastRow+1);
    var setRow = targetPage.getLastRow();
    targetPage.getRange(setRow,statusCol).setValue("Even");
    targetPage.getRange(setRow,4).setValue("LEJ WebForm");
  }else
       if(aSName == "Internal"){
    var nameIt = s.getRange(aRow,nameCol).getValue();
    Logger.log(nameIt);
    var leadRange = s.getRange(aRow,1,1,24);
    var targetPage = ss.getSheetByName(nameIt);
    var lastRow = targetPage.getLastRow();
    targetPage.insertRowAfter(lastRow);
    leadRange.copyValuesToRange(targetPage,1,24,lastRow+1,lastRow+1);
    }
  }

// This is for website lead submission movement handling 
////////////////////////////////////////////////////////////////
// This is the code for moving the info between lead sheets.

function leadProcessing(P){
  if(nameCol == P.range.getColumnIndex()){
   Logger.log("Lead Processing");
    var rowIndex = P.range.getRowIndex();
    var nameIt = s.getRange(rowIndex,nameCol).getValue();
    if (nameIt == "Open"||"Luke"||"James"){
      Logger.log(nameIt);
      var leadRange = s.getRange(rowIndex,1,1,24);
      var targetPage = ss.getSheetByName(nameIt);
      var lastRow = targetPage.getLastRow();
      if(ss.getSheetName() != targetPage.getSheetName()){
        targetPage.insertRowAfter(lastRow);
        leadRange.copyValuesToRange(targetPage,1,24,lastRow+1,lastRow+1); 
        var shetChk = ss.getSheetByName("Internal");
        var webSheChk = ss.getSheetByName("External");
        if(s.getSheetName() != shetChk.getSheetName() && s.getSheetName() != webSheChk.getSheetName()){
          s.deleteRow(rowIndex);
          Logger.log(shetChk.getSheetName());
          Logger.log(webSheChk.getSheetName());
          Logger.log(s.getSheetName());
          Logger.log(targetPage.getSheetName());
        }
      }
    }
  }
}
// This is the code for moving the info between lead sheets.
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
// This is for Assigning Closed Accounts to the Accounts Manager

function leadAssignment(A){

  if(statusCol == A.range.getColumn()&& A.value == "Assign"){
      Logger.log("LeadAssignment")
    var rowIndex = A.range.getRow();
    var nameIt = s.getRange(rowIndex,nameCol).getValue();
    var leadRange = s.getRange(rowIndex,1,1,24);
    var targetPage = ss.getSheetByName(nameIt+"s Acct");
    var lastRow = targetPage.getLastRow();
    targetPage.insertRowAfter(lastRow);
    leadRange.copyValuesToRange(targetPage,1,24,lastRow+1,lastRow+1); 
    if(s.getSheetName() != targetPage.getSheetName()){
      s.deleteRow(rowIndex);
    }
  }
}
// This is for Assigning Closed Accounts to the Accounts Manager
////////////////////////////////////////////////////////////////
// This moves product to territory page

function leadConflict(C){
  if(statusCol ==  s.getActiveRange().getColumn()){
    Logger.log("Lead Conflict");
    var rowIndex = s.getActiveRange().getRow();
    var nameIt = s.getRange(rowIndex,statusCol).getValue();
    var leadRange = s.getRange(rowIndex,1,1,24);
    var targetPage = ss.getSheetByName(nameIt);
    var lastRow = targetPage.getLastRow();
    if(s.getSheetName() != targetPage.getSheetName()){
      targetPage.insertRowAfter(lastRow);
      leadRange.copyValuesToRange(targetPage,1,24,lastRow+1,lastRow+1);
      s.deleteRow(rowIndex);
    }
  }
}

// This is for Assigning Closed Accounts to the Accounts Manager
////////////////////////////////////////////////////////////////
// This is for Applying a tiume stamp for edit to specific columns 

function timeStamp(T){
  var Colindex = aCol;
  if( Colindex == 2||4||5||6||7||8||9||10||11||12||13||14||15||16||17||18||19||20||21){ //checks the column
    var row = T.range.getRow();
    var time = new Date();
    ss.getRange('W' + row.toString()).setValue(time);
    ss.getRange('X' + row.toString()).setValue(0);
  }
}

// This is for Applying a tiume stamp for edit to specific columns 
///////////////////////////////////////////////////////////////////
Related Topic