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!!
Note: I assume that
column H
is a typo and should becolumn I
on the statementSo if the user enters the standard work date (Column J), column H will automatically change it to 1 year later from the SW Date.
sincecolumn H
is a drop-down from your example spreadsheet andcolumn I
is where the date oncolumn 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.
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;
});
}