Trouble with applying DataValidation to a column

[George Joseph]

Hello everyone.

I am a beginner at using apps script in google sheets and I am trying to bring a drop down to a whole column from a particular list. The data validation works on the basis of the title of the sheet, executing setdatavalidation() to all cells through a for loop.

If the cell of column = 3 and row = 2 in a sheet contains the company name, then through apps script it will execute, first, a filter on a range from the sheet containing the product lists of different companies, based on the first column,i.e. company name. The 2nd column is the actual product list that should come in the drop down. Next, I execute the map function, mapping the values of 2nd column in a variable and then setting the DataValidation of each cell in the 2nd column.

My code works when the products of a company are less than 500, but when they are greater than that it doesn't execute the code. I'll paste the code below and would love to have a solution for this issue.

var fbk = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FBK");

var ts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product lists");

var tsOptions=ts.getRange(3, 1, ts.getLastRow()-2,2).getValues();

function myFunction() {}
function onEdit(e){
 
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var ssName = activeCell.getSheet().getName();
 
  if(ssName == "FBK" && c ==3 && r==2){
    for(var i=4;i < 3000;i++){
    var filteredOptions = tsOptions.filter(function(o){return o[0] === val});
    var listToApply = filteredOptions.map(function(o){return o[1]});    
    console.log(listToApply);
    var cell = fbk.getRange(i,2);
    applyValidationToCell(listToApply,cell);
    }
  }
}
function applyValidationToCell(list,cell)
  {
 
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
 
  cell.setDataValidation(rule);
}

Hope I have given you all the information.

Thank You!

[Martin Molloy]

I think there is an upper limit of 1000 items in a drop-down list.

You are iterating over the range of 3000 cells and working out the list of items every time. If you took that part out of the loop it would probably work.

But, having said that, you don't need a loop at all. You can apply a  data validation to a range of cells.

SO get rid of the loop and cange 

var cell = fbk.getRange(i,2);

to 

var cell = fbk.getRange(4,2, 2997, 1);

That should work and be much faster

Martin

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e61ea9b1-a857-4578-be08-53ffc5993b1do%40googlegroups.com.

[George Joseph]

Hi Martin,

I have removed the for loop, thank you for that, but still the code won't run if the products are more than 500. Maybe it's because I have several other more companies which have this same code repeating in if-else statements in the same file.

You should also know that at the top of the code I'm calling all of the sheets of different companies into different variables.

If you could please help.

George

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[George Joseph]

Also is there any way I could increase this upper limit in dropdown?\

On Thursday, August 13, 2020 at 1:44:07 PM UTC+5:30, Martin Molloy wrote:

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.