英文:
onEdit function in google sheets seems its not working at all
问题
function update_supervisor_drop_down() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet6");
var data = sheet.getRange("E1:F").getValues();
var list = [];
for (var i = 0; i < data.length; i++) {
if (data[i][1] === "supervisor") {
list.push(data[i][0]);
}
}
var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
sheet.getRange("B:B").setDataValidation(null);
sheet.getRange("B1:B5").setDataValidation(rule);
}
function onEdit(e) {
var range = e.range;
var sheetName = e.source.getActiveSheet().getSheetName();
if (
sheetName === "Sheet6" &&
range.getRow() >= 1 &&
range.getColumn() >= 5 &&
range.getColumn() <= 6
) {
update_supervisor_drop_down();
}
}
英文:
function update_supervisor_drop_down() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet6");
var data = sheet.getRange("E1:F").getValues();
var list = [];
for (var i = 0; i < data.length; i++) {
if (data[i][1] === "supervisor") {
list.push(data[i][0]);
}
}
var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
sheet.getRange("B:B").setDataValidation(null);
sheet.getRange("B1:B5").setDataValidation(rule);
}
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(e.source.getActiveSheet().getSheetName());
var range = e.range;
if (range === "E1:F") {
update_supervisor_drop_down();
}
}
Basicly I have 2 functions, the first one is working like a charm after I save and run the script.
My problem is in the second onEdit function that is supposed to execute the first function if range E1:F is edited, but its not working when I edit the specified range.
Any idea how to solve this issue ?
Thanks
I tried alot of code snippits one of which is this:
function onEdit(e){
var range = e.range;
var sheetName = e.source.getActiveSheet().getSheetName();
if ( // E1:F on "Sheet6"
sheetName == "Sheet6" &&
range.rowStart >= 1 &&
range.columnStart >= 5 &&
range.columEnd <= 6
) {
update_supervisor_drop_down()
}
}
答案1
得分: 0
根据您的示例,我理解您希望在编辑列 E:F
时运行函数 update_supervisor_drop_down()
。
尝试将您的 onEdit(e)
函数更新为以下内容:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(e.source.getActiveSheet().getSheetName());
var range = e.range.getColumn();
if (range === 5 || range === 6) {
update_supervisor_drop_down();
}
}
我进行了测试,它可以正常工作。如果您有问题,请告诉我。
参考链接:
英文:
Based on your sample, I understand that you want to run the function update_supervisor_drop_down()
when you edit columns E:F
.
Try updating your onEdit(e)
function to this:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(e.source.getActiveSheet().getSheetName());
var range = e.range.getColumn();
if (range === 5 || range === 6) {
update_supervisor_drop_down();
}
}
I tested it and it worked. Let me know if you have questions.
Reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论