var API_KEY = "";

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Integrate')
  .addItem('Get Requests', 'getRequest')//x
  .addItem('Get Position', 'getPosition')//
  .addItem('Get Progress', 'getProgress')//x
  .addItem('Get Department', 'getDept')//x
  .addItem('Get Employee', 'getEmp')//x
  .addToUi();
}

//========================================================== REQUEST ====================================================================
function getRequest() {//x
  getRequestNext(null, 0);
}

function getRequestNext(offset, page){
  var response = "";
  if(!offset)
  {
    response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/Request?api_key='+API_KEY);
    clean("Requests");
  }
  else 
    response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/Request?api_key='+API_KEY+"&offset="+offset);
  var data = JSON.parse(response.getContentText());
  fillToRequest(data.records, page);
  if(data.offset){
    getRequestNext(data.offset, page+1);
  }
}

function fillToRequest(records, page){
  var data = []; 
  var n = records.length;
  var r;
  for(var i = 0; i < n; i++)
  {
    r = records[i].fields;
    
    //
    var position = "";
    if(typeof r["Position"] !== 'undefined')
    {
      position = r["Position"][0];
      for(var j = 1; j < r["Position"].length; j++)
      {
        position += "," + r["Position"][j];
      }
    }
    
    //
    var progress = "";
    if(typeof r["Progress"] !== 'undefined')
    {
      progress = r["Progress"][0];
      for(var j = 1; j < r["Progress"].length; j++)
      {
        progress += "," + r["Progress"][j] ;
      }
    }
    
    //
    var department = "";
    if(typeof r["Department"] !== 'undefined')
    {
      department = r["Department"][0];
      for(var j = 1; j < r["Department"].length; j++)
      {
        department += "," + r["Department"][j] ;
      }
    }
    
    //
    var division = "";
    if(typeof r["Division"] !== 'undefined')
    {
      division = r["Division"][0];
      for(var j = 1; j < r["Division"].length; j++)
      {
        division += "," + r["Division"][j] ;
      }
    }
    
    //
    var requester = "";
    if(typeof r["Requester"] !== 'undefined')
    {
      requester = r["Requester"][0];
      for(var j = 1; j < r["Requester"].length; j++)
      {
        requester += "," + r["Requester"][j] ;
      }
    }
    
    //
    var candidateInfo = "";
    if(typeof r["CandidateInfo"] !== 'undefined')
    {
      candidateInfo = r["CandidateInfo"][0];
      for(var j = 1; j < r["CandidateInfo"].length; j++)
      {
        candidateInfo += "," + r["CandidateInfo"][j] ;
      }
    }
    
    //
    var leadtimeByPosition = "";
    if(typeof r["Leadtime by Position"] !== 'undefined')
    {
      leadtimeByPosition = r["Leadtime by Position"][0];
      for(var j = 1; j < r["Leadtime by Position"].length; j++)
      {
        leadtimeByPosition += "," + r["Leadtime by Position"][j] ;
      }
    }
    
    //
    
    data.push([records[i].id, 
               (typeof r["Request Code"] === 'undefined'? '': r["Request Code"]), position,
               (typeof r["Quantity"] === 'undefined'? '': r["Quantity"]),
               (typeof r["Rercruitment type"] === 'undefined'? '': r["Rercruitment type"]),
               (typeof r["Request Day"] === 'undefined'? '': r["Request Day"]),
               (typeof r["Job type"] === 'undefined'? '': r["Job type"]),
               (typeof r["Recruitment Status"] === 'undefined'? '': r["Recruitment Status"]), progress,department, division,requester,candidateInfo,
               (typeof r["Leadtime by Req"] === 'undefined'? '': r["Leadtime by Req"]),
               (typeof r["Team TA"] === 'undefined'? '': r["Team TA"]),
               (typeof r["Mass-Office"] === 'undefined'? '': r["Mass-Office"]), leadtimeByPosition,
               (typeof r["Deadline"] === 'undefined'? '': r["Deadline"]),
               (typeof r["Offered Number"] === 'undefined'? '': r["Offered Number"]),
               (typeof r["Candidate Number"] === 'undefined'? '': r["Candidate Number"]),
               (typeof r["Onboard Number"] === 'undefined'? '': r["Onboard Number"]),
               (typeof r["R1 - Interview Number"] === 'undefined'? '': r["R1 - Interview Number"]),
               (typeof r["R2 - Interview Number"] === 'undefined'? '': r["R2 - Interview Number"]),
               (typeof r["R3 - Interview Number"] === 'undefined'? '': r["R3 - Interview Number"]),
               (typeof r["R1 - Pass Number"] === 'undefined'? '': r["R1 - Pass Number"]),
               (typeof r["R2 - Pass Number"] === 'undefined'? '': r["R2 - Pass Number"]),
               (typeof r["R3 - Pass Number"] === 'undefined'? '': r["R3 - Pass Number"]),
               (typeof r["Direct - Indirect"] === 'undefined'? '': r["Direct - Indirect"]),
               (typeof r["PIC"] === 'undefined'? '': r["PIC"]),
               (typeof r["Completed Day"] === 'undefined'? '': r["Completed Day"]),
               (typeof r["No count"] === 'undefined'? false: r["No count"])
              ]);
  }
  
  var sheet = SpreadsheetApp.getActive().getSheetByName("Requests");
  var range = sheet.getRange("A"+(page*100+2)+":AE"+(page*100 + data.length + 1));
  range.setValues(data);
  
}

