Have a problem with setting data validation rules in a Google Sheets 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 a user 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!

What's 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);

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);
  • Do you still need help with this? If so, could you share an example sheet? – Punchlinern Apr 23 '14 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.