Add border to whole row if cell in column D is not empty

Question

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

Answer 1

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

Answer 2

I thought about your sheet and your script, and I think this would be a much better approach,

create one master sheet with drop downs and better formulas to work out your daily values, add a date column via script or from a picker for your team to use.

Then, I would suggest using a google query on the "totals" with a pivot on a date which you can transpose. As Ruben has pointed out the question is too open-ended.

if you need any assistance I'll be more than happy to help a fellow Brit : )

my short attempt at your sheet

Answer 3

I found this post and the answer very helpful for what I wanted to do...I modified the code a little and learnt a bit too! This will remove the border around entire row.

   function Border() {
  var classeur = SpreadsheetApp.getActiveSpreadsheet();
  var ss = classeur.getActiveSheet(); 
  var range = ss.getRange("T4:T"); // Range that gets modified, and has a value in to trigger formatting.
  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) + ":BH" + (i + offsetRow)) //Range to get formated
      .setBorder(true, null, null, null, null, null, "black", 
       SpreadsheetApp.BorderStyle.SOLID) // Modified
      .setBackground('#FFFFFF');
    } else {
      ss.getRange("A" + (i + offsetRow) + ":BH" + (i + offsetRow)) //Range to get formated
      .setBorder(false, null, null, null, null, null, "black", 
                    SpreadsheetApp.BorderStyle.SOLID)
      .setBackground('#FFFFFF');
  }
}
}