title: VBA code to Google Script (Find difference in dates, find average, on Form Submit)
body: <p>I am currently working on creating an app in google sheets from code I wrote in excel VBA. There are 2 sheets, one contains user submitted info. This info is dates and it populates row by row. There are 8 columns of dates (8 entries per row). The other sheet finds the difference in the dates (i.e. days in between), calculates and calculates an average.</p>
  
  <p>I am having trouble with the script language and syntax but here is what I have so far:</p>
  
  <pre><code>    function calcValues() {
  
      //var ss = SpreadsheetApp.getActiveSheet();
     //ss.insertRowAfter(ss.getLastRow());
    //ss.getRange(ss.getLastRow(),1,1,ss.getLastColumn()).copyTo(ss.getRange(ss.getLastRow()+1,1));
  
    //spreadsheet variable declared
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   //FormResponses sheet declared as variable
   var sFR = ss.setActiveSheet(ss.getSheetByName("FormResponses"));
   //Last row on form response sheet
   var last = sFR.getLastRow();
   //Declare calcData sheet 
   var sCD = ss.setActiveSheet(ss.getSheetByName("CalcData"));
  
   //Loop to write values to CalcData sheet
   for( var i = 2; i &lt; last; i++)
   if(sFR.getRange(i,1) !== 0){
    sCD.getRange(3, 1, 1, 8).copyTo(sCD.getRange(i+2,1))
    }
  }
  
  function FormSubmitUpdate() {
     //spreadsheet variable declared
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    //FormResponses sheet declared as variable
    var sFR = ss.setActiveSheet(ss.getSheetByName("FormResponses"));
    //Last row on form response sheet
    var last = sFR.getLastRow();
    //Declare calcData sheet 
    var sCD = ss.setActiveSheet(ss.getSheetByName("CalcData"));
  
    //sCD.getRange("B4").setValue(sFR.getRange("C3").getValue()-    sFR.getRange("B3").getValue())
    //sCD.insertRowAfter(sCD.getLastRow());
    sCD.getRange(sCD.getLastRow(),1,1,sCD.getLastColumn()).copyTo(sCD.getRange(sCD.getLastRow()+1,1));
  </code></pre>
  
  <p>I currently have the formulas set in the cells and this code simply copies the formula down. calcValues counts how many user inputs there are and performs that many copies.</p>
  
  <p>formSubmitUpdate copies the previous row down one row.</p>
  
  <p>When I attempt to perform the subtraction of dates in the code I get a really strange number. I did some research and that is because of the millisecond date transformation.</p>
  
  <p>I would like the subtraction and average to be found using code and not formulas in the sheet. I would also like it to update when a new userform is submitted.</p>
  
  <p>If you can give me some code samples that will do what I am asking I would much appreciate it. I am a VBA guy and I would really like to learn this platform. </p>
  
  <p>Thank you.</p>