Turnover Forms

Do you keep track of everything that's been handed over by different persons. When was it given? When was it solved? How much of it was similar? Maybe if only I had known how much of it occurs very often, then I would have it solved with a long term solution by now and saved me some time doing this maintenance work over and over again. Better use Google Appscript.

Google Appscript works like and is very similar to Javascript. If you have a bad PC and your company can't afford live servers. Then google's Appscript can be your gateway to get things done automatically. There are free and paid options of course.

Complete Formula

function placeCheck(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form= ss.getSheetByName("Form");
  var newturnoverform = form.getRange("E4:E");
  var newidform = form.getRange("F4:F");
  var newturnoverformvalues = newturnoverform.getValues();
  var newidformvalues = newidform.getValues();
  var turnoverform = form.getRange("E4:E");
  var b = turnoverform.getValues();
  
  if(form.getRange("C24").getValue()=="Done"){
  for(var i =0;i<b.length; i++){
    var newturnover = newturnoverformvalues [i][0];
    if(newturnover !== "" ){
      form.getRange(i+4,4).setValue(true);           
    }
  }
  }
  else{}
}




function onEdit(e) {

  var count = 0;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form");
  var sheet2 = ss.getSheetByName("Data");
  var range2 = sheet2.getRange("B4:B");
  var rangeVals2 = range2.getValues();
  var newturn = sheet.getRange("I9:I23");
  var newtime = sheet.getRange("H9:H23");
  var newid = sheet.getRange("O9:O23");
  var newturnVals = newturn.getValues();
  var newtimeVals = newtime.getValues();
  var checkturn = 0;
  var checktime = 0;
  

  
  for(var k =0; k<newtimeVals.length; k++){
        var instanceturn = newturnVals [k][0];
        var instancetime = newtimeVals [k][0];
        if(instanceturn !== "" ){
          checkturn = checkturn + 1;
        }
        if(instancetime !== "" ){
          checktime = checktime + 1;
        }
    } 
  if(checkturn == checktime){ 
  }
  else{
    newid.clearContent();
    newtime.clearContent();
  }
  for(var k =0; k<rangeVals2.length; k++){
        var instance = rangeVals2 [k][0];
        if(instance !== ""){
          count = count + 1;
        }
    } 
  for(var i =0; i<newturnVals.length; i++){
    var instance = newturnVals[i][0];
    if(instance !== ""){
       sheet.getRange(i+9,8).setValue(count+i+1);
       sheet.getRange(i+9,15).setValue(new Date());
    }   
  };
  if(sheet.getRange("C23").getValue() !== "Pick Date"){
    sheet.getRange("C26:C27").clearContent();
  }
  
}


function consolidateToDoList() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form");
  var sheet2 = ss.getSheetByName("Data"); //Data Sheet
  var range = sheet.getRange("E4:E");
  var check = sheet.getRange("C4:C");
  var rangeb = sheet.getRange("F4:F");
  var range2 = sheet2.getRange("B4:B");
  var timerange = sheet.getRange("H3:H");
  var rangeVals = range.getValues();
  var rangeValsb = rangeb.getValues();
  var rangeVals2 = range2.getValues();
  var finalList = new Array();
  var finalListb = new Array();
  var dataList = new Array();
  var dataListb = new Array();
  var dataListc = new Array();
  var newturn = sheet.getRange("I9:I23");
  var newturnVals = newturn.getValues();
  var newid = sheet.getRange("H9:H23");
  var newidNo = newid.getValues();
  var newtime = sheet.getRange("O9:O23");
  var newtimeVals = newtime.getValues();
  var countnew = 0;
  
  for(var k =0; k<newtimeVals.length; k++){
    var instance = newtimeVals[k][0];
    if(instance !== ""){
      var instanceBlock = [instance];
      dataListc.push(instanceBlock);
    }
    
  };
  for(var i =0; i<rangeVals.length; i++){
    var instance = rangeVals[i][0];
    if(instance !== ""){
      var instanceBlock = [instance];
      finalList.push(instanceBlock);
    }
  };
  for(var i =0; i<newturnVals.length; i++){
    var instance = newturnVals[i][0];
    if(instance !== ""){
      var instanceBlock = [instance];
      finalList.push(instanceBlock);
      dataList.push(instanceBlock);
      countnew = countnew+1
    }
  };   
  for(var j =0; j<rangeValsb.length; j++){
    var instanceb = rangeValsb[j][0];
    Logger.log(instanceb);
    if(instanceb !== ""){
        var instanceBlockb = [instanceb];
        finalListb.push(instanceBlockb);  
    }   
  }
   for(var j =0; j<newidNo.length; j++){  
    var instanceb = newidNo[j][0];
    if(instanceb !== ""){
        var instanceBlockb = [instanceb];
        finalListb.push(instanceBlockb);
        dataListb.push(instanceBlockb);     
    }    
  }
  if(sheet.getRange(9,9).isBlank()){ 
  }
  else  {
  var lastrow = sheet2.getLastRow()+1;
  sheet2.getRange(lastrow, 2, countnew, 1).setValue(dataListc);
  sheet2.getRange(lastrow, 4, countnew, 1).setValues(dataListb);
  sheet2.getRange(lastrow, 5, countnew, 1).setValues(dataList);
  range.clearContent();
  rangeb.clearContent();
  newturn.clearContent();
  newid.clearContent();
  newtime.clearContent();
  range.offset(0,0, finalList.length).setValues(finalList);
  rangeb.offset(0,0, finalListb.length).setValues(finalListb);  
  }
  
}

