Get Cell Drop Box - not just values-CodePudding

I am writing a script in Google sheets to pre-populate a cell with a drop box when a new row is added. Cell A1 of the spreadsheet has a basic drop box selector (data validation) with a couple of choices in it. The drop box is not connected to anything and is not used for any actions. I just want to copy that drop box into the first cell of each new row that I add to the sheet.

So far, the script I have will get the selected value of the drop box in A1 and insert that value into the first cell of the new row. What I want is the entire drop box with all the choices to be added to each new row - not just the value. Here's my script so far

function myFunction(e) {
 var sh = SpreadsheetApp.getActiveSheet();
 if(e.changeType === 'INSERT_ROW') {
 var row = sh.getActiveRange().getRow();
 var range = sh.getRange(1,1); 
 var dropbox = range.getValue();
 sh.getRange(row, 1).setValue(dropbox);
  }
}

So the GetValue() is not what I need. Is there a way to get the entire drop box from A1 and copy it to the first cell in each new row added? It would also be fine to programmatically create a simple drop box in the first cell of each new row, but I haven't found a way to do that either. Any help is greatly appreciated.

CodePudding user response：

I think you mean data validation as a drop box? If so, insert something like this code that gets the DataValidation rather than the value:

 var row = sh.getActiveRange().getRow();
 var range = sh.getRange(1,1); 
 var dropbox = range.getDataValidation();
 sh.getRange(row, 1).setDataValidation(dropbox);

To illustrate, in this example sheet if you run the below myFunction() it will get the data validation (i.e. drop box) in cell A1 and set it into cells A2:A10.

/** @OnlyCurrentDoc*/

function myFunction() {
  const ss = SpreadsheetApp.getActiveSheet();
  var pullValidation = ss.getRange("A1").getDataValidation();
  ss.getRange("A2:A10").setDataValidation(pullValidation);
}