這是基于一些舊的 youtube 視頻的動態資料驗證集的功能腳本
它很慢，我不確定哪里有我可以洗掉的回圈
直到 4 小時前才使用過腳本；任何人都可以讓這個運行得更快嗎
任何建議表示贊賞！
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference Sheet");
if(ss.getName() == "Test Sheet" && ss.getActiveCell().getColumn() == 10 && ss.getActiveCell().getRow() > 7){
ss.getActiveCell().offset(0, 1).clearContent().clearDataValidations();
var materials = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
var materialIndex = materials[0].indexOf(ss.getActiveCell().getValue()) 1;
if(materialIndex != 0){
var validationRangeMU = datass.getRange(2, materialIndex, datass.getLastRow());
var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
ss.getActiveCell().offset(0, 1).setDataValidation(validationRuleMU);
}
}
if(ss.getName() == "Test Sheet" && ss.getActiveCell().getColumn() == 15 && ss.getActiveCell().getRow() > 7){
ss.getActiveCell().offset(0, 1).clearContent().clearDataValidations();
var materials = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
var materialIndex = materials[0].indexOf(ss.getActiveCell().getValue()) 1;
if(materialIndex != 0){
var validationRangeMU = datass.getRange(2, materialIndex, datass.getLastRow());
var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
ss.getActiveCell().offset(0, 1).setDataValidation(validationRuleMU);
}
}
if(ss.getName() == "Test Sheet" && ss.getActiveCell().getColumn() == 20 && ss.getActiveCell().getRow() > 7){
ss.getActiveCell().offset(0, 1).clearContent().clearDataValidations();
var materials = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
var materialIndex = materials[0].indexOf(ss.getActiveCell().getValue()) 1;
if(materialIndex != 0){
var validationRangeMU = datass.getRange(2, materialIndex, datass.getLastRow());
var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
ss.getActiveCell().offset(0, 1).setDataValidation(validationRuleMU);
}
}
uj5u.com熱心網友回復：
試試這個：
充分利用事件物件
function onEdit(e) {
var idx = [10, 15, 20].indexOf(e.range.columnStart)
if (e.range.getSheet().getName() == "Test Sheet" && ~idx && e.range.rowStart > 7) {
var rsh = e.source.getSheetByName("Reference Sheet");
e.range.offset(0, 1).clearContent().clearDataValidations();
var materials = rsh.getRange(1, 1, 1, rsh.getLastColumn()).getValues();
var materialIndex = materials[0].indexOf(e.value) 1;
if (materialIndex != 0) {
var validationRangeMU = rsh.getRange(2, materialIndex, rsh.getLastRow());
var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
e.range.offset(0, 1).setDataValidation(validationRuleMU);
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/384486.html