Is there a way to copy and paste a value into a new data validation with google sheets-CodePudding

I am newer to scripting in Google sheets. I do believe this is possible but if not please let me know.

I am building an employee schedule sheet, When we get a new employee I want someone less savvy to be able to press a script button and it will replace the data validation as shown in my photo. The names column is already formatted in a way where the first and last are separate cells.

Photo of ideal validation

CodePudding user response：

In general terms, it's possible.

A data validation showing a dropdown could be set to use the values from a range or from a list of values, so the first you have to do is decide which of them you will use.

Please bear in mind that any editor is able to modify the data-validation settings or even they are able to remove it, i.e. pasting a cell no having a data-validation over a cell with a data validation will remove the data validation. Also buttons in Google Sheets are very fragile, any editor could remove them and the assigned function could be easily removed.

CodePudding user response：

A Script to edit the range of a datavalidation

function updateValidationSelections() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const drg = sh.getRange(2,7,getColumnHeight(7,sh,ss));
  const dvs = drg.getValues();
  let html = `<textarea id="txt1" cols="" rows="">${dvs}</textarea><br><input type="button" value="Save" onClick="save();" />`;
  html  = '<script>function save(){let list = document.getElementById("txt1").value;google.script.run.withSuccessHandler(function(){google.script.host.close();}).saveList(list);}</script>'
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),"Edit List")
  
}

function saveList(s) {
  Logger.log(s);
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const list = [];
  s.split(",").forEach(e => list.push([e]));
  Logger.log(list);
  sh.getRange(2,7,getColumnHeight(7,sh,ss)).clearContent();
  sh.getRange(2,7,list.length,list[0].length).setValues(list);
}

Demo: