Compare cell to an array and return a value in App script-CodePudding

i am trying to create an app script to compare a cell value to an array, where column D has values (task1, task2... task6), each task has a level 1 or 2, therefore if column D2 has value "task1" then it is L1 and the value is set in the respective row in column O, it seems that I might have made several errors in the way as i am not getting the result i would like, any help would be much appreciated.

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

CodePudding user response：

I haven't tested it. Just a guess. Probably it should be like this:

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

It can be done shorter. For example you can use if (L1Tasks.includes(value[0])) instead of the verbose loop, etc.

The shortened version is here:

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

Update

Here is the variant of the code that hanldes commas in column 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);
}

Update 2

If you want to assign 'L2' for the cells that contain not only 'L1' tasks you can change the inner loop this way:

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

Or (shorter notation of about the same algo):

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

Or (even shorter), just in case:

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