2

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?

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);
}
  • Your code works like a charm. I wonder whether you could edit it so as to be able to include multiple folders in separate sheets identified by the folder's name. e.g.: sheet TESTS, sheet RESULTS, etc. Or have a main folder and its subfolders in the same fashion. e.g. sheet BIOLOGY, sheet subfolderBIOLOGY-TESTS, sheet subsubfolderRESULTS. I hope it makes sense. The code would then be really helpful. We could see what is happening in a whole class by opening just one spreadsheet. – marikamitsos Aug 2 '16 at 7:03
  • @red red wine THANK YOU so, so much. I really appreciate your work. You are the man! – user130137 Aug 8 '16 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.