-2

I would like some help with my script to add a border on cells when the cell has values. And that script will run to all the tabs. Here's my sample sheet https://docs.google.com/spreadsheets/d/1Hq8HJ23c-FHETkoUJifgmrfE3-49PjkmCnmz87SDAKM/edit#gid=0

Here's my sample script:

function onEdit(e) {
    if (e.source.getActiveSheet().getName() !== 'WEEKLY' || e.range.getA1Notation() !== ' ') return;
    checkRange(e.source.getActiveSheet())
}

function onEdit(e) {
    e.range.setBorder(false, false, false, false, false, false);
    if (e.value) e.range.setBorder(true, true, true, true, true, true).setBackground('white');
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
Leo
  • 3
  • 3
  • Why do you have two definitions for the same name? The second definition overwrites the first one. – tehhowch Mar 16 '19 at 20:21
  • 1
    `e.range.getA1Notation() !== ' '` When will e.range.getA1Notation ever equal a space? – Cooper Mar 16 '19 at 20:32
  • To be honest, I not sure. I just tried searching over the internet and stumble into that script. What I'm trying to achieve is to add a border whenever a cell has a value (either text or number). And will run the script on all the tabs. Hope you can help me Cooper. TIA – Leo Mar 16 '19 at 21:59

1 Answers1

0

This one works for me:

function onEdit(e) {
    e.range.setBorder(false, false, false, false, false, false);
    if (e.value) e.range.setBorder(true, true, true, true, true, true).setBackground('white');
}

This one is a little better I think:

function onEdit(e) {
  e.range.setBorder(false, false, false, false, false, false);
  if(e.value && e.value.length>0) e.range.setBorder(true, true, true, true, true, true).setBackground('white');
}

I like this one even better. It even works on a paste with multiple cells.

function onEdit(e) {
  var vA=e.range.getValues();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      e.range.getSheet().getRange(e.range.rowStart + i,e.range.columnStart + j).setBorder(false,false,false,false,false,false);
      if(vA[i][j] && vA[i][j].toString().length>0) {
        e.range.getSheet().getRange(e.range.rowStart + i,e.range.columnStart + j).setBorder(true,true,true,true,true,true).setBackground('white');
      }
    }
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks, I'll try it. :) – Leo Mar 17 '19 at 07:12
  • Hi, Cooper! All script is not working on my end. It gave me an error "TypeError: Cannot read property "range" from undefined. (line 2, file "Code")". Not sure how to resolve this. Please advise – Leo Mar 17 '19 at 22:30
  • If your trying to run the script from the script editor that won't work. Or if you forgot to put the e into onEdit(e) then that won't work either. It needs the event object to function properly. – Cooper Mar 17 '19 at 22:34
  • Actually, the script works when you add a value. But, it doesn't detect the current values or if a new value is added. – Leo Mar 17 '19 at 22:37
  • You can take a look at this [https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas](https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas) if you want to test a trigger by running it from the script editor. I don't do that I just use `e.source.toast('flag');` to figure what's not working. – Cooper Mar 17 '19 at 22:38
  • I believe, you said that you wanted borders when a cell had a value. I presumed that you didn't want the border when the cell has no value. – Cooper Mar 17 '19 at 22:42
  • Thanks, Cooper! I'll check and monitor the sheet. Have a great day. :) – Leo Mar 17 '19 at 22:44