function updateTurnover() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form");
  var sheet2 = ss.getSheetByName("Data");
  var help = ss.getSheetByName("Help");
  var range = sheet.getRange("E4:E");
  var rangec = sheet.getRange("F4:F");
  var checkboxrange = sheet.getRange("D4:D");
  var timerange = sheet.getRange("F4:F");
  var rangeVals = range.getValues();
  var checkVals = checkboxrange.getValues();
  var timeVals = timerange.getValues();
  var finalList = new Array();
  var finalListc = new Array();
  var count = 0;
  var countt = 0;
  var helptags = help.getRange("B3:B17").getValues();
  var showtags = new Array();

  
  // iteration start
  if(sheet.getRange("C24").getValue() == "Unfinished"){
    
  for(var i =0; i<rangeVals.length; i++){
    var instancerange = rangeVals[i][0];
    var instancecheck = checkVals[i][0];
    var rangec = timeVals[i][0];

 
  //check for unchecked box and save to instanceBlock to push in new Array of finalList
    
    if(instancerange !== "" && instancecheck == false ){
      var instanceBlock = [instancerange];
      finalList.push(instanceBlock);
      var instanceBlockc = [rangec];
      finalListc.push(instanceBlockc);
      
      
    }
    
  //check for checked box and save it to finalList2 for new array
    
    if(instancerange !== "" && instancecheck == true ){
      
      var endtimerow = sheet.getRange(i+4,6).getValue();
      
      sheet2.getRange(endtimerow+3,3).setValue(new Date());
      count = 0; 
      countt = countt +1;
    
      
    }
    
  }
  
  range.clearContent();
  timerange.clearContent();
  checkboxrange.clearContent();
  
  if(finalList.length == 0){
  
  }
  else{
  sheet.getRange(4,5,finalList.length).setValues(finalList);
  sheet.getRange(4,6,finalList.length).setValues(finalListc);
  
  }
  }
  // if unfinished dont do anything
  else{
  }
  
   for(var i =0; i<helptags.length; i++){
    var instance = helptags[i][0];
    
    if(instance !== ""){
      var instanceBlock = [instance];
      showtags.push(instanceBlock);
    }
    
  };
  sheet.getRange(5,2,helptags.length).setValues(helptags);
  
  
}


function reset() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form = ss.getSheetByName("Form");
  var data = ss.getSheetByName("Data");
  var allList = new Array();
  var idList = new Array(); 
  var formrange = form.getRange("E4:F");
  var datarange = data.getRange("E4:E"); 
  var finData = data.getRange("C4:C"); 
  var idData = data.getRange("D4:D");  
  var formvalues = formrange.getValues();
  var datavalues = datarange.getValues(); 
  var finvalues = finData.getValues();
  var idvalues = idData.getValues();
  form.getRange("D4:D").clearContent();

  var ignore = 0; 
   for(var i =0; i<datavalues.length; i++){
    var instance = datavalues[i][0];
    var fininstance = finvalues[i][0];
    var idinstance = idvalues[i][0];
    if(instance !== "" && fininstance == "" ){
      var instanceBlock = [instance];
      var idinstanceBlock = [idinstance];
      ignore = ignore +1;  
      allList.push(instanceBlock);
      idList.push(idinstanceBlock);
    }
   }
   
  
   if(ignore > 0){
     formrange.clearContent();
     form.getRange(4,5,allList.length).setValues(allList);
     form.getRange(4,6,idList.length).setValues(idList);
     }
     else{
       formrange.clearContent();
     }
     
  form.getRange("C22").setValue("Sana All");
  form.getRange("C23").setValue("Sana All");
  form.getRange("C24").setValue("Unfinished");
  
}

