How to add newDataValidation() code block into my Google apps script

Question

So I have a block of code under the OnEdit(e) trigger. I have identified the range and it is working. The problem is I am now trying to add some data validation using apps script on that same range.

I have a block of code written out but I am unsure how to implement it properly on the desired range. I am currently using onOpen(e) and am also unsure if it is acting on the correct column as the data validation does not work when I run the code despite the REGEXMATCH expression being correct.

Any help would be greatly appreciated! Thanks.

Answer 1

I am assuming that you want to change the background color and apply data validation only to the column B of a sheet called TermList. If that's the case:

• The code that is creating the data validation is correct. As it is now, it should create a new data validation when you open the file. Actually, I would not include this in an onOpen trigger because you only need to create the data validation once, not every time you open the file.

• In the following line you are comparing a Sheet to a string, something which can never return true. You should apply getName to the sheet. This might be the reason you think the data validation is not being properly inserted:

  if(sheet != 'TermList' && col != 2) return

• You are also using offset(0, 0) for no reason (the range returned by this method is the one to which this method is applied, because you are not offsetting any row nor column).

• If you want to change the background only if the sheet name is TermList and the edited column is B, then you should change this:

  if(sheet != 'TermList' && col != 2) return;
  var color = e.value;
  range.offset(0, 0).setBackground(color);

To this:

  if (sheet.getName() == 'TermList' && col == 2) {
    var color = e.value;
    range.setBackground(color);
  }

I hope this is of any help.