20190609 Lock Output cell LODSCRIPT

//https://stackoverflow.com/questions/56323053/how-to-lock-the-result-of-the-output-cell-once-it-meets-a-criteria-from-subseq

//https://docs.google.com/spreadsheets/d/1UjjMnLmNbEDUfXjeCIOgBZwYqTeXdm_t_4N45OL0k6I/edit#gid=0

//https://youtu.be/DEI7RGRotcs

/**
* Locks the Status of a cell according to criteria and Colors and bolds the specific words of status
*
* @param arg3:  Colors "VALID" Status to orange, "PENDING" Status to green, and "APPROVED" to purple
*
* @customfunction
*/


function LOCKVALUECOLORBOLDWORDS(arg3) {

      var app = SpreadsheetApp;
      var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

      //(read left to right) get the value from range A1:A1, from active sheet, and assign it the variable "fixedvalueCell"
      var fixedvalueCell = activeSheet.getRange(1, 1).getValue();

      //(read left to right) get the value from range B1:B1, from active sheet, and assign it the variable "changingvalueCell"
      var changingvalueCell = activeSheet.getRange(1, 2).getValue();

      //(read left to right) get the value from range C1:C1, from active sheet, and assign it the variable "approveCell"
      var approveCell = activeSheet.getRange(1, 3).getValue();

      //(read left to right) get the value from range D1:D1, from active sheet, and assign it the variable "statusCell"
      var statusCell = activeSheet.getRange(1, 4).getValue();




      //[1.] if the value of approveCell is not-blank, then set/print "APPROVED" into D1, and color it to purple, and bold it.
      if
        (approveCell !== "") {
        activeSheet.getRange(1, 4).setValue("APPROVED").setFontColor("#7F00FF").setFontWeight("bold");
      }


      //[2.] if the value of changingvalueCell is smaller or equal to the value of fixedvalueCell, then set/print "PENDING" into D1, and color it to green, and bold it.
        else if
        (changingvalueCell <= fixedvalueCell) {
        activeSheet.getRange(1, 4).setValue("PENDING").setFontColor("#7FFF00").setFontWeight("bold");
      }


      //[3.] if the value of statusCell is "PENDING", then set/print "PENDING" into D1, and color it to green, and bold it.
        else if
        (statusCell == "PENDING") {
        activeSheet.getRange(1, 4).setValue("PENDING").setFontColor("#7FFF00").setFontWeight("bold");
      }


      //[4.] if all [1.] and [2.] and [3.] IF statements are FALSE (for everything else), then set/print "VALID" into D1, and color it to orange, and bold it.
        else {
        activeSheet.getRange(1, 4).setValue("VALID").setFontColor("#FF6600").setFontWeight("bold");
      }


      var result = arg3;

      return result;

    }