function equipment() {
  
  updateFilter();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form = ss.getSheetByName("Form");
  var data = ss.getSheetByName("Data");
  var allList = new Array();
  var idList = new Array();
 
  var formrange = form.getRange("D4:F");
  var turnrange = form.getRange("E4:E"); 
  var idrange = form.getRange("F4:F");  
  var idData = form.getRange("D4:D"); 
  
  var formvalues = formrange.getValues();
  var datavalues = turnrange.getValues(); 
  var idvalues = idrange.getValues();
  
  var ignore = 0;
    
   for(var i =0; i<datavalues.length; i++){
    var instance = datavalues[i][0];
    var idinstance = idvalues[i][0];
    
   
    
    if(instance !== ""&& instance.indexOf("#") > 1 ){
      var instanceBlock = [instance];
      var idinstanceBlock = [idinstance];
      
       ignore = ignore + 1;

      
      allList.push(instanceBlock);
      idList.push(idinstanceBlock);
    }
   
   
     
   }
  if(ignore > 0){
     formrange.clearContent();
    
   if(form.getRange("C24").getValue()=="Done"){
     for(var i =0; i<ignore; i++){
       form.getRange(i+4,4).setValue(true);
    
     }
     }
     if(form.getRange("C24").getValue()=="Unfinished"){
     }
     form.getRange(4,5,allList.length).setValues(allList);
     form.getRange(4,6,idList.length).setValues(idList);
  }
     else{
       formrange.clearContent();
     }
  
}

function notes() {
  
   updateFilter();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form = ss.getSheetByName("Form");
  var data = ss.getSheetByName("Data");
  var allList = new Array();
  var idList = new Array();
 
  var formrange = form.getRange("D4:F");
  var turnrange = form.getRange("E4:E"); 
  var idrange = form.getRange("F4:F");  
  var idData = form.getRange("D4:D"); 
  
  var formvalues = formrange.getValues();
  var datavalues = turnrange.getValues(); 
  var idvalues = idrange.getValues();
  
  var ignore = 0;
  
   for(var i =0; i<datavalues.length; i++){
    var instance = datavalues[i][0];
    var idinstance = idvalues[i][0];
     
     
       
     
    
    if(instance !== "" && instance.indexOf("#") < 1 ){
      var instanceBlock = [instance];
      var idinstanceBlock = [idinstance];
      
      ignore = ignore + 1;
      
      allList.push(instanceBlock);
      idList.push(idinstanceBlock);
    }
   
   }
   if(ignore >0){
     formrange.clearContent();
     if(form.getRange("C24").getValue()=="Done"){
     for(var i =0; i<ignore; i++){
       form.getRange(i+4,4).setValue(true);
    
     }
     }
     if(form.getRange("C24").getValue()=="Unfinished"){
     }
     
     form.getRange(4,5,allList.length).setValues(allList);
     form.getRange(4,6,idList.length).setValues(idList);
   }
     else{
       
       formrange.clearContent();
     }
   
  
  
}


