Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Have a problem with setting data validation rules in a google spreadsheet using apps script. Below are two functions, one is an onChange function which will be triggered whenever a user changes the spreadsheet in any way.

The updatenamedataVal() function will be triggered whenever adds/removes a row from a specific sheet.

Everything works fine and the code executes. But there is no data validation set in the cells! Whats wrong?

function myonChange(e){
  var ss = e.source.getActiveSheet();
  var ssname = ss.getSheetName();
  if((e.changeType === 'INSERT_COLUMN' || e.changeType === 'REMOVE_COLUMN') && ssname !== 'Aggregates'){
    Browser.msgBox('Whoops!','You are not allowed to add/remove columns. Please undo action by pressing CTRL+Z', Browser.Buttons.OK);  
  }
  else if((e.changeType === 'INSERT_ROW' || e.changeType === 'REMOVE_ROW') && ssname === 'Teacher Details'){
    var ssrange = ss.getDataRange();
    var valrange = ss.getRange(2,3,ssrange.getLastRow(),1);
    updatenamecelldataVal(valrange);
  } 
};


function updatenamecelldataVal(valrange){
      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      var numsheets = SpreadsheetApp.getActiveSpreadsheet().getNumSheets();
      var rule = SpreadsheetApp.newDataValidation().requireValueInRange(valrange).build();
      for(var i = 0; i < numsheets; i++){
        var sheetname = sheets[i].getName();
        var sheetrange = sheets[i].getDataRange();
        var sheetlastrow = sheetrange.getLastRow();
        if(sheetname !== 'Teacher Details' && sheetname !== 'Aggregates' && sheetname !== 'List of Subjects'){
          for(var j = 0; j < (sheetlastrow/17); j++){
            var namecell = sheetrange.getCell(4+(17*j), 2);
            namecell.clearDataValidations();
            namecell.setDataValidation(rule); 
          }
        }
      }
    }; 
share|improve this question
    
Do you still need help with this? If so, could you share an example sheet? –  Punchlinern Apr 23 at 13:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.