Add text to end of string if data validation is invalid-CodePudding

I have data validation in my ranges and I want a script to insert text at the end of the string if it is invalid, otherwise leave it alone. Can this be done?

Example:

"CHILLS" is a part of my data validation and follows a conditional formatting to turn blue. "NOT VALIDATED" is obviously not a part of the above validation/formatting (hence the red flag) so I would like it to append to "NOT VALIDATED (*)"

CodePudding user response：

I believe your goal is as follows.

When the inputted value is not included in the values of the data validation rule, you want to add (*) to the inputted value.

In this case, how about the following sample script?

Sample script:

function onEdit(e) {
  const {range} = e;
  if (range.columnStart < 2 || range.columnEnd > 3 || range.rowStart < 3 || range.rowEnd > 6) return;
  const values = range.getDataValidation().getCriteriaValues()[0].getValues().flat();
  if (!values.includes(e.value)) {
    range.setValue(e.value   "(*)");
  }
}

From your sample Spreadsheet, it supposes that the dropdown list is set to the cells "B3:C6". When you want to change this range, please modify the above script.

Note:

In this sample script, from your question, the script is run by the OnEdit simple trigger. So when you directly run the script at the script editor, an error occurs. Please be careful about this. When you use this script, please input the value to the cells "B3:C6".

Sample script:

Note:

References: