VPTSchedulingScript.js

/**
 * @OnlyCurrentDoc
 */

/*
Add this to your "time difference column"
=if(OR(ISBLANK(W2), ISDATE(V2) =false, ISDATE(W2)=false) , (if(AND(ISDATE(V2), ISDATE(W2)=false), "started", "waiting")), W2-V2)
*/

function onInstallableEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  
 
  if( s.getIndex() != 0 ) { //checks that we're on the correct sheet
    
    var r = s.getActiveCell();
    console.log('Triggering on column: ' + r.getColumn())
    
    
    onStartedClicked(s, r)

    onDoneClicked(s, r)
    
    createNextDayCopy(s, r)
    
  }
  
 
}

function onStartedClicked(s, r) { 
  var header = s.getDataRange().getValues().shift();
  var indexOfStart = headerIndex(header, "Start time");
  var indexOfEnd = headerIndex(header, "End time");
  var indexOfStartCheck = headerIndex(header, "STARTED")
  var indexOfFinishCheck = headerIndex(header, "FINISHED")
  var timeZone = Session.getScriptTimeZone();
  var date = Utilities.formatDate(new Date(), timeZone, "HH:mm:ss");
  
  
  if (r.getColumn() != indexOfStartCheck) {
    // wront column
    return;
  }
  console.log('On started clicked')
  
  var nextCell = s.getRange(r.getRow(), indexOfStart);
  //if( nextCell.getValue() !== '' ) //is empty?
  nextCell.setValue(r.getValue() ? date : 'cleared');
  if (!r.getValue()) {
    s.getRange(r.getRow(), indexOfEnd).setValue('cleared');
  }
}

function onDoneClicked(s, r) {
  var header = s.getDataRange().getValues().shift();
  var indexOfEnd = headerIndex(header, "End time");
  var indexOfStart = headerIndex(header, "Start time");
  var nextCell = s.getRange(r.getRow(), indexOfEnd);
   var indexOfStartCheck = headerIndex(header, "STARTED")
  var indexOfFinishCheck = headerIndex(header, "FINISHED")
  var timeZone = Session.getScriptTimeZone();
  var date = Utilities.formatDate(new Date(), timeZone, "HH:mm:ss");
  
  if (r.getColumn() != indexOfFinishCheck) {
    // wront column
    return;
  }
  console.log('On done clicked')
  
  nextCell.setValue(r.getValue() ? date : 'cleared');
  if (!r.getValue()) {
   s.getRange(r.getRow(), indexOfStart).setValue('cleared');
  }
  else {
    // var protection = r.protect().setDescription("Protected cell");
    // protection.removeEditors(protection.getEditors());
    // protection = r.offset(0, -1).protect().setDescription("Protected cell");
    // protection.removeEditors(protection.getEditors());
    //r.setNote('Message a moderator to edit this again! It is locked.')
  }
}

function createNextDayCopy(s, r) {
  
  
  var header = s.getDataRange().getValues().shift();
  var indexOfFirst = headerIndex(header, "How many players am I looking for?");
  var indexOfStartCheck = headerIndex(header, "STARTED")
  var indexOfFinishCheck = headerIndex(header, "FINISHED")
  
  if (r.getColumn() != indexOfFirst || r.getRow() != 77 || r.getValue() != "copy") {
    // wront column
    return;
  }
  console.log('Copy triggered')
  
  var oldName = s.getName();
  var upName = (s.getName() + "" ).includes("Thursday") ? "Monday" : "Thursday";
  var providedName = s.getRange(77, indexOfFirst + 1).getValue()
  upName = providedName && providedName.length > 0 ? providedName : upName;
  var sheet = s.copyTo(SpreadsheetApp.getActiveSpreadsheet()).setName(upName);
  
  s.setName('OLD: ' + oldName)
  var prot = s.protect()
  prot.removeEditors(prot.getEditors());
  SpreadsheetApp.flush(); // 
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var tab = sheets[sheets.length -1]
  var RANGE = tab.getDataRange();
  var rangeVals = RANGE.getValues();
  
  for (var i = 1; i < rangeVals.length; i ++) {
    console.log('Whats this: ' + rangeVals[i][3]+ " set to == " + rangeVals[i][2])
    if (rangeVals[i][2] == true) {
      console.log('Deleting this: ' + rangeVals[i][2])
      
      
      var copiableValues = tab.getRange(74, 1, 1, 25).getValues();
      tab.insertRowAfter(73)
      tab.deleteRow(i + 1) 
      rangeVals.splice(i, 1)
      i--;
      SpreadsheetApp.flush();
      var emptyRange = tab.getRange(74, 1, 1, 25);
      emptyRange.setValues(copiableValues)
      SpreadsheetApp.flush();
    }
  }
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(tab);
  SpreadsheetApp.flush();
  SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(2);
}

function installonEdit() {
  if(!isTrigger('onInstallableEdit')) {
    ScriptApp.newTrigger('onInstallableEdit').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}

// Returns the column index (1-based) from the name. Currently the name must be an
// exact match, but should be changed to left-substring match as some of the column
// names can be long.
function headerIndex(header, title) {
  return header.indexOf(title) + 1;
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('My Menu')
  .addItem('Install On Edit Trigger','installonEdit')
  .addToUi();
}