sortinggoogle-sheetsgoogle-apps-script

Find and print last edit dates of preexisting rows in a sheet (Google Sheets)


I have a sheet tracking the details and statuses of multiple projects across different teams. While we have been putting down the date the project was completed, other status updates are typically entered without a date.

Team Project Details Status
Michael Alpha ABCD Status A
Randy Beta XYOZ Status B 12/01

Given that not everyone in the office remembers to put a date even on completions, I would like to find a way to have a column printing the last time any cell in each row was edited, to then be sorted with either the sort function or the basic sorting tools. I can't reasonably do this manually due to how many projects there are.

It is entirely acceptable for the dates for projects that already exist to just be the date that this was put in place.

The end product should look something like this, with the last column ideally being hidden:

Team Project Details Status Update
Michael Alpha ABCD Status A 11/23
Randy Beta XYOZ Status B 12/01 12/01

I don't know where to begin on this. I had added the status column in order to have some Y/N binary sense of whether projects were finished, but as time went on the column ended up containing all types of status updates from other people updating the sheet. Due to security measures, I don't personally have access to the email with the dates of these status updates, so ripping it from gmail is not an option.

It seems that this question is looking for a similar effect. What would be the syntax to have this work for rows within a specific sheet?


Solution

  • How about something simple like this:

    function onEdit(e) {
      const sh = e.range.getSheet();
      if(sh.getName() == "Your Sheet Name") {
        sh.getRange(e.range.rowStart,5).setValue(new Date())
      }
    }