如何在google script函数中忽略空单元格

Question

我试图创建一个函数，突出显示任何单元格，当它们被改变时，忽略对空单元格的任何更改，下面的函数确实突出显示更改，但对包括空单元格在内的所有单元格，任何帮助将不胜感激。

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

我尝试了一堆不同的if语句来尝试过滤空单元格，但没有改变结果

Answer 1

利用事件对象e，如下所示:

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

参见Apps Script在Stack Overflow, Clean Code JavaScript和这些onEdit(e)优化技巧。

Answer 2

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