How to automatically set a value in drop-down list based on a date

Question

I read the following post which is 2023. How to automatically set a value in drop-down list based on a value from another cell in row - apps script

And I have it modified for my form, however, it doesn't do anything. No error, nothing in the log. Says executed, but no notes. Here is my code:

function onEdit(event){
  var colI = 9;  // Column Number of "I"

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

    //Get the Due 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(), colI - 1);
    if (dateDifference == 0) {
      group.setValue("Needs SW Owner Review");
    }
  }
}

I know I don't have an else statement, because I only want the value in the drop-down menu to change to Needs SW Owner Review if the Do Date is today's date. It should be noted that the Do Date column is a formula that points to Column J, which is SW Date. This date field is entered by the user and Column I automatically adds 365 days to it. If that date is past, then the Status Column H will change to Needs SW Owner Review, and then an email will go to that owner. Thanks in advance for the help!

I modified the code to ensure it matches all the values of my sheet, column names, numbers, etc. It executes with no errors, but doesnt do anything. I planned to run a trigger based on a time, say midnight every nite but was doing an event first to get it working.

Here is the link to the test sheet. https://docs.google.com/spreadsheets/d/1aW3FAaybXRJryAM9KWkswFCgjBPrNsyaCI9ugfDgAjM/edit?usp=sharing

So if the user enters the standard work date (Column J), column H will automatically change it to 1 year later from the SW Date. I would like to run this on a timer trigger so that when the Do Date is today's date, the value in Column H changes to Needs SW Owner Review automatically.

Thanks for the help!!

Can you share a minimal, reproducible example that the community can use to see what you'd like to do? Like a dummy sheet so we can visualize and replicate what you have. Kindly include your desired output in it as well. — Saddles
– Saddles, Commented Oct 3, 2024 at 21:28
Can you confirm if we were able to address your question, @Jacob Pruet? If so, consider clicking the accept button on the left, which is the check icon, and upvote it (if possible). That way, it's known that the issue has been resolved. You may also review, What should I do when someone answers my question? for more information. — Saddles
– Saddles, Commented Oct 4, 2024 at 18:06

user27425627 · Accepted Answer · 2024-10-03 21:33:59Z

1

It should be noted that the Do Date column is a formula that points to Column J, which is SW Date. This date field is entered by the user and Column I automatically adds 365 days to it.

Well, indeed it should be noted: that's the problem. Here's an extract from Google's guide on simple triggers:

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet.

That means that the source event's range is the cell edited by the user; the onEdit function is not triggered when your formula changes the value of column I. So the first if statement in your code is never true, and thus the function does nothing.

What you need to do is change that condition to check when column J is edited by the user:

function onEdit(event){
  const colI = 9;  // Column Number of "I"
  const sheet = event.source.getActiveSheet();
  const changedRange = event.range;
  if (changedRange.getColumn() === (colI + 1)) {
    // An edit has occurred in Column J
    const row = changedRange.getRow();
    //Get the Due date and current date then set as same time.
    const doDate = sheet.getRange(row, colI).getValue().setHours(12, 0, 0);
    const today = new Date().setHours(12, 0, 0);

    //Get the difference of the 2 dates
    const dateDifference = Math.round((doDate - today) / 8.64e7)
    //Set value to dropdown depending on difference
    const group = sheet.getRange(row, colI - 1);
    if (dateDifference == 0) {
      group.setValue("Needs SW Owner Review");
    }
  }
}

answered Oct 3, 2024 at 21:33

user27425627

1,2714 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jacob Pruet Over a year ago

So right now I have the trigger set to onedit, from spreadsheet-onedit

Jacob Pruet Over a year ago

I would like to run this on a timer say everyday at 6am or so. I am assuming I would have to change the event object? Also, I tried changing mine to point to the user input to column J and still didnt do anything. The log says it fired, but theres nothing in the log nor did the value change. Thanks for the help!!

user27425627 Over a year ago

What? The onEdit trigger fires whenever a user edits the spreadsheet, in which case the function runs automatically.

Jacob Pruet Over a year ago

I figured it out. The script was looking for a difference of 0 between the dates. My date I put in the SW column was negative 0, meaning it was over 1 year old. Once I changed the date to exactly 1 year ago, it worked perfectly! Thanks all!!

Saddles · Accepted Answer · 2024-10-04 15:21:24Z

Collectives™ on Stack Overflow

How to automatically set a value in drop-down list based on a date

2 Answers 2

4 Comments

SUGGESTED SOLUTION

SAMPLE TRIGGER IMAGE

OUTPUT

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

SUGGESTED SOLUTION

SAMPLE TRIGGER IMAGE

OUTPUT

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related