Conditionally format row borders

Question

I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet.

I'd also like to have the same top border across each row whose whole row is blank.

I understand Conditional Formatting doesn't include border formatting as it does in Excel, and I am as of yet unfamiliar with scripting in this web app.

Would someone be able to explain the process to generate this please?

Answer 1

The process that would generate a script that makes mentioned conditional formatting includes of course what you mentioned you are unfamiliar with: scripting.

A) Learn the basics of google apps script bound to google sheets. This is just a small part of apps script and this is good starting point. Most of the basics can be learned while trying to build your formatting script but you have to want to learn scripting.

B) In the sheets document, open the script editor and define the following function:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;
  var adress= range.getA1Notation();
  Logger.log("change cell %s", adress);
  sheet.getRange('F2').setValue(adress);
}

C) Install a trigger on your spreadsheet that triggers our 'onEdit' function when the sheet is edited. See the official HOWTO. Test that this simple trigger and code works. Also make sure you understand what the code expresses.

D) Write the code corresponding to the logic:

FOR EACH ROW in event.range() {
  IF( your condition for formatting) THEN 
    var myRange= sheet.getRange(rownr, 1,1,nbrOfColumns)
    myRange.setBorder();
  ENDIF
}

Note that the former is not apps script but pseudocode explaining the logic. This translates into the following apps script:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;

  var startingRow=range.getRow();
  var numRows = range.getNumRows();
  for (var i = 1; i <= numRows; i++) {
    var rowNbr=startingRow+i-1;
    Logger.log("changed row %s",rowNbr );
    //Now check the WHOLE row
    var colLeft=1; //1=A
    var colRight=10; //10=J
    var row = sheet.getRange(rowNbr,colLeft, 1, colRight-colLeft+1);
    if(row.isBlank()) {
      row.setBorder(false, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    } else {
      row.setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  }
}

Small variation: iso 1 row the code underneath simply formats a single cell:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;
  var adress= range.getA1Notation();
  Logger.log("changed cell %s", adress);
  sheet.getRange('F2').setValue(adress);

  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var currentValue = range.getCell(i,j).getValue();
      Logger.log("changed cell %s", range.getCell(i,j).getA1Notation() );
      if(currentValue != "") {
        Logger.log("positive on %s", range.getCell(i,j).getA1Notation());
        range.getCell(i,j).setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
      } else {
        range.getCell(i,j).setBorder(false, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
      }
    }
  }
}

Answer 2

Another workaround to create conditionally formatted borders within Google Sheets exists. You can create the conditional formatting rule in Excel, then upload and convert it to Google Sheets format, and it will retain those conditional formatting rules.

It's not a perfect translation every time, and the conditional formatting rules that include borders only live in the background with no way to access or edit them in Google Sheets once they are there.