gapps_scripts.js

function calcDateFromDuration() {
  var today = parseInt(new Date()/1000)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ["2F-12-18-1", "2F-12-18-2"]
  
  for (var sheet_idx in sheets) {
    //Browser.msgBox(sheet_idx);
    var sheet = ss.getSheetByName(sheets[sheet_idx])
    var range = sheet.getRange("I2:I60")
    var values = range.getValues()
    for (var i in values) {
      var duration = Math.round((today - parseInt(new Date(values[i])/1000)) / 86400)
      if (!isNaN(duration)) {
        var cell = sheet.getRange("J"+(parseInt(i)+2))
        cell.setValue(duration)
      }
    }
  }
}

function setBackgroundColorByValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ["2F-12-18-1", "2F-12-18-2"]
  
  for (var sheet_idx in sheets) {
    var sheet = ss.getSheetByName(sheets[sheet_idx]);
    var range = sheet.getRange("J2:J60")
    var values = range.getValues()
    for (var i in values) {
      if (values[i] == "-") continue;
      var result = Math.floor(parseInt(values[i]) / 365)
      var idx = parseInt(i) + 2;
      var color = calcColor(result)
      var row = sheet.getRange("A"+idx+":AH"+idx)
      row.setBackground(color)
    }
  }
}

function calcColor(year) {
  var color = "";
  year = parseInt(year)
  switch(year) {
    //case 1: color = "#dc6e3b"; break;
    //case 2: color = "#dc613b"; break;
    case 3: color = "#dc963b"; break;
    case 4: color = "#dc813b"; break;
    case 5: color = "#dc6e3b"; break;
    case 6: color = "#dc563b"; break;
    case 7: color = "#dc493b"; break;
    case 8: color = "#dc433b"; break;
    case 9: color = "#dc3e3b"; break;
    case 10: color = "#dc3b3b"; break;
    case 11: color = "#dc3b3b"; break;
    case 12: color = "#dc3b3b"; break;
    case 13: color = "#dc3b3b"; break;
    case 14: color = "#dc3b3b"; break;
    case 15: color = "#dc3b3b"; break;
    default: break;
  }
  return color
}