function updateFilter(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("Data");
  var form = ss.getSheetByName("Form");
  var clear = form.getRange("D4:F");
  clear.clearContent();
  var wholedata = data.getRange(4, 2,data.getLastRow()+4,4).getValues();
  var newData ;

 if(form.getRange("C24").getValue() =="Unfinished"){
   if(form.getRange("C22").getValue() !=="Sana All"){
     
     newData = wholedata.filter(filterNotSanaAllUnfinished);
     
   }
    
   if(form.getRange("C22").getValue() =="Sana All"){
     newData = wholedata.filter(filterSanaAllUnfinished);
   }
 }
   if(form.getRange("C24").getValue() =="Done"){
   if(form.getRange("C22").getValue() !=="Sana All"){
     
     newData = wholedata.filter(filterNotSanaAllDone);
     
     
   }
    
   if(form.getRange("C22").getValue() =="Sana All"){
     newData = wholedata.filter(filterSanaAllDone);
   }
 }
 
  
  var turn = newData.map(function(e){return e[3];});
  var id = newData.map(function(e){return e[2];});
 
  for(var i=0; i<turn.length; i++){
  form.getRange(4+i,5).setValue(turn[i]); 
  form.getRange(4+i,6).setValue(id[i])  
      }
  placeCheck();
}

  var filterNotSanaAllUnfinished = function(item){
    
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var data = ss.getSheetByName("Data");
   var form = ss.getSheetByName("Form");
    var mintimeChoose = form.getRange("C26").getValue()/(24*60*60*1000);
   var maxtimeChoose = form.getRange("C27").getValue()/(24*60*60*1000);
   var newDate = new Date()/(24*60*60*1000);
      var area = form.getRange("C22").getValue();


   var mintime;
   var maxtime;
   
 if( form.getRange("C23").getValue()== "Recent"){
      maxtime = 2*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Week"){
      maxtime = 8*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Month"){
      maxtime = 32*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Sana All"){
      maxtime = 10000*24*60*60*1000;
      mintime =0;

      }
    if( form.getRange("C23").getValue()== "Pick Date"){
     mintime = (newDate-maxtimeChoose-1)*24*60*60*1000;
      maxtime = (newDate-mintimeChoose)*24*60*60*1000;

      }
  
  if(item[1]=="" && item[3].indexOf(area)> 1 && new Date()- new Date(item[0])<=maxtime&& new Date()- new Date(item[0])>=mintime){
    return true;
  }else{
    return false;
  }
  }
  
 var filterSanaAllUnfinished = function(item){
   
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var data = ss.getSheetByName("Data");
   var form = ss.getSheetByName("Form");
 var mintimeChoose = form.getRange("C26").getValue()/(24*60*60*1000);
   var maxtimeChoose = form.getRange("C27").getValue()/(24*60*60*1000);
   var newDate = new Date()/(24*60*60*1000);
     var area = form.getRange("C22").getValue();
 
 
   var mintime;
   var maxtime;
   
   if( form.getRange("C23").getValue()== "Recent"){
      maxtime = 2*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Week"){
      maxtime = 8*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Month"){
      maxtime = 32*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Sana All"){
      maxtime = 10000*24*60*60*1000;
      mintime =0;

      }
   if( form.getRange("C23").getValue()== "Pick Date"){
mintime = (newDate-maxtimeChoose-1)*24*60*60*1000;
      maxtime = (newDate-mintimeChoose)*24*60*60*1000;

      }
   
  if(item[1]==""&& new Date()- new Date(item[0])<=maxtime&& new Date()- new Date(item[0])>=mintime){
    Logger.log("aw");
    return true;
  }else{
    return false;
  }
  
 
}

var filterNotSanaAllDone = function(item){
    
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var data = ss.getSheetByName("Data");
   var form = ss.getSheetByName("Form");
  var area = form.getRange("C22").getValue();
  var mintimeChoose = form.getRange("C26").getValue()/(24*60*60*1000);
   var maxtimeChoose = form.getRange("C27").getValue()/(24*60*60*1000);
   var newDate = new Date()/(24*60*60*1000);
 

   var mintime;
   var maxtime;
   
    if( form.getRange("C23").getValue()== "Recent"){
      maxtime = 2*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Week"){
      maxtime = 8*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Month"){
      maxtime = 32*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Sana All"){
      maxtime = 100000*24*60*60*1000;
      mintime =0;

      }
    if( form.getRange("C23").getValue()== "Pick Date"){
     mintime = (newDate-maxtimeChoose-1)*24*60*60*1000;
      maxtime = (newDate-mintimeChoose)*24*60*60*1000;

      }
  
  if(item[1]!=="" && item[3].indexOf(area)> 1 && new Date()- new Date(item[0])<=maxtime&& new Date()- new Date(item[0])>=mintime){
    return true;
  }else{
    return false;
  }
  }
  
 var filterSanaAllDone= function(item){
   
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var data = ss.getSheetByName("Data");
   var form = ss.getSheetByName("Form");
   var mintimeChoose = form.getRange("C26").getValue()/(24*60*60*1000);
   var maxtimeChoose = form.getRange("C27").getValue()/(24*60*60*1000);
   var newDate = new Date()/(24*60*60*1000);
   
 
   var mintime;
   var maxtime;
   
if( form.getRange("C23").getValue()== "Recent"){
      maxtime = 2*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Week"){
      maxtime = 8*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Last Month"){
      maxtime = 32*24*60*60*1000;
      mintime =0;
      }
    if( form.getRange("C23").getValue()== "Sana All"){
      maxtime = 100000*24*60*60*1000;
      mintime =0;

      }
    if( form.getRange("C23").getValue()== "Pick Date"){
      mintime = (newDate-maxtimeChoose-1)*24*60*60*1000;
      maxtime = (newDate-mintimeChoose)*24*60*60*1000;
      }
   
  if(item[1]!==""&& new Date()- new Date(item[0])<=maxtime&& new Date()- new Date(item[0])>=mintime){
    return true;
  }else{
    return false;
  }
  
 
}