2

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?

  • Where to read up: see this question. – user79865 Dec 7 '15 at 6:53
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]]);
  }   
}
  • Awesome Thank you for the reference links and answer Ill dig in and let you know! – Marshalls Best Dec 8 '15 at 3:20
  • I did end up adding a row. the reason i have the rows trimmed is to prevent someone from adding character 500 rows down by accident, then the getLastROw dumps the infor 501 rows down and people come asking where the leads went . i didn't want to tackle that one yet so i delete all unused rows. so that it's more easily seen if that happens again. However i am finding that as i'm using a few different scripts now in this doc so i don't disturb this core that some the set values are inserting them and not taking up that added row which in turn makes for a lot of empty rows again. – Marshalls Best Mar 7 '16 at 3:05
  • the lesson learned have allowed me the options to make what I'm hopiing will be 100 times better with an html daily dash board of todos rather then just another spread sheet. this will be relegated to assignment only and not the daily usage. – Marshalls Best Mar 7 '16 at 3:06
0

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   
  • I have spent hours trying to figure this out to where I called a buddy and he said theres no real easy way. I apprciate the insight and ill giver a go – Marshalls Best Mar 7 '16 at 3:19

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.