google-apps-scriptgoogle-sheetssetvalue

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


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'.

enter image description here

Thank you for your help!!!


Solution

  • 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รฉ')
        }
      }
    }