Google Sheets converting Conditional Formatting to Apps Scripts

Question

I'm sure that there's not an easy way to do this, but I figured I would ask. I have a sheet that has a decent amount of conditional formatting on it, however, people that I share the sheet with have been copy/pasting and moving rows which aggravatingly disrupts the formatting. I am trying to convert to Scripting for them. The main thing is background colors based on other cell information.

ie: if Any cell from row 4 from column A-D has information in it then the K4 will be red. Once K4 has information inside it then the background will be white.

The conditional formatting I have is: =AND(OR(ISBLANK(A4)=FALSE,ISBLANK(B4)=FALSE,ISBLANK(C4)=FALSE,ISBLANK(D4)=FALSE),ISBLANK(K4)=TRUE).

The other is that when any cell has a certain term "LWOS" then the whole row has a purple background and strikethrough.

Thanks in advance.

This might be helpful - developers.google.com/sheets/api/quickstart/apps-script. You can use the values from cells and check if it contains anything, and if it does, just highlight it. — Endothermic_Dragon

NaziA NaziA · Accepted Answer · 2021-01-25T18:25:32

I know scripting is hard when you are new to it, so I understand you and decided I'm going to give you a code that should work with yours. And please do follow Endothermic_Dragon's recommendation into studying Apps Script if you want to learn more.

Code:

function onEdit(e) {
  // Get active spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  // Get row and column of the current cell edited
  var editRow = e.range.getRow();
  var editColumn = e.range.getColumn();

  // If edited cell is below row 4:
  if(editRow > 3) { 
    // Get row and K cell for checking values later
    var rowRange = sheet.getRange("A" + editRow + ":D" + editRow)
    var rowValues = rowRange.getValues();
    var kCell = sheet.getRange("K" + editRow);
    var kValue = kCell.getValue();

    var rowHasValue = rowValues[0].some(el => !!el);
    var rowHasLWOS = rowValues[0].includes("LWOS");
    var kHasValue = kValue != "";

    // If edited column is in A-D
    if(editColumn < 5) { 
      // If we see a value on that row and K row doesnt have value 
      if(rowHasValue && !kHasValue){
        // Set K cell to red
        kCell.setBackground("red");
      }

      // If columns A-D of the edited row has LWOS 
      // (cell value is "LWOS", not cell containing LWOS)
      if (rowHasLWOS){ 
        // Set row to purple and strikethrough
        sheet.getRange(editRow + ":" + editRow).setBackground("purple").setFontLine("line-through");
      }
    }
    // If edited column is K
    if(editColumn == 11) { 
      // If row doesnt have LWOS, proceed with checking the K value
      if(!rowHasLWOS) {
        // If edited value of K has value
        if(kHasValue) { 
          // Set K to white
          sheet.getRange("K" + editRow).setBackground("white");
        }
        // If K value was deleted and A-D has values
        if(rowHasValue && !kHasValue) { 
          // Set K back to red
          sheet.getRange("K" + editRow).setBackground("red");
        }
      }
    }
  }
}

Output:

This has comments on it so hopefully you can at least understand what the code does and how it works.

For more details, check the references you can study below.

References:

Google Sheets converting Conditional Formatting to Apps Scripts

1 Answers