//======================================================== PROGRESS ======================================================================
function getProgress() {//x
  getProgressNext(null, 0);
}

function getProgressNext(offset, page){
  var response = "";
  if(!offset)
  {
    response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/Progress?api_key='+API_KEY);
    clean("Progress");
  }
  else 
    response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/Progress?api_key='+API_KEY+"&offset="+offset);
  var data = JSON.parse(response.getContentText());
  fillToProgress(data.records, page);
  if(data.offset){
    getProgressNext(data.offset, page+1);
  }
}

function fillToProgress(records, page){
  var data = []; 
  var dataFunctions = [];
  var n = records.length;
  var r;
  for(var i = 0; i < n; i++)
  {
    r = records[i].fields;
    data.push([records[i].id, 
               (typeof r["Name"] === 'undefined'? '': r["Name"]),
               (typeof r["Request"] === 'undefined'? '': r["Request"]),
               (typeof r["Round Interview"] === 'undefined'? '': r["Round Interview"]),
               (typeof r["Contact"] === 'undefined'? '': r["Contact"]),
               (typeof r["Interview"] === 'undefined'? '': r["Interview"]),
               (typeof r["Interview Date"] === 'undefined'? '': r["Interview Date"]),
               (typeof r["Result"] === 'undefined'? '': r["Result"]),
               (typeof r["R1 - No. Interview"] === 'undefined'? '': r["R1 - No. Interview"]),
               (typeof r["R2 - No. Interview"] === 'undefined'? '': r["R2 - No. Interview"]),
               (typeof r["R3 - No. Interview"] === 'undefined'? '': r["R3 - No. Interview"]),
               (typeof r["R1 - No. Pass Result"] === 'undefined'? '': r["R1 - No. Pass Result"]),
               (typeof r["R2 - No. Pass Result"] === 'undefined'? '': r["R2 - No. Pass Result"]),
               (typeof r["R3 - No. Pass Result"] === 'undefined'? '': r["R3 - No. Pass Result"]),
               (typeof r["Offer"] === 'undefined'? '': r["Offer"]),
               (typeof r["Offer Date"] === 'undefined'? '': r["Offer Date"]),
               (typeof r["No. Offer"] === 'undefined'? '': r["No. Offer"]),
               (typeof r["Onboard"] === 'undefined'? '': r["Onboard"]),
               (typeof r["Onboard Date"] === 'undefined'? '': r["Onboard Date"]),
               (typeof r["No. Onboard"] === 'undefined'? '': r["No. Onboard"]),
               (typeof r["Dept"] === 'undefined'? '': r["Dept"]),  
               (typeof r["TA"] === 'undefined'? '': r["TA"])
              ]);
     dataFunctions.push(["MAXIFS($S$2:$S,$C$2:$C,C"+(page*100+i+2)+")"]);
  }
  
  var sheet = SpreadsheetApp.getActive().getSheetByName("Progress");
  var notation = "A"+(page*100+2)+":V"+(page*100 + data.length + 1);
  var range = sheet.getRange(notation);
  range.setValues(data);
  
  sheet.getRange("AN"+(page*100+2)+":AN"+(page*100 + data.length + 1)).setFormulas(dataFunctions);
}

