How to change cell value based on today's date in Google Sheets

Question

I have a table like this one, but I'm looking to automate the change of status when a given date is reached. I've looked into setValue(), but I still need your help to make it work : I'm more or less desperately trying to write something that will change 'Status' to 'Terminated' when today's date match with the one in 'End'.

Thank you for your help!!!

In your situation, unfortunately, from your sample image, I cannot understand that whether your date is the date object, and also I cannot understand the columns and rows. So, can you provide the sample Spreadsheet? By the way, in your situation, how is the values of "End" edited? — Tanaike, Feb 03 '22 at 05:00

score 0 · Accepted Answer · answered Feb 03 '22 at 05:44

Try, assuming status in in column A and end date in column C

function myFunction() {
  var d = new Date()
  var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Feuille 1')
  for (var i = 2; i <= f.getLastRow(); i++) {
    if (f.getRange(i, 3).getValue() - d < 0){
      f.getRange(i,1).setValue('Terminé')
    }
  }
}

How to change cell value based on today's date in Google Sheets

1 Answers1