I am trying to figure out how to make a googlesheet drop down list for 2 categories, I've got it to work like intended by following this and this guide, however I'm having issues with adding/removing rows and applying the data validation on them, currently to do this after creating/removing a row I need to go to my data prep, and update the indexes for the modified row - I am looking for a way to automate this somehow, is this possible?

CodePudding user response：

I believe your goal is as follows.

• There are 2 Spreadsheets of Main and Dataset.
• The columns "A" and "B" of Main sheet have the data validation rules.
• The cells "B2:D" are the values of data validation rules.
• When the dropdown list of the column "A" is changed, you want to set the dropdown list corresponding to the value of column "A".
• When the new row is added or a row is deleted at Main, you want to automatically set the data validation rules.

In this case, how about the following script?

Sample script:

When you want to run this script, please change the column "A" of Main sheet. Or, please add or delete rows of Main. By this, the script is run and checks the columns "A" and "B" of Main, and then, the script sets the data validation rules to the columns "A" and "B".

function onEdit(e) {
  const {range, source} = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Main" || ![1, 2].includes(range.columnStart) || range.rowStart == 1) return;
  const dataset = source.getSheetByName('Dataset');
  const values = dataset.getRange("B2:D7").getValues();
  const d1 = values[0];
  const d2 = values[0].map((_, c) => values.map(r => r[c]));
  const obj = d2.reduce((o, [h, ...v]) => (o[h] = v.filter(String), o), {});
  const vrange = sheet.getRange("A2:B"   sheet.getLastRow());
  const v = vrange.getValues();
  const dataValidations = vrange.getDataValidations().map(([a, b], i) => [a || SpreadsheetApp.newDataValidation().requireValueInList(d1).build(), a ? SpreadsheetApp.newDataValidation().requireValueInList(obj[v[i][0]] || []).build() : (b || SpreadsheetApp.newDataValidation().requireValueInList([]).build())]);
  vrange.setDataValidations(dataValidations);
}

Note:

• When you directly run this script, an error like Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this. In this case, please edit the cells of the columns "A" and "B" of Main sheet. By this, the script is run.

References:

• Class DataValidationBuilder
• getDataValidations()
• setDataValidations(rules)

Edit By Cooper:

If you want to work through this and the data links in the question are no longer valid you will want this data.

• Sheet Name: Dataset

• Sheet Name: Main

It will be especially helpful when you are working through the datavalidations line which is pretty complex. If you don't know what to enter the first time through because there won't be any data validations at first. So just type anything in column one of the main and it will start.