Google Sheets Script - using an array for data validation

Question

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.

Have you tried recording a macro? You'd be better off using requireValueInRange — TheMaster
– TheMaster, Commented Sep 28, 2019 at 13:58
I didn't want to make a macro, I just wanted it all running off the code. I'm stubborn like that :) Thanks for the pointer towards requireValueInRange. That was exactly what I needed! — Rob Gillespie
– Rob Gillespie, Commented Sep 29, 2019 at 13:55

Rob Gillespie · Accepted Answer · 2019-09-29 14:01:55Z

1

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.

answered Sep 29, 2019 at 14:01

Rob Gillespie

114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Sheets Script - using an array for data validation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related