0

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]]);  }  }
  • The expected behaviour isn't clear to me. By the other hand the code has at least a couple of typos: on the onFormsubmit function var = values.sheet.getDataRange().getValues(); should be replaced by var values = sheet.getDataRange().getValues();; on the onEdit function s.getSheetByName == 'Luke' should be replaced by ss.getSheetByName == 'Luke'. Also the onEdit function the row variable isn't defined. See How can I test a trigger function in GAS – Rubén Dec 28 '15 at 15:35
  • 1
    Don't put the solution in the question. Put it in an answer and "accept" it. I've rolled back your change and put your solution in your non-answer answer. – ale Feb 17 '16 at 15:43
1

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 
///////////////////////////////////////////////////////////////////

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.