APC RTO

/*---------------------------------------------------------------------------------*/
// Global Scope Variables
var app = SpreadsheetApp.getActiveSpreadsheet();
var int = app.getSheetByName("Interview");
var que = app.getSheetByName("Organized Questions");
var ans = app.getSheetByName("Organized Answers");
var hid = app.getSheetByName("HideInterviews");
var aca = app.getSheetByName("Academy");
var data = populateArr(); // RTO | Date | New Cop | Steam ID | Score | LINK | Accepted/Declined
var arr = [
  ["A3"], // 0||1
  ["B3"], // 1||2
  ["C3"], // 2||3
  ["D3","D4","D5"], // 3||4
  ["E3","E4","E5","E6"], // 4||5
  ["F3","F4","F5","F6","F7","F8","F9"], // 5||6
  ["G3","G4","G5","G6","G7","G8","G9","G10","G11"], // 6||7
  ["H3","H4","H5","H6","H7","H8"], // 7||8
  ["I3"],// 8||9
  ["J3","J4","J5","J5","J6"], // 9||10
  ["K3","K4","K5","K6","K7"], // 10||11
  ["L3","L4","L5","L6"], // 11||12
  ["M3","M4"], // 12||13
  ["N3","N4"], // 13||14
  ["O3"], // 14||15
  ["P3"] // 15||16
];
/*---------------------------------------------------------------------------------*/
// Start: Imports questions
function importQuestions() {
  app.toast('', 'Status: Importing Questions');
  var arrPostions = [];
  for (var i = 0; i <= arr.length - 1; i++) {
    var innerValue = Math.floor(Math.random() * arr[i].length);
    arrPostions.push(innerValue);
  }
  var a = 15; // Insert Row
  for (var p = 0; p <= arrPostions.length - 1; p++) {
    var queText = que.getRange(arr[p][arrPostions[p]]).getValue();
    var ansText = ans.getRange(arr[p][arrPostions[p]]).getValue();
      if (a != 17 && a != 20 && a != 22 && a != 27 && a != 32) {
        int.getRange(a, 3).setValue(queText);
        int.getRange(a, 6).setValue(ansText);
        a++;
      } else {
        p--;
        a++;
      }
    }
    app.toast('Questions Have Successfully Imported.', 'Status: Completed');
  return;
}
// Finish: Imports questions
/*---------------------------------------------------------------------------------*/
// Start: Clear questions
function clearQuestions() {
  int.getRange("C15:C35").clearContent();
  int.getRange("F15:H35").clearContent();
  app.toast('Questions Cleared Successfully.', 'Status: Completed');
}
// Finish: Clear questions
/*---------------------------------------------------------------------------------*/
// Start: Finish button
function finsih() {
  var appID = app.getId();
  var folder = DriveApp;
  var accFold = folder.getFolderById("1qvl3iQf7ntnJsFMHduMxKlLo7Mq0fw4E"); //Accepted folder
  var decFold = folder.getFolderById("1qQcrTBQmKiDI67bZvLFWK-k5tk00R8fT"); //Failed folder
  var curFold = folder.getFolderById("1XkMcT5Afeik6J2VS7rsjZSI3GFKn6oON"); //Current Foldere
  var score = int.getRange("A45").getValue();
  var tick = int.getRange("J42").getValue();
  var title = int.getRange("D12").getValue();
  int.getRange("D11").setValue(data[1]);
  app.setName(title)
  if (score > 114 && tick != "✔") {
    importAca();
    importHid();
    accFold.addFile(DriveApp.getFileById(appID));
    curFold.removeFile(DriveApp.getFileById(appID));
    app.toast("The sheet has been moved to the ACCEPTED folder.","RTO Interview");
    int.getRange("J42").setValue("✔") ;
  } else {
    importHid();
    decFold.addFile(DriveApp.getFileById(appID));
    curFold.removeFile(DriveApp.getFileById(appID));
    app.toast("The sheet has been moved to the FAILED folder.","RTO Interview");
    int.getRange("J42").setValue("✔") ;
  }
}
// Finish: Finish button
/*---------------------------------------------------------------------------------*/
// Start: Imports data to HideInterviews sheet
function populateArr() {
  var playerInfo = [];
  var indents = [9,11,6,7];
  for (var i = 0; i <= 3; i++ ) {
    var data = int.getRange(indents[i], 4).getValue();
    playerInfo.push([data]);
  }
  playerInfo.splice(4, 0,[int.getRange("A45").getValue()]);
  var url = int.getRange("D10").getValue();
  var link = "=HYPERLINK(\"" + url + "\",\"LINK\")";
  playerInfo.splice(5, 0,[link]);
  playerInfo.splice(6, 0,[int.getRange("J37").getValue()]);
  playerInfo.join();
  return playerInfo;
}
function importHid() {
  hid.insertRowsBefore(1, 1);
  var u = 0;
  for (var i = 1; i <= 7; i++) {
    hid.getRange(1, i).setValue(data[u]);
    u++;
  }
}
// Finish: Imports data to HideInterviews sheet
/*---------------------------------------------------------------------------------*/
// Start: Imports data to Academy sheet
function importAca() {
  var formulas = [
    ['=IF(ISERROR(MATCH(D5,Config!C:C,0)),"✘", "✔")'],// 6
    ['=(TODAY()-C5)'], // 31
    ['=IF(AND(H6="✔",K6="✔",N6="✔",Q6="✔",T6="✔",W6="PASSED",AA6>=3),"✔","✘")'] // 32
  ];
  var postions = [2, 1, 3, 0];
  var formulaPostions = [6, 31, 32];
  u = 2
  a = 0
  aca.insertRowsBefore(5, 1);
  for (var i = 0; i <= postions.length - 1; i++) {
    aca.getRange(5, u).setValue(data[postions[i]]);
    u++;
    while (a < 3) {
      aca.getRange(5, formulaPostions[a]).setFormula(formulas[a]);
      Logger.log(a);
      a++;
    }
  }
}
// Finish: Imports data to Academy sheet
/*---------------------------------------------------------------------------------*/