//=================================================== POSITION ===========================================================================

function getPosition() {//x
  var response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/POSITION?api_key='+API_KEY);
  //Logger.log(response.getContentText());
  clean("Position");
  fillToPosition(JSON.parse(response.getContentText()).records);
}

function fillToPosition(records){
  var data = []; 
  var n = records.length;
  var r;
  for(var i = 0; i < n; i++)
  {
    r = records[i].fields;
    data.push([records[i].id, 
               (typeof r["Name"] === 'undefined'? '': r["Name"]),
               (typeof r["Name Vi"] === 'undefined'? '': r["Name Vi"]),
               (typeof r["Level"] === 'undefined'? '': r["Level"]),
               (typeof r["Leadtime"] === 'undefined'? '': r["Leadtime"])
              ]);
  }
  
  var sheet = SpreadsheetApp.getActive().getSheetByName("Position");
  var notation = "A2:E"+(data.length + 1);
  var range = sheet.getRange(notation);
  range.setValues(data);
  
}

//================================================ DEPARTMENT ==============================================================================

function getDept() {//x
  var response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/Department?api_key='+API_KEY);
  //Logger.log(response.getContentText());
  clean("Department");
  fillToDept(JSON.parse(response.getContentText()).records);
}

function fillToDept(records){
  var data = []; 
  var n = records.length;
  var r;
  for(var i = 0; i < n; i++)
  {
    r = records[i].fields;
    data.push([records[i].id, 
               (typeof r["Name"] === 'undefined'? '': r["Name"]),
               (typeof r["Parent"] === 'undefined'? '': r["Parent"]),
               (typeof r["Type"] === 'undefined'? '': r["Type"]),
               (typeof r["Name Vi"] === 'undefined'? '': r["Name Vi"]),
               (typeof r["Name En"] === 'undefined'? '': r["Name En"])
              ]);
  }
  
  var sheet = SpreadsheetApp.getActive().getSheetByName("Department");
  var notation = "A2:F"+(data.length + 1);
  var range = sheet.getRange(notation);
  range.setValues(data);
  
}

//================================================ EMPLOYEE ==============================================================================

function getEmp() {//x
  var response = UrlFetchApp.fetch('https://api.airtable.com/v0/tMlyHvTph/Employee?api_key='+API_KEY);
  //Logger.log(response.getContentText());
  clean("Employee");
  fillToEmp(JSON.parse(response.getContentText()).records);
}

function fillToEmp(records){
  var data = []; 
  var n = records.length;
  var r;
  for(var i = 0; i < n; i++)
  {
    r = records[i].fields;
    data.push([records[i].id, 
               (typeof r["Name"] === 'undefined'? '': r["Name"]),
               (typeof r["ID"] === 'undefined'? '': r["ID"])
              ]);
  }
  
  var sheet = SpreadsheetApp.getActive().getSheetByName("Employee");
  var notation = "A2:C"+(data.length + 1);
  var range = sheet.getRange(notation);
  range.setValues(data);
  
}

//=======================================================================

function clean(sheetName){
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  switch(sheetName){
    case "Requests":
      sheet.getRange("A2:AE").clear();
      break;
      case "Progress":
      sheet.getRange("A2:V").clear();
      sheet.getRange("AN2:AN").clear();
      break;
      case "Position":
      sheet.getRange("A2:E").clear();
      break;
      case "Department":
      sheet.getRange("A2:E").clear();
      break;
      case "Employee":
      sheet.getRange("A2:C").clear();
      break;
  }
  
}