วิธีใช้ Google Apps Script กับแผ่นงานหลายแผ่น

Question

ฉันต้องการใช้รหัสต่อไปนี้กับหลายแท็บใน Google ชีตเดียวกัน การกำหนดแท็บที่แตกต่างกันใน var แรกหรือการใช้ไฟล์. gs หลายไฟล์ในโปรเจ็กต์เดียวกันแต่ละไฟล์ด้วย var ที่แตกต่างกันไม่ประสบความสำเร็จ

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)

}

ฉันได้ลองใช้งานกับแท็กเพียงแท็กเดียวและไม่รวมตามที่ระบุไว้ที่นี่: วิธีเรียกใช้สคริปต์ในหลายแผ่น Google ชีตและลองทำงานกับอาร์เรย์ตามที่ระบุไว้ที่นี่:https://webapps.stackexchange.com/questions/115076/how-to-run-script-on-multiple-google-sheet-tabsแต่ทั้งสองไม่มีประโยชน์ ใครช่วยชี้ทางที่ถูกต้องให้ฉันหน่อยได้ไหม

ตัวอย่างเช่นฉันสามารถแสดงให้เห็นว่าฉันพยายามทำงานกับแท็กเดียวอย่างไร

//**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

ในการonEditตรวจสอบว่าชื่อของแผ่นงานปัจจุบันมี "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
  }
}

หลังจากนั้นคุณมีfunction KasboekModification(cell){}ฟังก์ชั่นที่คุณสามารถใช้เวทมนตร์ของแผ่นงานปัจจุบันของคุณได้

Answer 2

ตอบ:

วิธีแก้ปัญหาง่ายๆคือการคืนค่าonEdit(e)หากแผ่นงานที่ถูกแก้ไขไม่ใช่หนึ่งในแผ่นงานที่กำหนดไว้ล่วงหน้าให้แก้ไข

ตัวอย่างโค้ด:

ทำได้เฉพาะ :

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
}

หรือไม่รวม :

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
}

---

ตัวอย่างกรณีการใช้งานของคุณ:

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) {
  // ...
}

ฉันหวังว่านี่จะเป็นประโยชน์กับคุณ!

Answer 3

TypeError: ไม่สามารถอ่านคุณสมบัติ 'range' ของ undefined (บรรทัดที่ 7 ไฟล์ "multipledatavalidation")

สาย 7; ถ้า (exclude.includes (e.range.getSheet (). getName ())) {return;

ควรสังเกตว่าฉันมีสคริปต์อื่นที่ทำงานบนแผ่นงานของฉันด้วย สิ่งที่ช่วยให้ฉันสามารถทำซ้ำแผ่นงานโดยล็อคเข้าที่ ฉันต้องการเรียกใช้รหัสนี้กับสำเนาทั้งหมดที่ฉันทำจากแผ่นงานต้นฉบับ นี่คือการเชื่อมโยงไปยังที่ใช้ร่วมกันที่สามารถแก้ไขได้, รุ่นของแผ่นของฉัน

     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)
    
    }