set data validation for column in google doc including new rows

Question

I have a column in a google doc with data validation, that makes a drop down menu available when editing existing rows. Occasionally though contributors create new rows at the bottom of the doc and this validation is lost.

I would like to keep this validatation in the column even in newly created rows (excluding the header row).

I have seen much of this discussion throughout stackoverflow and google help forums, but still cannot find a clear answer.

My hunch is that this would involve Google App Acript using an onEdit trigger and then either paint formatting or a combination of the confusing class DataValidationBuilder. (https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder) but I'm not sure beyond that.

If someone could provide the code, or a link to where I can find it, I would be grateful - thanks.

Answer 1

onEdit trigger which uses Range.getDataValidation() and Range.setDataValidation() to copy the rule from an existing row to the new row

Answer 2

While not necessarily elegant I found the following to work:

• create your validation on the entire column
• once that's done click the cell(s) that represent the heading, select data / validation, and then click on 'remove validation' on that single cell.

This way any newly inserted rows will inherit the validation set up on the entire column and you don't have to always be reminded that your heading isn't a valid value.

Answer 3

While in the data validation dialog screen you can manually enter the range as "Sheet1!A:A" to apply the formula to all of column A.

This also includes new rows added afterwards

Answer 4

I'm not sure if this is a new feature but this worked. Set the validation as list from interval and interval as:

'sheet'!C2:C

This includes all new rows excluding title(first row)