Comparing data and writing cell data based on results

Question

I am relatively new to google apps script and am slowly working my way through building a script. Please, if you give an answer, try to keep the answer as simple as possible so I can understand what is going on. What I am trying to do.

1. Our administrators have to complete 8 steps to an evaluation process for every teacher in the building and I am trying to build a spreadsheet that will keep track of it based on data submitted via a google form.

2. I Copy first five columns of last row of submitted data on Form responses sheet to tracker sheet. (May not have to be done after I figure this all out.) Data includes timestamp, lastname, firstname, date, stage.

3. This is where it gets complicated. I want to compare the lastname of the imported data to the lastname of the tracker sheet. If the lastname is not there, add it to the end of the list and put the date in the row as the corresponding matching stage. If the lastname is there, put the date in the same row corresponding to the matching stage.

4. Looking at the example below, the lastname pulled in is Smith. Since Smith is in the list, put the date 1/1/2014 in the walkthrough2 column. As administrators continue the evaluation process, there will be more teachers added to the list and it will grow to the right populating the dates in which that part of the evaluation was done.

Data in Tracker sheet

Administrator     Teacher     PreEval     Walkthrough1     Walkthrough2

                  Brown       10/2/2013   10/15/2013       11/15/2103

                  Smith       10/2/2013   10/16/2013    
                  Korytoski   10/5/2013   10/17/2013    

                  Reddick     10/15/2013  10/17/2013    

DATA Pulled from Form Responses spreadsheet

11/10/2013 20:08:34    Smith     Kim    1/1/2014     Walkthrough2

Code

function copyLastRowtoTracker() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = SpreadsheetApp.getActiveSheet();
var lastRow = sh.getLastRow() 
var lastCol = sh.getLastColumn();
var name = sh.getRange(lastRow,1,1,5); 

var tracker = ss.getSheetByName('Tracker');
ss.setActiveSheet(ss.getSheets()[2]);
var sht = SpreadsheetApp.getActiveSheet();
var lastRowTracker = tracker.getLastRow() 

name.copyValuesToRange(tracker,1,5,lastRowTracker+3,lastRowTracker+3);
var walkname = sht.getRange(lastRowTracker + 3, 2).getValue();
var date = sht.getRange(lastRowTracker + 3, 4).getValue(); 
var stage = sht.getRange(lastRowTracker + 3, 5).getValue(); 

var teachernamesdata = tracker.getRange(2, 2, lastRowTracker, 1).getValues();

for(var i = 0; i < teachernamesdata.length; i++){
var teachname = teachernamesdata[i];

if(teachname == walkname) then copy date to cell in same row as teachname with         
corresponding stage

if(teachname != walkname) then add walkname to end of list and copy date to the cell in     
the same row with corresponding stage

Remember that I am new to this when you post the code responses. I know some of things I have done are probably inefficient and not correct, but I am in the learning process.

Answer 1

After many hours of trial and error, I have been able to get it to work when the names are prepopulated and cleaned up the code a little bit. Now I have to figure out how to add a name to the end of the list if it is a new name which should be easy compared to figuring the other stuff out.

New Code

function copyLastRowtoTracker() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses");
var lastRow = ss.getLastRow(); 
var name = ss.getRange(lastRow,1,1,5); 

var sstracker = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracker");
var sstrackerLastRow = sstracker.getLastRow() 

name.copyValuesToRange(sstracker,1,5,sstrackerLastRow+3,sstrackerLastRow+3);
var walkname = sstracker.getRange(sstrackerLastRow + 3, 2).getValue();
var date = sstracker.getRange(sstrackerLastRow + 3, 4).getValue(); 
var stage = sstracker.getRange(sstrackerLastRow + 3, 5).getValue(); 

var teacherarray = sstracker.getRange(2, 2, sstrackerLastRow, 1).getValues();
var stagearray = sstracker.getRange(1, 1, 1, 12).getValues(); 


for(var i = 0; i < teacherarray.length; i++){
  var teachname = teacherarray[i];
    if(teachname == walkname){

      for(var r = 0; r < 12; r++){
        var stagename = stagearray[0][r];
          if(stagename == stage){
      var row = sstracker.getRange(i + 2, r + 1).setValue(date).setBackground('Green');
    }
   }
  }
 }
  var removeLastRow = sstracker.getLastRow();
  var range = sstracker.getRange(removeLastRow, 1, 1, 12);
  range.clear();``

 }