JavaScript for Google Sheet to track job Search

JobSearchGoogleSheet.js

/**
 * Google Sheet: 
 * https://docs.google.com/spreadsheets/d/1rkPwzZIE60Tjdk-Rq18tewy48vdpWk1dSD3wr4wp81w/copy
 * 
 * For more info see: 
 * https://medium.com/@hisa_py/google-sheet-to-track-job-search-68c88c699d33
 */
// globals
var STATUS_COLUMN = 12;

/**
 * Salary can be a string as 50,000(USD) or a range 
 * like 100k-160k(EUR) from which the result would be 100000
 */
function parseSalary(salary){
  if (salary === "") return 0;
  
  // range
  if (salary.indexOf("-") > 0) {
    var base = salary.substring(0, salary.indexOf("k")).replace(/\D/g, '');
    return base * 1000;
  }
  
  // just a number
  return parseInt(salary.replace(/\D/g, ''));
}

/**
 * Remote is better
 */
function compLocation(aLocation, bLocation) {
  if (aLocation === bLocation) return 0;
  
  if (aLocation.indexOf("Remote") > -1) {
    return -1;
  }

  if (bLocation.indexOf("Remote") > -1) {
    return 1;
  }
  
  return aLocation.localeCompare(bLocation);
}

/**
 * Elixir is better
 */
function compElixir(aElixir, bElixir) {
  if (aElixir === bElixir) return 0;
  
  if (aElixir) {
    return -1
  }
  
  return 1
}

/**
 * Sort based on Status, Elixir?, Salary and Location
 */
function statusBestJobSort(sheet){
  var statusList = ['VIEWED', 'APPLIED', 'IN PROCESS', 'STALLED', 'REJECTED', ''];

  // Range from table's first data row until last row with data in the spreadsheet
  var tableRange = "A8:P"+(sheet.getLastRow()+1);
  var range = sheet.getRange(tableRange);
  var data = range.getValues();  

  var ordered = data.sort(function(a, b){
    // Rows without company or title are considered empty and will go to the bottom 
    if (!a[0] || !a[1]) {
      return 1;
    }
    
    // The 1st order criteria is status in the order given in statusList
    // need -1 because in JavaScript col index starts at 0
    var aStatus = statusList.indexOf(a[STATUS_COLUMN-1]);
    var bStatus = statusList.indexOf(b[STATUS_COLUMN-1]);
    
    // The 2nd order criteria: Elixir?
    var elixirCol = 3
    var aElixir = a[elixirCol];
    var bElixir = b[elixirCol];
    
    // The 3rd order criteria is the salary (Greater base is better)
    var salaryCol = 10;
    var aSalary = parseSalary(a[salaryCol]);
    var bSalary = parseSalary(b[salaryCol]);
    
    // The 4th order criteria is location (Remote is better)
    var locationCol = 4;
    var aLocation = a[locationCol];
    var bLocation = b[locationCol];
    
    
    return aStatus - bStatus || compElixir(aElixir, bElixir) || bSalary - aSalary || compLocation(aLocation, bLocation);
  });
  
  
  range.setValues(ordered);
}

/**
 * This function is triggered each time a cell is updated in the spreadsheet
 */
function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  if(editedCell.getColumn() == STATUS_COLUMN){   
    statusBestJobSort(sheet);
  }
}