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.

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, May 18 '21 at 22:16
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, May 19 '21 at 13:34

Cooper · Answer 1 · 2021-05-18T19:10:29.937

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

you've done a great job. But the script in the question works noticeably faster. — Yuri Khristich, May 18 '21 at 17:03
Thanks MetaMan - from your demonstration that looks like exactly the functionality I'm looking for. However, when I try to run your script I get an error as follows "TypeError: Cannot read property 'range' of undefined: onEdit @ Code.gs:2" in relation to line 2. I don't understand enough about the error to straighten out whatever the problem is. — ianm12, May 18 '21 at 17:34
I just found why your script works so slow. It's here: `sh.getRange(i + sr, 1, 1, sh.getLastColumn()).setBorder(...`. You shouldn't call `getLastColumn()` for each line. It would be better to get the last column just once outside of `forEach()` and then to use it as a constant. — Yuri Khristich, May 18 '21 at 18:10
Wow that was a stupid mistake and wow it really does run a lot faster now. Thanks — Cooper, May 18 '21 at 18:35

Yuri Khristich · Answer 2 · 2021-05-18T17:02:03.507

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

Thanks very much for your help with this. However, the reason I hadn't realised that my script was close to working was because it only works with numbers. Is there a way to get the script to add borders to the row when column A contains *any* text? — ianm12, May 18 '21 at 17:30
I think it's here: `if (testvalues[i] > 0) { // test applied to array of values`. You need just to change the condition this way `if (testvalues[i] !="") { ` — Yuri Khristich, May 18 '21 at 17:37

score 0 · Answer 3 · answered Dec 23 '21 at 06:04

0

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

answered Dec 23 '21 at 06:04

Mike Steelson

14,650
2
5
20

Conditional formatting of borders in Google Sheets using Apps Script

3 Answers3