How to update data validation for a certain range within a specific column with a master list with Google Apps Script?

Question

I have two sheets, both have a column that use data validation in Column G/#7, starting from row #8 and all the way down. I have to edit the data validation every once in a while and match the lists for both sheets. This can be annoying since it starts from row 8 down to row 1000+ and needs to be done for both sheets.

How do you make it so that you have a third sheet called "settings" where there will be a master list where in one column there will be a list of rows and for each row it takes the data and automatically updates the data validation list for both sheet one and sheet two?

e.g.


1 | Master List (Title)
2 | John Doe
3 | Jane Doe
4 | Steve Smith
5 | Stacy Smith

and it makes the data validation for column 7, row 8+ (all the way down) for BOTH Sheet 1 and Sheet 2 as such:

John Doe,Jane Doe,Steve Smith,Stacy Smith

And if a name is added, it adds it to the data validation list / updates the list. If a name is removed, it removes it from the data validation list.

-- Photo examples provided:

We have a column that uses data validation to list out items.

We also have a "master list" with all those items. If I update that "master list" I want the data validation to be updated so I don't have to go into the settings for data validation but only update my list since it is always changing so I can have an updated dropdown for that column.

I have to apologize for my poor English skill. Unfortunately, from your question, I cannot image your goal. In order to correctly understand about it, can you provide the sample Spreadsheet including the input and output situations? — Tanaike
Hi @Tanaike let me explain better. I have Sheet #1 and Sheet #2. Both these sheets have the same column with the same data validation list. I want to create Sheet #3 where I can make a list of names (row by row) and whenever that list is updated, it changes the data validation for Sheet #1 and Sheet #2 (the columns on Sheet #1 and Sheet #2 are both Column G). How do I update data validation by using a list in the spreadsheet rather than having to edit the data validation? — usernametaken
Thank you for replying. I have to apologize for my poor English skill. Unfortunately, from your replying, I cannot still understand about your goal. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon. — Tanaike
@Tanaike No worries, let me try one more time, and it doesn't have to be the exact scenario I'm asking for: how do I update "data validation" in Sheet 1, column G, row 8-1000 through a list in another sheet? If the list has 10 different names listed row after row, how can I make it so that list updates the data validation list set in Column G from Sheet 1? Thank you! — usernametaken
Thank you for replying. I have to apologize for my poor English skill again. Unfortunately, from your provided information, I cannot still understand about your goal. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon. — Tanaike

Tanaike Tanaike · Accepted Answer · 2021-03-14T08:18:21

I believe your goal as follows.

You want to update the datavalidation rules at the column "D" (the range is "D2:D") on the sheet of "Members", when "Master" sheet is updated.
You want to achieve this using Google Apps Script.

In this case, I would like to propose to run the script using OnEdit trigger.

Sample script:

Before you use this script, please set the sheet names of "Master" and "Members" sheets. From your question, I couldn't understand about the correct sheet names. When you want to run the script, please update the cells of "Master" sheet. By this, the datavalidation rules at the cells "D2:D" on "Members" sheet are updated.

function onEdit(e) {
  const masterlistSheetName = "Master";  // Please set the sheetname.
  const membersSheetName = "Members";  // Please set the sheetname.

  const ss = e.source;
  const master = ss.getActiveSheet();
  if (master.getSheetName() != masterlistSheetName) return;
  const range = master.getRange("A2:A" + master.getLastRow());
  const members = ss.getSheetByName(membersSheetName);
  const dataValidation = members.getRange("D2").getDataValidation().copy().requireValueInRange(range).build();
  const length = members.getRange("D2:D").getDataValidations().filter(String).length;
  members.getRange("D2:D" + (length + 1)).setDataValidation(dataValidation);
}

Note:

This sample script is run by the OnEdit trigger. So when you directly run the function onEdit at the script editor, an error occurs because the event object is not used. Please be careful this.
This sample script supposes that your values for datavalidation rules are put to the cells "A2:A" in "Master" sheet. When you want to change this, please modify the above script.
This sample script supposes that your datavalidation rules are put to the cells "D2:D" without the empty rows. When you want to change this, please modify the above script.

How to update data validation for a certain range within a specific column with a master list with Google Apps Script?

1 Answers

Sample script:

Note:

References: