将单元格与数组进行比较，并在应用程序脚本中返回值

Question

我正在尝试创建一个应用程序脚本，将单元格值与数组进行比较，其中D列有值(task1、task2…task6(，每个任务都有一个级别1或2，因此如果D2列有值"；任务1"；那么它是L1，并且该值设置在O列的相应行中，看起来我可能在这方面犯了几个错误，因为我没有得到我想要的结果，任何帮助都将不胜感激。

function fillValues() {
var spreadsheet = SpreadsheetApp.openById().getSheetByName()
var task = spreadsheet.getRange("D2:D" + spreadsheet.getLastRow())
var taskLevel = spreadsheet.getRange("O2:O" + spreadsheet.getLastRow())
var newRange = []
var newFillValue
task.getValues().map(function(value) {
var L1Tasks = ["task1", "task2", "task3"];
var L2Tasks = ["task4","task5", "task6"];
for (var i = 0; i < L1Tasks.length; i++){ 
if (value[0]==L1Tasks[i]) { 
newFillValue="L1" 
newRange.push([newFillValue]) }
else { newFillValue="L2" newRange.push([newFillValue]) } 
} 
}) 
taskLevel.setValues(newRange) 
}

Answer 1

我还没有测试过。只是猜测。可能应该是这样的：

function fillValues() {
var spreadsheet = SpreadsheetApp.openById('###').getSheetByName('xxx'); // <--- your ID and sheet name should be here
var task = spreadsheet.getRange("D2:D" + spreadsheet.getLastRow());
var taskLevel = spreadsheet.getRange("O2:O" + spreadsheet.getLastRow());
var newRange = taskLevel.getValues(); // <-- fill the array with values from the column
var newFillValue;
task.getValues().forEach((value,v) => { // <-- the counter 'v'
var L1Tasks = ["task1", "task2", "task3"];
// var L2Tasks = ["task4", "task5", "task6"]; // <-- doesn't need?
for (var i = 0; i < L1Tasks.length; i++) {
if (value[0] == L1Tasks[i]) {
newFillValue = "L1";
newRange[v] = [newFillValue]; // <-- assign the value
}
else {
newFillValue = "L2";
newRange[v] = [newFillValue]; // <-- assign the value
}
}
})
taskLevel.setValues(newRange);
}

它可以做得更短。例如，您可以使用if (L1Tasks.includes(value[0]))代替冗长循环等。

缩短版在这里：

function fillValues() {
var sheet = SpreadsheetApp.openById('###').getSheetByName('xxx');  // <--- paste your ID and sheet name
var tasks = sheet.getRange('D2:D' + sheet.getLastRow()).getValues().flat();
var levels_range = sheet.getRange('O2:O' + sheet.getLastRow());
var levels = levels_range.getValues();
var L1Tasks = ['task1', 'task2', 'task3'];
for (var row in tasks) {
levels[row] = L1Tasks.includes(tasks[row]) ? ['L1'] : ['L2'];
}
levels_range.setValues(levels);
}

更新

以下是在D:列中使用逗号的代码变体

function fillValues() {
// var sheet = SpreadsheetApp.openById('###').getSheetByName('xxx');  // <--- paste your ID and sheet name
var sheet = SpreadsheetApp.getActiveSheet()
var tasks = sheet.getRange('D2:D' + sheet.getLastRow()).getValues().flat();
var levels_range = sheet.getRange('O2:O' + sheet.getLastRow());
var levels = levels_range.getValues();
var L1Tasks = ['task1', 'task2', 'task3'];
for (var row in tasks) {
var sub_tasks = tasks[row].replace(/,s+/g,',').split(',');
for (var task of sub_tasks) {
if (L1Tasks.includes(task)) {
levels[row] = ['L1']; break;
} else { 
levels[row] = ['L2'];
}
}
}
levels_range.setValues(levels);
}

更新2

如果您想为不仅包含"L1"任务的单元格分配"L2"，您可以通过以下方式更改内部循环：

for (var task of sub_tasks) {
if (!L1Tasks.includes(task)) {
levels[row] = ['L2']; break;
} else { 
levels[row] = ['L1'];
}
}

或者(大约相同算法的较短符号(：

for (var row in tasks) {
var sub_tasks = tasks[row].replace(/,s+/g,',').split(',');
levels[row] = ['L1'];
if (sub_tasks.some(x => !L1Tasks.includes(x))) levels[row] = ['L2'];    
}

或者(更短(，以防万一：

for (var row in tasks) {
var sub_tasks = tasks[row].replace(/,s+/g,',').split(',');
levels[row] = sub_tasks.every(x => L1Tasks.includes(x)) ? ['L1'] : ['L2'];
}