Conditional formatting of borders in Google Sheets using Apps Script

0
votes

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.

2
google-apps-scriptgoogle-sheetsspreadsheetconditional-formatting
And I'd advise you to consider the use of background colors instead of borders. If it's possible, of course. With Google App Script you can handle backgrounds much easier than borders. For now at least. - Yuri Khristich
Thanks for the suggestion of colour instead of borders. I don't use borders very much in the spreadsheets I make, however in this case the spreadsheet is for generating a printable form with a grid of fields for people to write in, so borders are much more appropriate. - ianm12

2 Answers

3
votes

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:

enter image description here

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

enter image description here

1
votes

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

enter image description here