How to ignore empty cell in google script function-CodePudding

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

CodePudding user response：

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"]
*/

CodePudding user response：

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.