I'm creating a lost&found log for work, I have a dropdown list with options like "Collected", "Sent to guest", "Donated" etc.

I'd like to have the sheet automatically select 'Donated' after a certain date (maybe after 1 month) IF an option hasn't already been selected.

E.G. If somebody inputs a found pair of gloves on Nov 2nd, The empty dropdown will automatically change to 'Donated' on Dec 2nd unless somebody already selected another option (E.G. 'Collected').

CodePudding user response：

You can accomplish this by either making use of Apps Script to check the conditions and make the corresponding changes or by directly implementing Sheets functions.

• Sheets Functions

  You can achieve the desired outcome by putting this formula on each status cell:

  =IF(TODAY()-D4>30,"Donated","")
  

  It will work as intended, the cell will not display anything until there are 30 days between the TODAY() date and the date provided in the D4 cell. If there are, the cell will display Donated. If another option from the drop-down is selected, that will erase the formula and only the new value will be displayed.

  However, this method is more of a quick hack than an actual robust solution, as several things can go wrong. For instance, if an option from the drop-down is chosen by accident and then it is left blank again, the method will not work anymore for that line as the formula will have been permanently erased. You can read about how TODAY() works here.

• Apps Script (recommended)

  The following script will check that, for every row in the sheet, both the status cell is blank and that subtracting the DATE FOUND is more than 30 days (unfortunately that has to be done in milliseconds).

function myFunction() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadSheet.getSheetByName("Sheet1");

  sheet.getLastRow()
  var range = sheet.getRange(4,1,sheet.getLastRow(), 7);
  //SELECT A RANGE WITH THE 7TH FIRST COLUMNS AND AS MANY ROWS AS NECESSARY 
  //(WE ARE INTERESTED IN DATA STARTING FROM ROW 4TH)

  var values = range.getValues();

  var millisecondsIn30Days = 30 * 24 * 60 * 60 * 1000;

  for(var row = 0; row<values.length; row  ){
    var status = values[row][6]; //STATUS IS THE 6TH ELEMENT IN THE ROW ARRAY (COLUMN G)

    if(status == "" && (new Date() - values[row][3]) > millisecondsIn30Days){
      //MORE THAN 30 DAYS AFTER FOUND
      sheet.getRange(row 4,7).setValue("DONATED");      
    }
  }
}

Of course, that script can be run manually or the job can be automated with an installable trigger that runs, for example, every month. You can read more about how to set up such triggers here.