Script for last edited/modified time & date

user_19595786 0
Viewed 72

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?

google-sheets last-modified datetime javascript
1 Answers

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.

enter image description here

Result:

enter image description here

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:

enter image description here

References:

user_179265910
Related