1

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);
    }
  }
}
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);
    }
  }
}
| improve this answer | |
0

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

| improve this answer | |
0

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');
  }
}
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.