0

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.

enter image description here

enter image description here

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.

function onEdit(event)  // 
{
  var ColK = 11;  // Column Number of "K"
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 "now”:
        // Select TODAY from dropdown list
        Group.setValue("TODAY");
        break

    }
  }
}
user6738171
  • 1,009
  • 2
  • 15
  • 50
  • so this is a function that would run automatically at midnight every night? – MattKing Feb 06 '23 at 20:35
  • I guess it would make sense to have it run every night at midnight. So if you used my screenshot above as an example, 12:00 am on 2/15/2023 the Timeline dropdown would automatically change from Next to Tomorrow. And then at 12:00 am on 2/16/2023 the Timeline dropdown would change from Tomorrow to Today. – user6738171 Feb 06 '23 at 20:52
  • so this is a common misconception... that "dropdown"s are somehow different than regular cells. When in fact, they're just a way of *entering* regular data into a cell. So what you're describing is just: A function that runs every night at midnight, and if the value in column K is equal to "tomorrow" change the value in column F to "Tomorrow", if the value in K is equal to "today" then change the value in column F to "Today" otherwise, leave it alone... Does that sound right? – MattKing Feb 06 '23 at 21:09

2 Answers2

1

Try:

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

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

  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("Today");
    } else if (dateDifference == 1) {
      group.setValue("Tomorrow");
    }
  }
}

Result:

enter image description here

Let me know if this helps!

Logan
  • 1,691
  • 1
  • 4
  • 11
  • Thank you, it works perfectly. If you don't mind, I have one more question. I tried to add a condition for the timeline to change to "this week" if the do date falls within this week. I found the dates of the first day and the last day of this week and then compared them to the date of the do date. I think I wrote it correctly, but it's not working. Would you mind taking a look at it? I added it in to my original question. – user6738171 Feb 07 '23 at 17:18
  • @user6738171 No worries. For your follow up question I suggest you post it as another question since it is mainly focused on how you can check if both dates are in the same week number and not on the subject which is setting value to the dropdown. However, here's a reference link that may help: https://stackoverflow.com/questions/69325700/check-if-two-dates-are-present-in-same-week-or-not-in-js – Logan Feb 07 '23 at 22:29
0

A start:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Your Sheet Name" && e.range.columnStart == 11 && e.range.rowStart > 3 && e.value) {
    //this will detect a user edit in column K and row > 3 what you wish to do with it is upto you
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54