I've a Google sheet which contains two columns first is "Status" column and second is "RegDate" column. I want to update values in the "Status" column if Today's date matches with the RegDate column. Here, I am attaching a sample google sheet for reference.

Basically, I want to write an automated script which will run daily at a certain time and check if the date on which script is running matches with the date present in RegDate column then it should change the corresponding values in "Status" column. For Eg. If the script is running on 3 Aug 2022 then the row where RegDate is 03-08-2022 should be considered and then "Inactive" value in Status column should be updated to "Active".

I do not have any idea how to automate this, as far as I've researched I need to write a Google AppScript for the same and use a time-driven trigger to run the script daily (Correct me if I am wrong here). I want to know how can I proceed with this code any help on this would be appreciated. Please also mention any threads which can help me in solving this.

CodePudding user response：

Solution:

• Create a daily trigger. You can do this manually (following these steps), or programmatically (executing the function installTrigger below once).
• The daily trigger will cause a specific function to execute once daily. On this function, iterate through your range, and for each row, check whether column B date is the same one as today. If that's the case, change column A value to Active.

function updateStatus() {
  const now = new Date();
  const day = now.getDate();
  const month = now.getMonth();
  const year = now.getFullYear();
  const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Change according to your preferences
  const range = sheet.getRange("A2:B"   sheet.getLastRow());
  const updatedVals = range.getValues().map(row => {
    const date = row[1];
    if (typeof date.getMonth === 'function' && date.getDate() === day && date.getMonth() === month && date.getFullYear() === year) {
      row[0] = "Active";
    } else {
      // row[0] = "Inactive"; // Uncomment if you want to change status from 'Active' to 'Inactive' if date is not today
    }
    return row;
  });
  range.setValues(updatedVals);
}

function installTrigger() {
  ScriptApp.newTrigger("updateStatus")
    .timeBased()
    .everyDays(1)
    .create();
}

Note:

I'm unsure whether you want to change status from Active to Inactive if the row date is not today (this was not made explicit on your question). If that's the case, uncomment the corresponding line (row[0] = "Inactive") on the sample above.

Reference:

• everyDays(n)