Como aplicar o Script do Google Apps a várias planilhas

Question

Quero aplicar o seguinte código a várias guias na mesma planilha do Google. A definição de guias diferentes na primeira var ou a aplicação de vários arquivos .gs no mesmo projeto, cada um com uma var diferente, não funcionou.

var naamWerkbladKasboek = "Kasboek"; 
var naamWerkbladOpties = "Opties";
var eersteKolomMetInput = 2; 
var tweedeKolomMetInput = 3; 
var derdeKolomMetInput = 4; 

var werkblad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(naamWerkbladKasboek);
var werkbladOpties = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(naamWerkbladOpties);
var Opties = werkbladOpties.getRange(2, 1, werkbladOpties.getLastRow()-1, 3).getValues();


function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var row = activeCell.getRow();
  var column = activeCell.getColumn();
  var werkbladName = activeCell.getSheet().getName();
  
  if(werkbladName === naamWerkbladKasboek && column === eersteKolomMetInput && row > 1){
    applyFirstLevelValidation(val, row);
  } else if(werkbladName === naamWerkbladKasboek && column === tweedeKolomMetInput && row > 1){ 
    applySecondLevelValidation(val, row);
  }
} 

function applyFirstLevelValidation(val, row){
  
  if(val === ""){
      werkblad.getRange(row, tweedeKolomMetInput).clearContent();
      werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
    } else {
      werkblad.getRange(row, tweedeKolomMetInput).clearContent();
      werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
      var gefilterdeOpties = Opties.filter(function(o){ return o[0] === val });
      var listToApply = gefilterdeOpties.map(function(o){ return o[1] });
      var cell = werkblad.getRange(row, tweedeKolomMetInput);
      applyValidationToCell(listToApply,cell);
    }
}


function applySecondLevelValidation(val, row){
  
  if(val === ""){
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
    } else {
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      var waardeEersteKolomMetInput = werkblad.getRange(row, eersteKolomMetInput).getValue();
      var gefilterdeOpties = Opties.filter(function(o){ return o[0] === waardeEersteKolomMetInput && o[1] === val });
      var listToApply = gefilterdeOpties.map(function(o){ return o[2] });
      var cell = werkblad.getRange(row, derdeKolomMetInput);
      applyValidationToCell(listToApply,cell);
    }
}

function applyValidationToCell(list,cell){
  
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  
  cell.setDataValidation(rule)

}

Tentei trabalhar com as únicas tags e excluir, conforme indicado aqui: Como executar um script em várias planilhas, Planilhas Google e também tentei trabalhar com uma matriz conforme indicado aqui:https://webapps.stackexchange.com/questions/115076/how-to-run-script-on-multiple-google-sheet-tabsmas ambos em vão. Alguém pode, por favor, apontar a direção certa?

Como exemplo, posso mostrar como tentei trabalhar com a única tag

//**EDIT**
var only = [
"Kasboek1",
"Kasboek2",
"Kasboek3"
];
var naamWerkbladOpties = "Opties";
var eersteKolomMetInput = 2; 
var tweedeKolomMetInput = 3; 
var derdeKolomMetInput = 4; 

var werkblad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(only);
var werkbladOpties = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(naamWerkbladOpties);
var Opties = werkbladOpties.getRange(2, 1, werkbladOpties.getLastRow()-1, 3).getValues();


function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var row = activeCell.getRow();
  var column = activeCell.getColumn();
  var werkbladName = activeCell.getSheet().getName();
  
  if(werkbladName === only && column === eersteKolomMetInput && row > 1){
    applyFirstLevelValidation(val, row);
  } else if(werkbladName === only && column === tweedeKolomMetInput && row > 1){ 
    applySecondLevelValidation(val, row);
  }
} 

function applyFirstLevelValidation(val, row){
  
  if(val === ""){
      werkblad.getRange(row, tweedeKolomMetInput).clearContent();
      werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
    } else {
      werkblad.getRange(row, tweedeKolomMetInput).clearContent();
      werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
      var gefilterdeOpties = Opties.filter(function(o){ return o[0] === val });
      var listToApply = gefilterdeOpties.map(function(o){ return o[1] });
      var cell = werkblad.getRange(row, tweedeKolomMetInput);
      applyValidationToCell(listToApply,cell);
    }
}


function applySecondLevelValidation(val, row){
  
  if(val === ""){
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
    } else {
      werkblad.getRange(row, derdeKolomMetInput).clearContent();
      var waardeEersteKolomMetInput = werkblad.getRange(row, eersteKolomMetInput).getValue();
      var gefilterdeOpties = Opties.filter(function(o){ return o[0] === waardeEersteKolomMetInput && o[1] === val });
      var listToApply = gefilterdeOpties.map(function(o){ return o[2] });
      var cell = werkblad.getRange(row, derdeKolomMetInput);
      applyValidationToCell(listToApply,cell);
    }
}

function applyValidationToCell(list,cell){
  
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  
  cell.setDataValidation(rule)

}

Answer 1

no onEditvocê verifica se o nome da planilha atual contém "Kasboek"

function onEdit(e){
  var activeCell = e.range;
  var activeSheet = activeCell.getSheet();
  
  if(activeSheet.getName().includes("Kasboek")){
    KasboekModification(activeCell);
  } else {
    //code here if changes need to be made on other sheets
  }
}

Depois disso, você tem sua function KasboekModification(cell){}função onde pode fazer a mágica de sua planilha atual.

Answer 2

Responda:

Uma solução simples seria retornar do onEdit(e)se a folha que foi editada não for uma das predefinidas para editar.

Amostra de código:

Para fazer apenas com :

var only = ["Sheet1Name", "Sheet2Name", "Sheet3Name"]; // add sheet names as desired

