I am writing a script in Google sheets to pre-populate a cell with a simple drop box when a new row is added. The top row of the spreadsheet has a simple drop box selector with a couple of choices in it – in cell A1. 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 – but I want the entire drop box with all the choices to be added to each new row – not just the selected 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.
>Solution :
I think you mean data validation as a drop box? If so, substitute in 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 this process on a sheet I made shown here, running the below function myFunction() will set the data validation (drop boxy) for cells a2:a10 to be the same as cell a1.
/** @OnlyCurrentDoc*/
function myFunction() {
const ss = SpreadsheetApp.getActiveSheet();
var pullValidation = ss.getRange("A1").getDataValidation();
ss.getRange("A2:A10").setDataValidation(pullValidation);
}