google-sheetsdrop-down-menuscripting

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


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!!


Solution

  • SUGGESTED SOLUTION

    Note: I assume that column H is a typo and should be column I on the statement 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. since column H is a drop-down from your example spreadsheet and column I is where the date on column J is added by a year.

    If you 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. you can modify the function from an function onEdit(event) to a regular function so that it won't run on every edit and use Manage triggers manually to run it.

    To do this, click on Triggers > + Add Trigger on the left of the Google Apps Script Editor.

    SAMPLE TRIGGER IMAGE

    TRIGGER

    This is the modified version script:

    const myFunction = () => {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var vl = ss.getRange(2, 8, ss.getLastRow(), 2).getValues();
      vl.forEach((r, i) => {
        var ci = r[1];
        var cid = new Date(ci);
        var td = new Date();
        var tdd = new Date(td.getFullYear(), td.getMonth(), td.getDate());
        cid.getTime() == tdd.getTime() ? ss.getRange(i + 2, 8).setValue("Needs SW Owner Review") : null;
      });
    }
    

    OUTPUT

    BEFORE

    AFTER