Script for last edited/modified time & date

Question

I have been searching around for a script that will give me the time & date that a google sheet was last modified.

I have a list of all the google sheet workbook IDs in column E of a sheet called 'Planner Directory' and I would like it to return the time & date last modified in column F.

This would be really helpful if anyone can assist?

Answer 1

Since you already have all the data in a Google Sheet, I think the easiest way to get this working automatically is using Google Apps Script. I will leave an example of how it would work.

Try this code:

function myFunction() {
  var ss = SpreadsheetApp.getActive().getSheetByName("Sheet20"); // Change it to the name
                                                                 // of the sheet you. are using
  var range = ss.getDataRange();
  var values = range.getValues();
  for(var i=1; i<values.length; i++)
  {
    values[i][1] = lastModifiedTime(values[i][0]); // 0 is the column number in my testing sheet
                                                   // where the IDs are.
                                                   // 1 is the column number where the time will be.
  }
  range.setValues(values);
}

function lastModifiedTime(id)
{
  var file = DriveApp.getFileById(id);
  return file.getLastUpdated();
}

Input data:

Replace the Sheet ID fields with valid IDs.

Result:

Remember to change the format of the column where you want you have the last modified time to Number > Date time.

Now, in order for you to automate this script you can use a time-driven trigger from Google Apps Script so that the script runs every 5, or 10 minutes for example and so that you can always get the latest time in the results.

Trigger:

References:

• Method: getLastUpdated()
• Time-driven triggers

Answer 2

I was able to use the suggested script to get the last modified date of a file, which is great but I am having two issues. In the next column of the spreadsheet I had a formula which would tell me if the last modified date is today's date. =if(C2=Today(),"Updated","Not Updated")

1. The formula doesn't recognize the last modified date that is entered by the script, even though I have that column format set to Date.

2. When the script suggested in this thread runs, it wipes out the formula entirely and replaces it with static text.