I am trying to create a drop down list in google sheet using google app script. Sheet Link: https://docs.google.com/spreadsheets/d/1TESZNYgSJC-ye8r9CcW57-Vt1yU5VcvELiJw4oRwAZQ/edit#gid=0
In this sheet, in column B there is responsible person. Whenever I will select any name from the drop down list of this column, the another drop down list will create in column C and in column D the current date will appear. For example, If I select 'Anushka' in cell B2, a drop down list 'New', 'on going', 'done', 'on hold' will create in cell C2 with the first value from the list of the drop down and a current date will appear in cell D2. Till now, I can create the drop down menu in cell C2 and also current date in cell D2 based one cell B2. But I don't know how to show always the first item from the drop down list in cell C2. Now it's giving me blank cell and I need to select something from the menu but I want in a way that when drop down menu will create, it will show the first item not empty cell. I have attached the code also. Can anyone please help me where I should modify?
var sheet = event.source.getActiveSheet();
var colrange=sheet.getRange("B2:B").isBlank()
var actRng = event.source.getActiveRange();
var index = actRng.getRowIndex();
var cindex = actRng.getColumnIndex();
if(colrange){
sheet.getRange(index,4).clearContent()
sheet.getRange(index,3).clearDataValidations()
} else{
var date = Utilities.formatDate(new Date(),"EST", "MMMM-dd-yyyy");
var list=["New", "Ongoing", "Done", "On-Hold"]
var rule=SpreadsheetApp.newDataValidation().requireValueInList(list).build()
sheet.getRange(index,3).setDataValidation(rule)
sheet.getRange(index,4).setValue(date)
}
Custom Validations
function onEdit(e) {
e.source.toast("Entry");
const sh = e.range.getSheet();
if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value) {
e.source.toast("Flag1");
let rg = e.source.getRangeByName("Status");
e.range.offset(0,1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(rg));
e.range.offset(0,2).setValue(new Date());
Logger.log(e.source.getRangeByName("Status").getValues()[0][0])
e.range.offset(0,1).setValue(e.source.getRangeByName("Status").getValues()[0][0]);
}
}
Demo:
Named Ranges:
In answer to your comment below:
function onEdit(e) {
//e.source.toast("Entry");
const sh = e.range.getSheet();
if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value) {
//e.source.toast("Flag1");
let rg = e.source.getRangeByName("Status");
e.range.offset(0,1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(rg));
e.range.offset(0,2).setValue(new Date());
//Logger.log(e.source.getRangeByName("Status").getValues()[0][0])
e.range.offset(0,1).setValue(e.source.getRangeByName("Status").getValues()[0][0]);
}
if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && !e.value) {
//e.source.toast("Flag2");
let rg = e.source.getRangeByName("Status");
e.range.offset(0,1).clearDataValidations();
e.range.offset(0,2).setValue('');
}
}