Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

387
Visualizações
TypeError: Cannot read property 'range' of undefined for OnEdit

I'm new to coding and need help with a GS script. I have a 5k row multiple-level dependent drop-down list.

I'm running into an error when I run the below script. It says TypeError: Cannot read property 'range' of undefined. My research says this is because the onEdit(e) object is triggered when the cell is edited, but why can't I run the code so that I can execute the trigger?

If anyone can help here, I'd be extremely grateful.

var mainWsName = "Input: Transactions";
var options2WsName = "Options2";
var fifthLevelColumn = 5;
var sixthLevelColumn = 6;
var seventhLevelColumn = 7;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(options2WsName);
var options = wsOptions2.getRange(2,1,wsOptions2.getLastRow()-1,4).getValues();




function onEdit(e) {
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  if(wsName === mainWsName && c === fifthLevelColumn && r > 7){
    applyFirstLevelValidation(val,r)
  } else if(wsName == mainWsName && c === sixthLevelColumn && r > 7){
    applySecondLevelValidation(val,r)
  }


} // end onEdit

function applyFirstLevelValidation(val,r){

  if(val === ""){
        ws.getRange(r, sixthLevelColumn).clearContent;
        ws.getRange(r, sixthLevelColumn).clearDataValidations;
        ws.getRange(r, seventhLevelColumn).clearContent;
        ws.getRange(r, seventhLevelColumn).clearDataValidations
      } else {
        ws.getRange(r, sixthLevelColumn).clearContent
        ws.getRange(r, sixthLevelColumn).clearDataValidations;
        ws.getRange(r, seventhLevelColumn).clearContent;
        ws.getRange(r, seventhLevelColumn).clearDataValidations
        var filteredOptions = options.filter(function(o){ return o[0] === val });
        var listToApply = filteredOptions.map(function(o){ return o[1]})
        var cell = ws.getRange(r, sixthLevelColumn);
        applyValidationToCell(listToApply,cell)
      }

}

function applySecondLevelValidation(val,r){

  if(val === ""){
        ws.getRange(r, seventhLevelColumn).clearContent;
        ws.getRange(r, seventhLevelColumn).clearDataValidations;
      } else {
        ws.getRange(r, seventhLevelColumn).clearContent
        var firstLevelColValue = ws.getRange(r, fifthLevelColumn).getValue()
        var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });
        var listToApply = filteredOptions.map(function(o){ return o[2]})
        var cell = ws.getRange(r, seventhLevelColumn);
        applyValidationToCell(listToApply,cell)
      }

}

function applyValidationToCell(list, cell){

  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();

  cell.setDataValidation(rule);
}
about 4 years ago · Juan Pablo Isaza
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda