CODEWITHSUNDEEP
arraysvalidationgoogle-apps-script
Rob Gillespie
Rob Gillespie

Reputation: 11

Google Sheets Script - using an array for data validation

Using Java, I'm trying to find the cells that contain any text, take that data and get it onto an array then use these for a dropdown data validation cell.

I've tried various different methods of creating the array but each one didn't create the dropdown cell, leaving it blank.

var carerList=new Array();
    for (var i=19;sheet.getRange('G'+[i])!="";i++){
    var j=0;
    carerList[j].push(sheet.getRange('G'+[i]).getValue());
    j++; 
}
var dV=sheet.getRange('G2').getDataValidation();
dV.setAllowInvalid(false);
dV.requireValueInList(carerList, true);
sheet.getRange("G2").setDataValidation(dV);

I want a nice little data validation in G2 that doesn't have any blank values.

Upvotes: 0

Views: 1310

Answers (1)

Rob Gillespie
Rob Gillespie

Reputation: 11

For anyone who is having a similar problem, here's the complete code answer that I found works.

//sort out the dropdown for choosing carer in G2  

//Get the last row of carers  
var Gvals = sheet.getRange("G19:G").getValues();
var Glast = Gvals.filter(String).length + 18;

//make the validation work
var cell = SpreadsheetApp.getActive().getRange('G2');
var range = SpreadsheetApp.getActive().getRange('G19:G'+Glast);
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);

Thanks again to TheMaster for the nudge towards the right answer.

Upvotes: 1

Related Questions