function belongsTo(character, checkstring)
    {
	    ph = 0;
	    for (k=0; k < checkstring.length; k++)
	    {
    		if (character == checkstring[k].getName())
		    {
    			ph = ph + 1;
		    }
	    }
	    if (ph > 0)
	    {
		    return "true";
	    }
	    else
	    {
    		return "false";
    	}
    }

function setup()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var overview = ss.getSheetByName('Form Responses 1');
  var numStudents = 10;
  var numTopics = overview.getLastColumn();
  ss.setActiveSheet(overview);
  ss.renameActiveSheet('Self Assessment');
  ss.insertSheet('Teacher Assessment',1);
  var overview1 = ss.getSheetByName('Teacher Assessment');
  overview.setFrozenColumns(3);
  overview1.setFrozenColumns(1);
  overview1.setFrozenRows(1);
  var numRows = overview.getMaxRows();
  var numRows1 = overview1.getMaxRows();
  overview.deleteRows(numStudents + 2,numRows - (numStudents + 1));
  overview1.deleteRows(numStudents + 2,numRows1 - (numStudents + 1));
  overview.setColumnWidth(1,10);
  overview.setColumnWidth(2,10);
  overview.setColumnWidth(3,200);
  overview1.setColumnWidth(1,200);
  
  for (j = 4; j <= numTopics; j++)
    {
      var topicTextStart = overview.getRange(1,j).getValue().indexOf("[");
      var topicTextEnd = overview.getRange(1,j).getValue().indexOf("]");
      var topicText = overview.getRange(1,j).getValue().substring(topicTextStart + 1,topicTextEnd);
      overview.getRange(1,j).setValue(topicText);
      overview.setColumnWidth(j,30);
      overview1.getRange(1,j - 2).setValue(topicText);
      overview1.setColumnWidth(j - 2,30);
    }

    overview1.getRange(1,1).setFormula("=\'Self Assessment\'!C1");

  
  
}

function createTabs(fromSheet) 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var overview = ss.getSheetByName(fromSheet);
  var numStudents = overview.getLastRow();
  for (i = 2; i <= numStudents; i++)
  {
    var name = overview.getRange(i, 3).getValue();
    if (belongsTo(name,ss.getSheets()) == "true")
    {
      ss.deleteSheet(ss.getSheetByName(name));
    }
    ss.insertSheet(name,i);
  }
  
}

