如何根据行中另一个单元格的值自动设置下拉列表中的值 – 应用脚本

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

How to automatically set a value in drop-down list based on a value from another cell in row - apps script

问题

以下是代码部分的翻译:

function onEdit(event)  // 
{
  var ColK = 11;  // 列号为“K”
  var now = new Date();
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColK) 
  {
    // 在列K中发生了编辑
    var state = changedRange.getValue();
    var Group = event.source.getActiveSheet().getRange(changedRange.getRow(), ColK - 5);
    switch (state) 
    {
      case "now":
        // 从下拉列表中选择“TODAY”
        Group.setValue("TODAY");
        break;
    }
  }
}

请注意,上面的代码是JavaScript代码,用于在编辑表格时自动更新时间轴下拉菜单。如果您有任何其他需要翻译的内容,请提供相关文本。

英文:

In my task list each row includes a timeline dropdown menu (F) and a do date cell (K). I want the Timeline dropdown to automatically change based on the date in the do date column.

For example I would like the timeline dropdown to change to Today if the date in the do date column is today's date. I would also like the timeline dropdown to change to Tomorrow if the date in the do date column is tomorrow's date.

如何根据行中另一个单元格的值自动设置下拉列表中的值 – 应用脚本

如何根据行中另一个单元格的值自动设置下拉列表中的值 – 应用脚本

I've tried to create the code based on the responses in a few other different forums. However, I have not been able to successfully create the function.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

function onEdit(event)  // 
{
  var ColK = 11;  // Column Number of &quot;K&quot;
var now = new Date();
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColK) 
  {
    // An edit has occurred in Column K
    var state = changedRange.getValue();
    var Group = event.source.getActiveSheet().getRange(changedRange.getRow(),ColK-5);
    switch (state) 
    {
      case &quot;now”:
        // Select TODAY from dropdown list
        Group.setValue(&quot;TODAY&quot;);
        break

    }
  }
}

<!-- end snippet -->

答案1

得分: 1

以下是代码部分的翻译:

尝试
----
尝试类似以下的内容您可以添加其他条件

function onEdit(event){
  var colK = 11;  // “K”列的列号

  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == colK) {
    // 在K列中发生了编辑

    // 获取Do日期和当前日期,然后设置为相同时间。
    var doDate = changedRange.getValue().setHours(12, 0, 0);
    let today = new Date().setHours(12, 0, 0)

    // 获取两个日期的差异
    var dateDifference = Math.round((doDate - today) / 8.64e7)

    // 根据差异设置下拉列表的值
    var group = event.source.getActiveSheet().getRange(changedRange.getRow(), colK - 5);
    if (dateDifference == 0) {
      group.setValue("今天");
    } else if (dateDifference == 1) {
      group.setValue("明天");
    }
  }
}

希望对您有所帮助!

英文:

Try:

Try something like this, you may add your other conditions.

function onEdit(event){
  var colK = 11;  // Column Number of &quot;K&quot;

  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == colK) {
    // An edit has occurred in Column K

    //Get the Do date and current date then set as same time.
    var doDate = changedRange.getValue().setHours(12, 0, 0);
    let today = new Date().setHours(12, 0, 0)

    //Get the difference of the 2 dates
    var dateDifference = Math.round((doDate - today) / 8.64e7)

    //Set value to dropdown depending on difference
    var group = event.source.getActiveSheet().getRange(changedRange.getRow(), colK - 5);
    if (dateDifference == 0) {
      group.setValue(&quot;Today&quot;);
    } else if (dateDifference == 1) {
      group.setValue(&quot;Tomorrow&quot;);
    }
  }
}

Result:

如何根据行中另一个单元格的值自动设置下拉列表中的值 – 应用脚本

Let me know if this helps!

答案2

得分: 0

以下是翻译好的代码部分:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Your Sheet Name" && e.range.columnStart == 11 && e.range.rowStart > 3 && e.value) {
    // 这将检测到在K列且行号大于3的用户编辑,您可以自行决定要执行的操作
  }
}
英文:

A start:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == &quot;Your Sheet Name&quot; &amp;&amp; e.range.columnStart == 11 &amp;&amp; e.range.rowStart &gt; 3 &amp;&amp; e.value) {
    //this will detect a user edit in column K and row &gt; 3 what you wish to do with it is upto you
  }
}

huangapple
  • 本文由 发表于 2023年2月7日 04:08:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/75366072.html
匿名

发表评论

匿名网友

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

确定