Conditional formatting of borders in Google Sheets using Apps Script

Question

I would like to add borders to cells in a Google Sheet using conditional formatting. I am aware that you cannot do this using the standard conditional formatting process in Google Sheets so I'm trying to get to grips with how to do it using a script.

I have copied a script from the following solution, and attempted to edit it for my needs: (Add border format to row if condition met in Google Sheets)

However, I am still coming to terms with how these scripts work and haven't yet been able to make this work as desired.

The desired effect is that for all rows 5 and higher, where A is not null, a border should be applied to all cells in columns A to M. The sheet is called 'Kit check list', and the script should be triggered any timean edit is made to the sheet.

Here is the my attempt so far

function onEdit() {
   GroupMyData(); // trigger this function when edits are made
}

function GroupMyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Kit check list'); // apply to sheet name only
  var rows = sheet.getRange('A5:M'); // range to apply formatting to
  var numRows = rows.getNumRows(); // no. of rows in the range named above
  var values = rows.getValues(); // array of values in the range named above
  var testvalues = sheet.getRange('a5:a').getValues(); // array of values to be tested (1st column of the range named above)

  rows.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID); // remove existing borders before applying rule below
      //Logger.log(numRows);

  for (var i = 0; i <= numRows - 1; i++) {
      var n = i + 1;
      //Logger.log(n);
      //Logger.log(testvalues[i] > 0);
      //Logger.log(testvalues[i]);
      if (testvalues[i] > 0) { // test applied to array of values
        sheet.getRange('a' + n + ':m' + n).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); // format if true
      }
  }
};

Unfortunately all it only resets the borders in the specified area, and does not apply a borders to the desired rows.

Any help with this would be much appreciated.

Answer 1

without any script, you can do it by first using conditional formatting in MS Excel, then importing the workbook into google sheets ! weird ...

Answer 2

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Kit check list') {
    const sr = 5;
    const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn());
    const vs = rg.getValues();
    rg.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    const numcolumns = sh.getLastColumn();
    vs.forEach((r, i) => {
      if (r[0]) {
        sh.getRange(i + sr, 1, 1, numcolumns).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
      }
    });
  }
}

Demo:

Note: you cannot run this function without providing the event object which populates the e. The only reasonable way to test it is to set it up and save it and edit the sheet.

You might actually like it better this way:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Kit check list') {
    const sr = 5;
    const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn());
    const vs = rg.getValues();
    //rg.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    const numcolumns = sh.getLastColumn();
    vs.forEach((r, i) => {
      if (r[0]) {
        sh.getRange(i + sr, 1, 1, numcolumns).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
      } else {
        sh.getRange(i + sr, 1, 1, numcolumns).setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
      }
    });
  }
}

Answer 3

Your script works fine. I just fixed one line.

Instead of this:

var n = i + 1;

You need:

var n = i + 5;

Here is the code:

function onEdit() {
   GroupMyData(); // trigger this function when edits are made
}

function GroupMyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Kit check list'); // apply to sheet name only
  var rows = sheet.getRange('A5:M'); // range to apply formatting to
  var numRows = rows.getNumRows(); // no. of rows in the range named above
  var values = rows.getValues(); // array of values in the range named above
  var testvalues = sheet.getRange('A5:A').getValues(); // array of values to be tested (1st column of the range named above)

  rows.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID); // remove existing borders before applying rule below

  for (var i=0; i <= numRows-1; i++) {
      var n = i + 5;
      //Logger.log(n);
      //Logger.log(testvalues[i] > 0);
      //Logger.log(testvalues[i]);
      if (testvalues[i] > 0) { // test applied to array of values
        sheet.getRange('A' + n + ':M' + n).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); // format if true
      }
  }
};