Automatically copy and paste cells that have just been updated to a list from forms

Question

I have a spreadsheet that collects information from two different forms. Each form feeds a different sheet in the spreadsheet. There are 3 other sheets that the info from either of the first two forms will be assigned to. I would like to have a couple things happen but it's really the same function for it all.

1. I need it to watch the "incoming" sheets list and copy and paste the rows to the last row on another sheet based on the value of cell in column B (this is the event that should decide when and where to copy and paste the info). Eg: if onEdit col B = Luke then copy and paste row to sheet "Luke"

2. I also need to auto fill a different form with certain cells values if say column k says "Yes".

I currently have a query moving the info to the sheets however I then have to manually copy and paste all the values over so they can be updated on their respective sheets. I am new to scripting and am having trouble tracking down the needed codes and the variables they require. I noticed that when people use onEdit for instance they always set var ss = getActiveSheet is there a place where I can read up on how these variables work and what they require?

Answer 1

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

1. ss = active spreadsheet, sheet = active sheet
2. values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
3. row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
4. values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

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') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Answer 2

This is as far as I got till my boss got mad that I was taking it down now and again. Time for V2, thanks for the help!

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();  
Logger.log(ss.getEditors());  
Logger.log(aSName);  
Logger.log(aRow);  
Logger.log(aCol);  

//////////////////////////////////////////////////////////////////////////////  

function SampleFormUrl(U){  
  var colIndexU = U.range.getColumn();  
  var rowIndexU = U.range.getRow();  
  var daysSince = s.getRange(rowIndexU,25);  
  if( U.value == "Arrived" && colIndexU == sampleCol ){   
    daysSince.clear();   
  }else  
  {  if( U.value == "Send" && colIndexU == sampleCol ){   
    var formlinkCell = s.getRange(rowIndexU,26);   
    var forminfo = s.getRange(rowIndexU,5,1,7);  
    var formSheet = ss.getSheetByName("Data");  
    var formInput = formSheet.getRange(11,2,1,9);  
    forminfo.copyValuesToRange(formSheet, 2, 9, 12, 12);  
    var formUrlRange = formSheet.getRange(6,1);  
    var formUrlCell = formUrlRange.getCell(1,1).getValue();  
    formlinkCell.setValue(formUrlCell);  
    U.range.setValue("Sent");  
    daysSince.setValue(0);  
  }  
}  
}  

//  this code is for generating the URLs for prefilled sample Forms   
/////////////////////////////////////////////////////////////////  
// This is for website lead submission movement handling   

 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"||"Karim"||"Luke"||"James"||"Taryn"||"Wayne"||"Internal")  {  
      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 sheetChk = ss.getSheetByName("Internal");  
        var webSheeChk = ss.getSheetByName("External");  
        if(s.getSheetName() != sheetChk.getSheetName() &&  s.getSheetName() !webSheeChk.getSheetName()){  
          s.deleteRow(rowIndex);  
          var email = Session.getActiveUser().getEmail();  
          Logger.log(email);  
          var scriptProperties = PropertiesService.getScriptProperties();  
          scriptProperties.setProperties({  
            'Luke': 'Luke@com',  
            'James': 'Jamem@com',  
            'Taryn': 'Taryna@com',  
            'Wayne': 'Wayne@com',  
            'Karim': 'Karim'  
          });   
          var Email = scriptProperties.getProperty(nameIt);  
          Logger.log(Email);  
          var message = "Check your leads page you've been assigned a lead!";  
          var subject = "New Lead Assigned to you ! ";  
          GmailApp.sendEmail(Email, subject, message);  
          SpreadsheetApp.flush();  
        }  
      }   
    }   
  }     
}   
// T his 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 = A.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;  
     var row = T.range.getRow();  
  if( row > 2 && Colindex == 2||4||5||6||7||8||9||10||11||12||13||14||15||16||17||18||19||20||21){ //checks   the column  
  >>  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   
///////////////////////////////////////////////////////////////////  
// Email handler