Google Apps Script getActiveCell().setValue() throws error-CodePudding

I am writing a multi-select for a dropdown menu. I have actually already written one, and I'm writing another one for another column and other data. However, this time, when I go to set the new value, it breaks and throws an error that I have not found online. I have been able to deduce that it is the setValue function that is causing the error, as I have commented it out and no error was thrown. I've also tried to use setNote, and that worked.

The error that I am getting:

A - Both Rollers_x000a_B - Starting Roller_x000a_C - Opposite Roller_x000a_H - Score High_x000a_L - Score Low_x000a_N - Nothing

Here is my code:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var active = ss.getActiveCell();
  if (active.getColumn() == 10 && active.getRow() != 1 && ss.getActiveSheet().getName() == "Sheet 2") {
    if (e.value == "N")
      return;

    var vals = [];
    if (e.oldValue)
      vals = Array.from(e.oldValue);

    if (vals.indexOf(e.value) == -1)
      vals.push(e.value);
    else
      vals.splice(vals.indexOf(e.value), 1);

    var val = "";
    for (var i = 0; i < vals.length; i  )
      val  = vals[i];

    active.setValue(val)
  }
}

Any help would be appreciated, thanks!

CodePudding user response：

The error contents, as shown in your question, looks like an array of values. The variable val is declared in your code in the same manner, that is an array of values. So I assume that the error message is telling you that you tried to fit an array of values into a single cell, please correct me if I am wrong.

You can easily solve this situation by updating the code using the Range.setValues() method, so it can paste the array into the Sheet occupying more than one cell. For this to work the range of the pasted values must match the range of the active variable, you can do so with the Spreadsheet.getActiveRange() method for example. Please be aware that the values need to be in a two dimensional array, drop a comment below if you need help with that.

The final code, using the samples above, could look like this:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var active = ss.getActiveRange();

  if (active.getColumn() == 10 && active.getRow() != 1 && ss.getActiveSheet()
    .getName() == "Sheet 2") {
    if (e.value == "N") {
      return;
    }

    var vals = [];
    if (e.oldValue) {
      vals = Array.from(e.oldValue);
    }

    if (vals.indexOf(e.value) == -1) {
      vals.push(e.value);
    } else {
      vals.splice(vals.indexOf(e.value), 1);
    }

    var val = "";
    for (var i = 0; i < vals.length; i  ) {
      val  = vals[i];
    }

    active.setValues(val);
  }
}

CodePudding user response：

I have found the problem. I had a data validation on the range, and the validation help text was the error. It failed because the data validation was set to reject input on invalid inputs. This makes sense as the concatenated string is not part of the list.