function selfAssess()
{
  createTabs('Self Assessment')
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var overview = ss.getSheetByName('Self Assessment');
  var numStudents = overview.getLastRow();
  var numTopics = overview.getLastColumn();
  var overview1 = ss.getSheetByName('Teacher Assessment');
  var grades = ["A","B","C","D"];
  for (i = 2; i <= numStudents; i++)
  {
    var name = overview.getRange(i, 3).getValue();
    overview1.getRange(i,1).setFormula("=\'Self Assessment\'!C"+i);
    var sheet = ss.getSheetByName(name);
    var Green = [];
    var Yellow = [];
    var Red = [];
    var noData = [];
    for (j = 4; j <= numTopics; j++)
    {
      var topicText = overview.getRange(1,j).getValue();
      if (overview.getRange(i,j).getValue() == "CONFIDENT on all questions or GREEN or :-)")
      {
        Green[Green.length] = topicText;
        overview.getRange(i,j).setBackground("green");
      }
      else if (overview.getRange(i,j).getValue() == "OK with most but some issues or YELLOW or :-|")
      {
        Yellow[Yellow.length] = topicText;
        overview.getRange(i,j).setBackground("yellow");
      }
      else if (overview.getRange(i,j).getValue() == "CONFUSED with it all or RED or :-(")
      {
        Red[Red.length] = topicText;
        overview.getRange(i,j).setBackground("red");
      }
      else
      {
        noData[noData.length] = topicText;
        overview.getRange(i,j).setBackground("white");
      }
    }
    
    var Green1 = [];
    var Blue1 = [];
    var Yellow1 = [];
    var Red1 = [];
    var noData1 = [];
    for (j = 2; j <= numTopics - 2; j++)
    {
      var topicText1 = overview1.getRange(1,j).getValue();
      if (overview1.getRange(i,j).getValue().toLowerCase() == grades[0].toLowerCase())
      {
        Green1[Green1.length] = topicText1;
        overview1.getRange(i,j).setBackground("green");
      }
      else if (overview1.getRange(i,j).getValue().toLowerCase() == grades[1].toLowerCase())
      {
        Blue1[Blue1.length] = topicText1;
        overview1.getRange(i,j).setBackground("blue");
      }
      else if (overview1.getRange(i,j).getValue().toLowerCase() == grades[2].toLowerCase())
      {
        Yellow1[Yellow1.length] = topicText1;
        overview1.getRange(i,j).setBackground("yellow");
      }
      else if (overview1.getRange(i,j).getValue().toLowerCase() == grades[3].toLowerCase())
      {
        Red1[Red1.length] = topicText1;
        overview1.getRange(i,j).setBackground("red");
      }
      else
      {
        noData1[noData1.length] = topicText1;
        overview1.getRange(i,j).setBackground("white");
      }
    }
    
    sheet.getRange(1,1).setValue(name).setFontSize(20).setHorizontalAlignment("center");
    sheet.getRange(3,1).setValue("Self Assessment").setFontSize(16).setHorizontalAlignment("center");
    sheet.getRange(4,1).setValue("RED - Needs urgent attention").setFontSize(14).setHorizontalAlignment("center").setBackground("red");
    
    var last = 5;
    
    for (k1 = 0; k1 < Red.length; k1++)
    {
      sheet.getRange(last,1).setValue(Red[k1]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    sheet.getRange(last,1).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last = last + 1;
    sheet.getRange(last,1).setValue("YELLOW - Needs moderate attention").setFontSize(14).setHorizontalAlignment("center").setBackground("yellow");
    last = last + 1;
    
    for (k2 = 0; k2 < Yellow.length; k2++)
    {
      sheet.getRange(last,1).setValue(Yellow[k2]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    sheet.getRange(last,1).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last = last + 1;
    sheet.getRange(last,1).setValue("GREEN - OK with these").setFontSize(14).setHorizontalAlignment("center").setBackground("green");
    last = last + 1;
    
    for (k3 = 0; k3 < Green.length; k3++)
    {
      sheet.getRange(last,1).setValue(Green[k3]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    sheet.getRange(last,1).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last = last + 1;
    sheet.getRange(last,1).setValue("No Data for these").setFontSize(14).setHorizontalAlignment("center").setBackground("purple");
    last = last + 1;
    
    for (k5 = 0; k5 < noData.length; k5++)
    {
      sheet.getRange(last,1).setValue(noData[k5]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    
    
    sheet.getRange(3,3).setValue("Teacher Assessment").setFontSize(16).setHorizontalAlignment("center");
    sheet.getRange(4,3).setValue(grades[3] + " - Needs urgent attention").setFontSize(14).setHorizontalAlignment("center").setBackground("red");
    
    var last1 = 5;
    
    for (k1 = 0; k1 < Red1.length; k1++)
    {
      sheet.getRange(last1,3).setValue(Red1[k1]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue(grades[2] + " - Needs moderate attention").setFontSize(14).setHorizontalAlignment("center").setBackground("yellow");
    last1 = last1 + 1;
    
    for (k2 = 0; k2 < Yellow1.length; k2++)
    {
      sheet.getRange(last1,3).setValue(Yellow1[k2]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue(grades[1] + " - Needs some attention").setFontSize(14).setHorizontalAlignment("center").setBackground("blue");
    last1 = last1 + 1;
    
    for (k4 = 0; k4 < Blue1.length; k4++)
    {
      sheet.getRange(last1,3).setValue(Blue1[k4]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue(grades[0] + " - OK with these").setFontSize(14).setHorizontalAlignment("center").setBackground("green");
    last1 = last1 + 1;
    
    for (k3 = 0; k3 < Green1.length; k3++)
    {
      sheet.getRange(last1,3).setValue(Green1[k3]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue("No Data for these").setFontSize(14).setHorizontalAlignment("center").setBackground("purple");
    last1 = last1 + 1;
    
    for (k5 = 0; k5 < noData1.length; k5++)
    {
      sheet.getRange(last1,3).setValue(noData1[k5]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    var numRows = Math.max(last,last1);
    sheet.deleteRows(numRows + 2,sheet.getMaxRows() - (numRows + 1));
    sheet.deleteColumns(4,sheet.getMaxColumns() - 3);
    
    sheet.autoResizeColumn(1);
    sheet.autoResizeColumn(3);
    
    var folder = DriveApp.getFileById(ss.getId()).getParents().next();
    if (folder.getFilesByName(name).hasNext() == true)
    {
      var newID = folder.getFilesByName(name).next().getId();
      var destination = SpreadsheetApp.openById(newID);
      sheet.copyTo(destination);
      if (belongsTo(overview.getRange(i,1).getValue(),destination.getSheets()) == "true")
      {
        destination.deleteSheet(destination.getSheetByName(overview.getRange(i,1).getValue()));
      }
      destination.setActiveSheet(destination.getSheets()[destination.getSheets().length - 1]);
      destination.renameActiveSheet(overview.getRange(i,1).getValue());
      destination.getActiveSheet().deleteColumn(3);
      destination.moveActiveSheet(0);
    }
    else
    {
      var ssName = SpreadsheetApp.create(name);
      var newID = ssName.getId();
      var destination = SpreadsheetApp.openById(newID);
      sheet.copyTo(destination);
      var toDelete = destination.getSheetByName('Sheet1');
      destination.deleteSheet(toDelete);
      destination.setActiveSheet(destination.getSheets()[0]);
      destination.renameActiveSheet(overview.getRange(i,1).getValue());
      destination.getActiveSheet().deleteColumn(3);
      folder.addFile(DriveApp.getFileById(newID));
      DriveApp.removeFile(DriveApp.getFileById(newID));
      DriveApp.getFileById(newID).addCommenter(overview.getRange(i,2).getValue());
      DriveApp.getFileById(newID).setSharing(DriveApp.Access.PRIVATE,DriveApp.Permission.COMMENT);
    }
  }
}




function selfAssessFormSubmit(e)
{
  createTabs('Self Assessment')
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var overview = ss.getSheetByName('Self Assessment');
  var numStudents = overview.getLastRow();
  var numTopics = overview.getLastColumn();
  var overview1 = ss.getSheetByName('Teacher Assessment');
  var grades = ["A","B","C","D"];
  var timestamp2 = e.values[0];
  var i1 = 2;
  while (i1 <= numStudents)
  {
    var timestamp1 = overview.getRange(i1,1).setNumberFormat('@STRING@').getValue();
    if (timestamp1 == timestamp2)
    {
      var i = i1;
    }
    i1 = i1 + 1;
  }
  
  
    var name = overview.getRange(i, 3).getValue();
  overview1.getRange(i,1).setFormula("=\'Self Assessment\'!C"+i);
    var sheet = ss.getSheetByName(name);
    var Green = [];
    var Yellow = [];
    var Red = [];
    var noData = [];
    for (j = 4; j <= numTopics; j++)
    {
      var topicText = overview.getRange(1,j).getValue();
      if (overview.getRange(i,j).getValue() == "CONFIDENT on all questions or GREEN or :-)")
      {
        Green[Green.length] = topicText;
        overview.getRange(i,j).setBackground("green");
      }
      else if (overview.getRange(i,j).getValue() == "OK with most but some issues or YELLOW or :-|")
      {
        Yellow[Yellow.length] = topicText;
        overview.getRange(i,j).setBackground("yellow");
      }
      else if (overview.getRange(i,j).getValue() == "CONFUSED with it all or RED or :-(")
      {
        Red[Red.length] = topicText;
        overview.getRange(i,j).setBackground("red");
      }
      else
      {
        noData[noData.length] = topicText;
        overview.getRange(i,j).setBackground("white");
      }
    }
    
    var Green1 = [];
    var Blue1 = [];
    var Yellow1 = [];
    var Red1 = [];
    var noData1 = [];
    for (j = 2; j <= numTopics - 2; j++)
    {
      var topicText1 = overview1.getRange(1,j).getValue();
      if (overview1.getRange(i,j).getValue().toLowerCase() == grades[0].toLowerCase())
      {
        Green1[Green1.length] = topicText1;
        overview1.getRange(i,j).setBackground("green");
      }
      else if (overview1.getRange(i,j).getValue().toLowerCase() == grades[1].toLowerCase())
      {
        Blue1[Blue1.length] = topicText1;
        overview1.getRange(i,j).setBackground("blue");
      }
      else if (overview1.getRange(i,j).getValue().toLowerCase() == grades[2].toLowerCase())
      {
        Yellow1[Yellow1.length] = topicText1;
        overview1.getRange(i,j).setBackground("yellow");
      }
      else if (overview1.getRange(i,j).getValue().toLowerCase() == grades[3].toLowerCase())
      {
        Red1[Red1.length] = topicText1;
        overview1.getRange(i,j).setBackground("red");
      }
      else
      {
        noData1[noData1.length] = topicText1;
        overview1.getRange(i,j).setBackground("white");
      }
    }
    
    sheet.getRange(1,1).setValue(name).setFontSize(20).setHorizontalAlignment("center");
    sheet.getRange(3,1).setValue("Self Assessment").setFontSize(16).setHorizontalAlignment("center");
    sheet.getRange(4,1).setValue("RED - Needs urgent attention").setFontSize(14).setHorizontalAlignment("center").setBackground("red");
    
    var last = 5;
    
    for (k1 = 0; k1 < Red.length; k1++)
    {
      sheet.getRange(last,1).setValue(Red[k1]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    sheet.getRange(last,1).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last = last + 1;
    sheet.getRange(last,1).setValue("YELLOW - Needs moderate attention").setFontSize(14).setHorizontalAlignment("center").setBackground("yellow");
    last = last + 1;
    
    for (k2 = 0; k2 < Yellow.length; k2++)
    {
      sheet.getRange(last,1).setValue(Yellow[k2]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    sheet.getRange(last,1).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last = last + 1;
    sheet.getRange(last,1).setValue("GREEN - OK with these").setFontSize(14).setHorizontalAlignment("center").setBackground("green");
    last = last + 1;
    
    for (k3 = 0; k3 < Green.length; k3++)
    {
      sheet.getRange(last,1).setValue(Green[k3]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    sheet.getRange(last,1).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last = last + 1;
    sheet.getRange(last,1).setValue("No Data for these").setFontSize(14).setHorizontalAlignment("center").setBackground("purple");
    last = last + 1;
    
    for (k5 = 0; k5 < noData.length; k5++)
    {
      sheet.getRange(last,1).setValue(noData[k5]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last = last + 1;
    }
    
    
    
    sheet.getRange(3,3).setValue("Teacher Assessment").setFontSize(16).setHorizontalAlignment("center");
    sheet.getRange(4,3).setValue(grades[3] + " - Needs urgent attention").setFontSize(14).setHorizontalAlignment("center").setBackground("red");
    
    var last1 = 5;
    
    for (k1 = 0; k1 < Red1.length; k1++)
    {
      sheet.getRange(last1,3).setValue(Red1[k1]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue(grades[2] + " - Needs moderate attention").setFontSize(14).setHorizontalAlignment("center").setBackground("yellow");
    last1 = last1 + 1;
    
    for (k2 = 0; k2 < Yellow1.length; k2++)
    {
      sheet.getRange(last1,3).setValue(Yellow1[k2]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue(grades[1] + " - Needs some attention").setFontSize(14).setHorizontalAlignment("center").setBackground("blue");
    last1 = last1 + 1;
    
    for (k4 = 0; k4 < Blue1.length; k4++)
    {
      sheet.getRange(last1,3).setValue(Blue1[k4]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue(grades[0] + " - OK with these").setFontSize(14).setHorizontalAlignment("center").setBackground("green");
    last1 = last1 + 1;
    
    for (k3 = 0; k3 < Green1.length; k3++)
    {
      sheet.getRange(last1,3).setValue(Green1[k3]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    sheet.getRange(last1,3).setValue("").setFontSize(10).setHorizontalAlignment("left").setBackground("white");
    last1 = last1 + 1;
    sheet.getRange(last1,3).setValue("No Data for these").setFontSize(14).setHorizontalAlignment("center").setBackground("purple");
    last1 = last1 + 1;
    
    for (k5 = 0; k5 < noData1.length; k5++)
    {
      sheet.getRange(last1,3).setValue(noData1[k5]).setFontSize(10).setHorizontalAlignment("left").setBackground("white");
      last1 = last1 + 1;
    }
    
    var numRows = Math.max(last,last1);
    sheet.deleteRows(numRows + 2,sheet.getMaxRows() - (numRows + 1));
    sheet.deleteColumns(4,sheet.getMaxColumns() - 3);
  
    sheet.autoResizeColumn(1);
    sheet.autoResizeColumn(3);
    
  
  
    var folder = DriveApp.getFileById(ss.getId()).getParents().next();
    if (folder.getFilesByName(name).hasNext() == true)
    {
      var newID = folder.getFilesByName(name).next().getId();
      var destination = SpreadsheetApp.openById(newID);
      sheet.copyTo(destination);
      if (belongsTo(overview.getRange(i,1).getValue(),destination.getSheets()) == "true")
      {
        destination.deleteSheet(destination.getSheetByName(overview.getRange(i,1).getValue()));
      }
      destination.setActiveSheet(destination.getSheets()[destination.getSheets().length - 1]);
      destination.renameActiveSheet(overview.getRange(i,1).getValue());
      destination.getActiveSheet().deleteColumn(3);
      destination.moveActiveSheet(0);
      MailApp.sendEmail(overview.getRange(i,2).getValue(),"Update to " + FormApp.openByUrl(ss.getFormUrl()).getTitle(),"An update has been made to your Self Assessment, which can be viewed here: " + destination.getUrl());
    }
    else
    {
      var ssName = SpreadsheetApp.create(name);
      var newID = ssName.getId();
      var destination = SpreadsheetApp.openById(newID);
      sheet.copyTo(destination);
      var toDelete = destination.getSheetByName('Sheet1');
      destination.deleteSheet(toDelete);
      destination.setActiveSheet(destination.getSheets()[0]);
      destination.renameActiveSheet(overview.getRange(i,1).getValue());
      destination.getActiveSheet().deleteColumn(3);
      folder.addFile(DriveApp.getFileById(newID));
      DriveApp.removeFile(DriveApp.getFileById(newID));
      DriveApp.getFileById(newID).addCommenter(overview.getRange(i,2).getValue());
      DriveApp.getFileById(newID).setSharing(DriveApp.Access.PRIVATE,DriveApp.Permission.COMMENT);
    }
}