A spreadsheet column with the last modified date of files in a Google Drive folder

Question

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?

Answer 1

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);
}