Google Sheets – Last Modified Date of Files in Google Drive Folder

google sheetsgoogle-apps-scriptgoogle-drive

I am creating a spreadsheet with a list of procedures and instructions that are currently scattered in a shared Google Drive folder. I am planning to make a column that would show the last modified date of the file. I am aware that documents show a timestamp of the last modification and you can easily check it while in the folder.

RichGonzalez together with OnenOnlyWalter had posted something (Auto-updating column in Google Spreadsheet showing last modify date) that seems similar to my problem but I have just recently started learning C++ and coding is still kind of a mystery for me so I am not sure how to implement this in my case.

Is it possible to create an auto-updating column in a Google Sheets that could suck up the last modified date directly from the document?

Best Answer

Here is a script that pulls all file names and last-updated from timestamps from a given folder, sorting files by their names.

The folder is specified by its Id, which is what you see at the end of folder URL after drive.google.com/drive/folders/

The logic of the script is simple: files is an iterator for all files in the folder, from which the files are pulled out. Then the output array is sorted and placed in the current sheet, starting with the cell A2 (that is, row 2 column 1 in getRange method).

function files() {
  var folderId = 'enter folder Id here';
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  var output = []; 
  while (files.hasNext()) {
    var file = files.next();
    output.push([file.getName(), file.getLastUpdated()]);
  }
  output.sort(function(a, b) {
    return a[0] == b[0] ? 0 : a[0] < b[0] ? -1 : 1;
  });
  SpreadsheetApp.getActiveSheet().getRange(2, 1, output.length, output[0].length).setValues(output);
}

Related Solutions

Google Sheets – How to Auto-Update Column with Last Modify Date

Ok, here is a modified version of the code in my previous answer:

function onEdit(e) {
  // Your sheet params
  var sheetName = "MySheet";
  var dateModifiedColumnIndex = 2;
  var dateModifiedColumnLetter = 'B';

  var range = e.range; // range just edited
  var sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {
    return;
  }

  // If the column isn't our modified date column
  if (range.getColumn() != dateModifiedColumnIndex) { 
    var row = range.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm:ss");
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
    dateModifiedRange.setValue(time);
  };
 };

What this does is grabs the row of any cell that is changed and assigns a timestamp to the second column of that particular row.

To implement, all you need to do is go into the spreadsheet and click on Tools > Script Editor. In the resulting editor page, just paste this in there. Since this is an onEdit() function, it should work without having to add anything else to the cells of your spreadsheet, just paste it into the editor and save it.

For the timestamp, I set the format to MM/dd/yy and left the time. If you want to change it, you can just change the use of Utilities.formatDate.

Google-drive – Is it possible to see the date/time for a Google Drive folder for the most recent add/modify/delete time/date of something within it

I was trying to find the time for items in the past, found it by clicking the (i) for details, then clicking the activity tab. On the date that's shown hover over and the time is displayed!

Best Answer

Related Solutions

Google Sheets – How to Auto-Update Column with Last Modify Date

Google-drive – Is it possible to see the date/time for a Google Drive folder for the most recent add/modify/delete time/date of something within it

Related Topic