In Google Sheets set dynamic dropdown with Apps Script

Question

I'm trying to set a dynamic dropdown in Google Sheet using Apps Script. I managed to get most parts working except setting the data validation in the cells necessary:

function sheetByName(ssId, sheetName) {
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheetByName(sheetName);

  return sheet;
};

function columnByName(sheet, columnName) {
  var data = sheet.getDataRange().getValues();
  var column = data[0].indexOf(columnName);

  return column;
};

function columnValues(sheet, index) {
  var data = sheet.getDataRange().getValues();
  var values = [];
  
  for(n=1; n<data.length; ++n) {
    values.push(data[n][index]);
  }

  return values;
}

function columnSetDataValidation(sheet, index, options) {
  var data = sheet.getDataRange().getValues();
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(options)
    .setAllowInvalid(true)
    .build();

  for(n=1; n<data.length; ++n) {
    var cell = data[n][index];
  };
};

function dropDownBedrijven() {
  var sheetCollegas = sheetByName("<<ID HERE>>", "Collegas");
  var sheetBedrijven = sheetByName("<<ID HERE>>", "Bedrijven");
  
  var getColumnIndexInBedijven = columnByName(sheetBedrijven, "Bedrijf");
  var getColumnIndexInCollegas = columnByName(sheetCollegas, "Bedrijf");

  var bedrijven = columnValues(sheetBedrijven, getColumnIndexInBedijven).filter(item => item);  

  columnSetDataValidation(sheetCollegas, getColumnIndexInCollegas, bedrijven);
  
};

I can't manage to get the function columnSetDataValidation to set data validation in the required cells.

Do you have any idea how to go about it?

Is the sheet blank if so the getDataRange() get's nothing

Cooper
– Cooper

2021-04-09 01:06:18 +00:00
Commented Apr 9, 2021 at 1:06 — Cooper
– Cooper, Commented Apr 9, 2021 at 1:06

Aerials · Accepted Answer · 2021-04-09 12:27:56Z

2

You need to use range.setDataValidation(rule) with a range.

In your function columnSetDataValidation you are correctly building the rule, but are failing to assign the rule to a range. You are looping over the values of the range and then changing the value of var cell until the loop ends. Nowhere did you call range.setDataValidation(rule).

Try the following solution:

function columnSetDataValidation(sheet, index, options) {
  var range = sheet.getDataRange();
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(options)
    .setAllowInvalid(true)
    .build();

  for(n = 1; n < range.getLastRow(); ++n) {
    var cell = range.getCell(n,index);
    cell.setDataValidation(rule);
  };
};

References:

answered Apr 9, 2021 at 12:27

Aerials

4,4691 gold badge20 silver badges20 bronze badges

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

1 Comment

Raoul Dundas Over a year ago

Thanks, works like a charm! I made minor adjustments to get the result I was seeking for. But you really helped me out.

Cooper · Accepted Answer · 2021-04-09 01:22:11Z

0

Try this:

function columnSetDataValidation() {
  const ss=SpreadsheetApp.getActive();
  const sheet=ss.getSheetByName('Sheet1');
  const range = sheet.getRange(2,4,sheet.getLastRow()-1);//putting validation in column 4
  const options=[1,2,3,4,5];
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(options)
    .setAllowInvalid(true)
    .build();
  range.setDataValidation(rule);
}

edited Apr 9, 2021 at 1:22

answered Apr 9, 2021 at 1:13

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

1 Comment

Raoul Dundas Over a year ago

Thanks for looking into this. I went with the solution Aerials posted because it fit my purpose precisely.

Collectives™ on Stack Overflow

In Google Sheets set dynamic dropdown with Apps Script

2 Answers 2

References:

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

References:

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related