在Google表格中,onEdit函数似乎根本不起作用。

huangapple go评论66阅读模式
英文:

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(&quot;Sheet6&quot;);
  var data = sheet.getRange(&quot;E1:F&quot;).getValues();
  var list = [];
  for (var i = 0; i &lt; data.length; i++) {
    if (data[i][1] === &quot;supervisor&quot;) {
      list.push(data[i][0]);
    }
  }
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
  sheet.getRange(&quot;B:B&quot;).setDataValidation(null);
  sheet.getRange(&quot;B1:B5&quot;).setDataValidation(rule);
}

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(e.source.getActiveSheet().getSheetName());
  var range = e.range;
  if (range === &quot;E1:F&quot;) {
    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 &quot;Sheet6&quot;
    sheetName == &quot;Sheet6&quot; &amp;&amp;
    range.rowStart &gt;= 1 &amp;&amp;
    range.columnStart &gt;= 5 &amp;&amp;
    range.columEnd &lt;= 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();
  }
}

我进行了测试,它可以正常工作。如果您有问题,请告诉我。

参考链接:

getColumn()

英文:

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:

getColumn()

huangapple
  • 本文由 发表于 2023年6月13日 01:18:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76458943.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定