function onEdit(e) {
  if (!(only.includes(e.range.getSheet().getName())) {
    return;
  }
  // put the rest of your onEdit function here
}

ou com excluir :

var exclude = ["Sheet4Name", "Sheet5Name", "Sheet6Name"]; // add sheet names as desired

function onEdit(e) {
  if (exclude.includes(e.range.getSheet().getName())) {
    return;
  }
  // put the rest of your onEdit function here
}

---

Exemplo para seu caso de uso:

var only = ["Kasboek", "Kasboek2", "Kasboek3"];

function onEdit(e) {
  if (!(only.includes(e.range.getSheet().getName())) {
    return;
  }

  var activeCell = e.range;
  var val = activeCell.getValue();
  var row = activeCell.getRow();

  if (row <= 1) return;

  var column = activeCell.getColumn();
  var eersteKolomMetInput = 2; 
  var tweedeKolomMetInput = 3; 
  var derdeKolomMetInput = 4;
  
  if (column === eersteKolomMetInput) {
    applyFirstLevelValidation(val, row);
  } 
  else if (column === tweedeKolomMetInput) {
    applySecondLevelValidation(val, row);
  }
} 

function applyFirstLevelValidation(val, row) {
  // ...
}

function applySecondLevelValidation(val, row) {
  // ...
}

Espero que isso seja útil para você!

Answer 3

TypeError: Não é possível ler a propriedade 'range' de undefined (linha 7, arquivo "multipledatavalidation")

linha 7; if (exclude.includes (e.range.getSheet (). getName ())) {return;

Deve-se observar que também tenho outro script em execução na minha planilha. Um que me permite duplicar a folha com o cadeado no lugar. Quero ser capaz de executar este código em todas as cópias que fizer da folha original. aqui está um link para uma versão compartilhada e editável de minha planilha .

     var optionsWsName = "Backend-Prices";
      var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
      var exclude = ["BackendImportedData", "donotedit", "BackendDataSort", "09/20_DS7"]; // add sheet names as desired
    
// function to update each time the file is Edited 

function onEdit(e){
      if (exclude.includes(e.range.getSheet().getName())) {
        return;
      }
      
      var Options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1, 5).getValues();
      var activeCell = e.range;
      var val = activeCell.getValue();
      var r = activeCell.getRow();
    
      if (r <= 8) return;
    
      var c = activeCell.getColumn();
      var FirstLevelColumn = 1;
      var SecondLevelColumn = 2; 
      var ThirdLevelColumn = 3; 
      var FourthLevelColumn = 4;
      
      
      if(c === FirstLevelColumn){
        applyFirstLevelValidation(val, r);
      } else if(c === SecondLevelColumn){ 
        applySecondLevelValidation(val, r);
      } else if(c === ThirdLevelColumn){ 
        applyThirdLevelValidation(val, r);
      }
    
}//end onEdit
    
    
    
    // function for second level of data validation to work correctly
    
function applyFirstLevelValidation(val, r){
      
      if(val === ""){
          ws.getRange(r, SecondLevelColumn).clearContent();
          ws.getRange(r, SecondLevelColumn).clearDataValidations();
          ws.getRange(r, ThirdLevelColumn).clearContent();
          ws.getRange(r, ThirdLevelColumn).clearDataValidations();
          ws.getRange(r, FourthLevelColumn).clearContent();
          ws.getRange(r, FourthLevelColumn).clearDataValidations();
        } else {
          ws.getRange(r, SecondLevelColumn).clearContent();
          ws.getRange(r, SecondLevelColumn).clearDataValidations();
          ws.getRange(r, ThirdLevelColumn).clearContent();
          ws.getRange(r, ThirdLevelColumn).clearDataValidations();
          ws.getRange(r, FourthLevelColumn).clearContent();
          ws.getRange(r, FourthLevelColumn).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, SecondLevelColumn);
          applyValidationToCell(listToApply,cell);
        }

}
    
    // function for third level of data validation to work correctly
    
function applySecondLevelValidation(val, r){
      
      if(val === ""){
          ws.getRange(r, ThirdLevelColumn).clearContent();
          ws.getRange(r, ThirdLevelColumn).clearDataValidations();
          ws.getRange(r, FourthLevelColumn).clearContent();
          ws.getRange(r, FourthLevelColumn).clearDataValidations();
        } else {
          ws.getRange(r, ThirdLevelColumn).clearContent();
          ws.getRange(r, ThirdLevelColumn).clearDataValidations();
          ws.getRange(r, FourthLevelColumn).clearContent();
          ws.getRange(r, FourthLevelColumn).clearDataValidations();
          var firstlevelColValue = ws.getRange(r, FirstLevelColumn).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, ThirdLevelColumn);
          applyValidationToCell(listToApply,cell);
        }
        
}
    
    
    // function for fourth level of data validation to work correctly
    
    function applyThirdLevelValidation(val, r){
      
      if(val === ""){
          ws.getRange(r, FourthLevelColumn).clearContent();
          ws.getRange(r, FourthLevelColumn).clearDataValidations();
        } else {
          ws.getRange(r, FourthLevelColumn).clearContent();
          var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();
          var secondlevelColValue = ws.getRange(r, SecondLevelColumn).getValue();
          var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === secondlevelColValue && o[2] === val });
          var listToApply = filteredOptions.map(function(o){ return o[3] });
          var cell = ws.getRange(r, FourthLevelColumn);
          applyValidationToCell(listToApply,cell);
        }

}

    
function applyValidationToCell(list,cell){
      
var rule = SpreadsheetApp
 .newDataValidation()
 .requireValueInList(list)
 .setAllowInvalid(false)
 .build();
      
 cell.setDataValidation(rule)
    
    }