Google-sheets – Add border to whole row if cell in column D is not empty

google sheetsgoogle-apps-script

I was researching how to "Add a border to the whole row if a cell in column D is not empty" – I've found similar script but I failed to edit it to my need!

Below is the script I found – I already allow trigger of the script upon spreadsheet opening but still doesn't work.

Link from original discussion:
Conditionally format row borders

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);
    }
  }
}

Best Answer

  • if a cell in D column isn't empty it paints a border around the whole row
  • if a cell in D column is empty it removes a border around the whole row
function onEdit(ss) {
  var classeur = SpreadsheetApp.getActiveSpreadsheet();
  var ss = classeur.getActiveSheet(); 
  var range = ss.getRange("D1:D1000"); // Modified
  range.setBorder(false, false, false, false, false, false);
  var values = range.getValues();
  var offsetRow = range.getRowIndex(); // Added
  for (var i = 0; i < values.length; i++) {
    if (values[i][0]) { // Modified
      ss.getRange("A" + (i + offsetRow) + ":Z" + (i + offsetRow))
      .setBorder(true, true, true, true, null, null, "black", 
       SpreadsheetApp.BorderStyle.SOLID_MEDIUM) // Modified
      .setBackground('#FFFFFF');
    }
  }
}
  • if a cell in D column isn't empty it paints a border around D cell
  • if a cell in D column is empty it removes a border around D cell
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=4; //4=D
    var colRight=4; //4=D
    var row = sheet.getRange(rowNbr,colLeft, 1, colRight-colLeft+1);
    if(row.isBlank()) {
      row.setBorder(false, false, false, false, null, null, "black", 
                    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    } else {
      row.setBorder(true, true, true, true, null, null, "black", 
                    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  }
}
Related Topic