时间戳

首页 > 解决方案 > 结合两个 onEdit 函数，每个函数都使用自己的数据为自己的工作表工作

google-apps-script - 结合两个 onEdit 函数，每个函数都使用自己的数据为自己的工作表工作

问题描述

我有两个代码。他们每个人都给了我三个相互依赖的下拉列表，并适用于不同的工作表。每个代码都使用其单独的数据列表。

如何组合这两个脚本，每个脚本都适用于 2 个不同的页面？（总共有 4 页，三个下拉列表）

第一个功能：

var mainWsName1 = "PERSONELextra";   
var mainWsName2 = "ISKUR";                                                    
var optionsWsName = "Lists";   

var firstLevelColumn = 12;                               
var secondLevelColumn = 13;                              
var thirdLevelColumn = 14; 

var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);                                

   function onEdit(e){   

  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName);                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName);                              
    }  
  }                             
} 

function applyFirstLevelValidation(val,r,wsName){
  //  obtain the sheet name dynamically from the onEdit() function  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  if(val == ""){                             
    ws.getRange(r, secondLevelColumn).clearContent();                               
    ws.getRange(r, secondLevelColumn).clearDataValidations();                               
    ws.getRange(r, thirdLevelColumn).clearContent();                                
    ws.getRange(r, thirdLevelColumn).clearDataValidations();                                
  } else {                                
    ws.getRange(r, secondLevelColumn).clearContent();                               
    ws.getRange(r, secondLevelColumn).clearDataValidations();                               
    ws.getRange(r, thirdLevelColumn).clearContent();                                
    ws.getRange(r, thirdLevelColumn).clearDataValidations();                                
    var filteredOptions = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] == val });  // <--- Modified        
    var listToApply = filteredOptions.map(function(o){ return o[1] });                              
    var cell = ws.getRange(r, secondLevelColumn);                               
    applyValidationToCell(listToApply,cell);                                
  }                               
}                               

function applySecondLevelValidation(val,r, wsName){   
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  if(val == ""){                             
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, thirdLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] === firstLevelColValue && o[1] == val });  // <--- Modified
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);  // <--- Modified
applyValidationToCell(listToApply,cell);
}
}

function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)  // <--- Modified
.setAllowInvalid(false)
.build();

cell.setDataValidation(rule)
}

这是其他两张表（和另一张数据表）的第二个功能：

var mainWsName1 = "GİDER";                                                       
var optionsWsName = "Lists2";   

var firstLevelColumn = 13;                               
var secondLevelColumn = 14;                              
var thirdLevelColumn = 15; 

var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);                                

function onEdit(e){                             
  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName);                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName);                              
    }  
  }                             
} 

function applyFirstLevelValidation(val,r,wsName){
  //  obtain the sheet name dynamically from the onEdit() function  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  if(val == ""){                             
    ws.getRange(r, secondLevelColumn).clearContent();                               
    ws.getRange(r, secondLevelColumn).clearDataValidations();                               
    ws.getRange(r, thirdLevelColumn).clearContent();                                
    ws.getRange(r, thirdLevelColumn).clearDataValidations();                                
  } else {                                
    ws.getRange(r, secondLevelColumn).clearContent();                               
    ws.getRange(r, secondLevelColumn).clearDataValidations();                               
    ws.getRange(r, thirdLevelColumn).clearContent();                                
    ws.getRange(r, thirdLevelColumn).clearDataValidations();                                
    var filteredOptions = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] == val });  // <--- Modified        
    var listToApply = filteredOptions.map(function(o){ return o[1] });                              
    var cell = ws.getRange(r, secondLevelColumn);                               
    applyValidationToCell(listToApply,cell);                                
  }                               
}                               

function applySecondLevelValidation(val,r, wsName){   
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  if(val == ""){                             
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, thirdLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] === firstLevelColValue && o[1] == val });  // <--- Modified
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);  // <--- Modified
applyValidationToCell(listToApply,cell);
}
}

function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)  // <--- Modified
.setAllowInvalid(false)
.build();

cell.setDataValidation(rule)
}

标签： google-apps-scriptgoogle-sheets

解决方案

解释：

  • 将这两个onEdit函数重命名为onEdit1和onEdit2。这些名称由您决定，但您不能拥有两个onEdit功能。

  • 将你在onEdit1andonEdit2函数之外的所有全局变量放在每个函数中。

  • 您不需要多次重新定义辅助函数，但您只需要它们一次，它们将适用于onEdit1和onEdit2。

  • 最后，创建一个onEdit将同时执行onEdit1和的onEdit2。

解决方案：

function onEdit(e){ 
  onEdit1(e);
  onEdit2(e);
 }

 function onEdit1(e){   
  var mainWsName1 = "A1";   
  var mainWsName2 = "A2";                                                    
  var optionsWsName = "listforA";   
  var firstLevelColumn = 12;                               
  var secondLevelColumn = 13;                              
  var thirdLevelColumn = 14; 
  var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);           

  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName,secondLevelColumn,thirdLevelColumn,wsOptions)                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName,firstLevelColumn,thirdLevelColumn,wsOptions)                              
    }  
  }                             
} 
                          
function onEdit2(e){     
  var mainWsName1 = "B1";
  var mainWsName2 = "B2";                                                        
  var optionsWsName = "listforB";   
  var firstLevelColumn = 13;                               
  var secondLevelColumn = 14;                              
  var thirdLevelColumn = 15; 
  var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);      
  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName,secondLevelColumn,thirdLevelColumn,wsOptions);                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName,firstLevelColumn,thirdLevelColumn,wsOptions);                              
    }  
  }                             
} 

function applyFirstLevelValidation(val,r,wsName,secondLevelColumn,thirdLevelColumn,wsOptions){
  //  obtain the sheet name dynamically from the onEdit() function  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  if(val == ""){                             
    ws.getRange(r, secondLevelColumn).clearContent();                               
    ws.getRange(r, secondLevelColumn).clearDataValidations();                               
    ws.getRange(r, thirdLevelColumn).clearContent();                                
    ws.getRange(r, thirdLevelColumn).clearDataValidations();                                
  } else {                                
    ws.getRange(r, secondLevelColumn).clearContent();                               
    ws.getRange(r, secondLevelColumn).clearDataValidations();                               
    ws.getRange(r, thirdLevelColumn).clearContent();                                
    ws.getRange(r, thirdLevelColumn).clearDataValidations();                                
    var filteredOptions = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] == val });  // <--- Modified        
    var listToApply = filteredOptions.map(function(o){ return o[1] });                              
    var cell = ws.getRange(r, secondLevelColumn);                               
    applyValidationToCell(listToApply,cell);                                
  }                               
}                               

function applySecondLevelValidation(val,r, wsName,firstLevelColumn,thirdLevelColumn,wsOptions){   
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  if(val == ""){                             
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, thirdLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] === firstLevelColValue && o[1] == val });  // <--- Modified
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);  // <--- Modified
applyValidationToCell(listToApply,cell);
}
}

function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)  // <--- Modified
.setAllowInvalid(false)
.build();

cell.setDataValidation(rule)
}

推荐阅读