0

I'm trying to create a function that highlights any cells when they are changed ignoring any changes to empty cells, the function below does highlight changes but to ALL cells including empty ones, any help would be greatly appreciated.

function onEdit() {

var sheetsToWatch = ['IC', 'FTE'];

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = sheet.getActiveCell();
var val = cell.getValue();


var sheetName = sheet.getName();
var matchFound = false;
for (var i = 0; i < sheetsToWatch.length; i++) {
if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  } 

if (val && matchFound){

var rowColLabel = 
sheet.getRange(cell.getRow(),cell.getColumn()).setBackground('#faec15');

I tried a bunch of different if statements to try filter empty cells but nothing changes the result

Rubén
  • 34,714
  • 9
  • 70
  • 166
gydex95
  • 1
  • 1
  • Hi, welcome. You need to take advantage of the Event Objects by using `onEdit(e)`, then you can get the value of `oldvalue` (i.e the value before the edit) as described in [onEdit Event object access to previous value of a cell that has been modified?](https://stackoverflow.com/a/71014364/1330560). Then, test for the value as described here [How to check if the cell is empty, if so skip it](https://stackoverflow.com/a/71880158/1330560). – Tedinoz Dec 11 '22 at 10:16

2 Answers2

0

Take advantage of the event object e, like this:

function onEdit(e) {
  if (!e) {
    throw new Error('Do not run this code in the script editor.');
  }
  if (!e.oldValue
    || !e.range.getSheet().getName().match(/^(IC|FTE)$/i)) {
    return;
  }
  e.range.setBackground('#faec15');
}

See Apps Script at Stack Overflow, Clean Code JavaScript, and these onEdit(e) optimization tips.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • thanks but whenever I try work with oldValue i'm getting TypeError: Cannot read properties of undefined (reading 'oldValue') – gydex95 Dec 12 '22 at 14:44
  • The `onEdit(e)` function is a [simple trigger](https://developers.google.com/apps-script/guides/triggers). It runs automatically when you manually edit the spreadsheet. Do not run the code in the script editor. If you do, the event object `e` is not populated, causing the error you mention. – doubleunary Dec 12 '22 at 17:44
-1

some ways to ignore empty data in apps-script:

// 1. assume you have one column of data:

  // if your data may contains 0 or false:
function test1() {
  const values = [
    ['value_1'],
    [0],
    ['value_2'],
    [false],
    ['value_3'],
    [''],
    ['value_4']
  ]

  for(const row of values) {
    if(row[0]==undefined || row[0]=='') continue;
    console.log(row);
  }
}

function test2() {
  // if your data do not contains 0 or false:
  const values = [
    ['value_1'],
    [''],
    ['value_2'],
    [''],
    ['value_3'],
    [''],
    ['value_4']
  ]

  for(const row of values) {
    if(!row[0]) continue;
    console.log(row);
  }
}

test1()
/** output:
["value_1"]
["value_2"]
["value_3"]
["value_4"]
*/

test2()
/** output:
["value_1"]
["value_2"]
["value_3"]
["value_4"]
*/

// 2. Assume you have a set rows and columns as data:
  // Assume all the test samples below does not contains 0 or false:
function test3(arg) {
  const values = [
    ['value_1-1','value_1-2','value_1-3'],
    ['','value_2-2','value_2-3'],
    ['value_3-1','','value_3-3'],
    ['value_4-1','value_4-2',''],
    ['','',''],
    ['value_5-1','value_5-2','value_5-3']
  ];
  
  switch(arg) {
    case 1: // 2-1. if you only want to check one column:
      {
        for(row of values) {
          if(!row[1]) continue; // check only column B of each row.
          console.log(row);
        }
      }
      break;
    case 2: // 2-2. if you need to check the whole row and skip only if the entire row is empty:
    {
      for(row of values) {
       if(row.every(col => !col)) continue; // check every columns of each row.
       console.log(row);
      }
    }
    break;
    case 3: // 2-3. if you need to check the whole row and skip if that row contains 1 or more empty cell:
    {
      for(row of values) {
       if(row.some(col => !col)) continue; // check every columns of each row.
       console.log(row);
      }
    }
    break;
  }
}

test3(1)
/** output:
["value_1-1","value_1-2","value_1-3"]
["","value_2-2","value_2-3"]
["value_4-1","value_4-2",""]
["value_5-1","value_5-2","value_5-3"]
*/

test3(2)
/** output:
["value_1-1","value_1-2","value_1-3"]
["","value_2-2","value_2-3"]
["value_3-1","","value_3-3"]
["value_4-1","value_4-2",""]
["value_5-1","value_5-2","value_5-3"]
*/

test3(3)
/** output:
["value_1-1","value_1-2","value_1-3"]
["value_5-1","value_5-2","value_5-3"]
*/
Ping
  • 891
  • 1
  • 2
  • 10