0

I've seen some helpful answers to this question, but none that have the script specify to list only new files/folders added to the parent folder listed. I think what I'm trying to do is combine this from user Tedinoz:
https://webapps.stackexchange.com/a/121447/250578 and this
https://gist.github.com/wdziemia/c45a59d13510fcd2f273f8e0583cb8d9 (except not for email notification).

Is there an easy modification to Tedinoz's code to only find files added within the last 24 hours (for example)? I do not want to list files that already exist.

  • Welcome. I think you need to "look" at every file but then evaluate the creation date getDateCreated() against your reference date (whether today() or some other date). – Tedinoz Jul 15 at 9:01
0

You want to list GDrive files that are recently created, either "today" or within a time span that you decide. Your existing script doesn't include a value for the date that a created was created.

I suggest that there are two options, both of which include adding dateCreated() to the information obtained via the script.

Option#1 - Date comparison within the script.
Option#2 - Date filter/query with a spreadsheet.
Of the two, I suggest that the second is more flexible, but this is a matter of opinion.

Script: with Date Comparison
Personally I don't prefer this option because you you can't see what files were NOT selected.

Note:

  • var foldername = "<<Enter your folder name>>"; - enter your own Google Drive folder name
  • var sheetname = "<<Enter your output sheet name>>"; - enter the name of the sheet where the data will output. not the name of the spreadsheet, just the name of the sheet in the spreadsheet

function ListFilesandFoldersDateComparison() {
  
  // start
  /* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
  Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
  */

  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = "<<Enter your folder name>>";
  var sheetname = "<<Enter your output sheet name>>";
  // declare this sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
    
  // establish dates data
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var endDate = new Date();
  var endDatevalue = endDate.valueOf();
  var daysadjust = 7;
  var startDate = endDate -(7*MILLIS_PER_DAY);
  var startDatevalue = startDate.valueOf()

  // clear any existing contents
  sheet.clear();
 
  // append a header row
  sheet.appendRow(["Folder","File Name", "Date Created","Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);

  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  
  // declare an array to push data into the spreadsheet
  var data = []; //individuals fields in records
  var newdata= []; // whole records

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();

  // loop through files in this folder
  while (myfiles.hasNext()) { //start loop through main folder
    var myfile = myfiles.next();   
    var fname = myfile.getName();
    var fcreate = myfile.getDateCreated();
    var fdate = myfile.getLastUpdated(); 
    var fsize = myfile.getSize();
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var fdesc = myfile.getDescription();
    var ftype = myfile.getMimeType();

    if (fcreate.valueOf()>=startDatevalue && fcreate.valueOf()<=endDatevalue){

      // Populate the array for this file
      data = [ 
        foldersnext,
        fname,
        fcreate,
        fdate,
        fsize,
        furl,
        fid,
        fdesc,
        ftype
      ];

      // push the record onto the array
      newdata.push(data);


    } //end if
   
  } // end while loop through main folder

  // Now get the subfolders
  // subfolders is a Folder Iterator
  var subfolders = foldersnext.getFolders();
  
  // now start a loop on the SubFolder list
  while (subfolders.hasNext()) {// start loop of subfolders
    var subfolderdata = [];
    var mysubfolders = subfolders.next();
    var mysubfolder = mysubfolders.getName();    
    // Get the files
    // mysubfiles is a File Iterator
    var mysubfiles = mysubfolders.getFiles();
 
    // now start a loop on the files in the subfolder
    while (mysubfiles.hasNext()) { // start 'while' loop of files
      var smyfile = mysubfiles.next();
      var sfname =  smyfile.getName();
      var sfcreate =  smyfile.getDateCreated();
      var sfdate =  smyfile.getLastUpdated(); 
      var sfsize =  smyfile.getSize();
      var sfurl =  smyfile.getUrl();
      var sfid =  smyfile.getId();
      var sfdesc =  smyfile.getDescription();
      var sftype =  smyfile.getMimeType();
      if (sfcreate.valueOf()>=startDatevalue && sfcreate.valueOf()<=endDatevalue){
        subfolderdata = [ 
          (foldersnext+"/"+mysubfolder),
          sfname,
          sfcreate,
          sfdate,
          sfsize,
          sfurl,
          sfid,
          sfdesc,
          sftype
        ];

      newdata.push(subfolderdata);
      } // end if
   }// Completes while listing of the files in the sub-folder
  } // end loop in the sub-folders

  // get the number of records in the array
  var newdataLen = newdata.length;
  var outputrange = sheet.getRange(2, 1, newdataLen,9)
  outputrange.setValues(newdata);
  return;
} // end of function

Date Comparison within script


Script: List all files, then Date Comparison within by query/filter

Note:

  • var foldername = "<<Enter your folder name>>"; - enter your own Google Drive folder name
  • var sheetname = "<<Enter your output sheet name>>"; - enter the name of the sheet where the data will output. not the name of the spreadsheet, just the name of the sheet in the spreadsheet

function ListAllFilesandFolders() {
  
  // List all files - analyse dates in query/filter
  
  /* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
  Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
  */

  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = "<<Enter your folder name>>";
  var sheetname = "<<Enter your output sheet name>>";
  // declare this sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
    
  // clear any existing contents
  sheet.clear();
 
  // append a header row
  sheet.appendRow(["Folder","File Name", "Date Created","Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);

  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  
  // declare an array to push data into the spreadsheet
  var data = []; //individuals fields in records
  var newdata= []; // whole records

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();

  // loop through files in this folder
  while (myfiles.hasNext()) { //start loop through main folder
    var myfile = myfiles.next();   
    var fname = myfile.getName();
    var fcreate = myfile.getDateCreated();
    var fdate = myfile.getLastUpdated(); 
    var fsize = myfile.getSize();
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var fdesc = myfile.getDescription();
    var ftype = myfile.getMimeType();

    // Populate the array for this file
    data = [ 
      foldersnext,
      fname,
      fcreate,
      fdate,
      fsize,
      furl,
      fid,
      fdesc,
      ftype
    ];

    // push the record onto the array
    newdata.push(data);
   
  } // end while loop through main folder

  // Now get the subfolders
  // subfolders is a Folder Iterator
  var subfolders = foldersnext.getFolders();
  
  // now start a loop on the SubFolder list
  while (subfolders.hasNext()) {// start loop of subfolders
    var subfolderdata = [];
    var mysubfolders = subfolders.next();
    var mysubfolder = mysubfolders.getName();    
    // Get the files
    // mysubfiles is a File Iterator
    var mysubfiles = mysubfolders.getFiles();
 
    // now start a loop on the files in the subfolder
    while (mysubfiles.hasNext()) { // start 'while' loop of files
      var smyfile = mysubfiles.next();
      var sfname =  smyfile.getName();
      var sfcreate =  smyfile.getDateCreated();
      var sfdate =  smyfile.getLastUpdated(); 
      var sfsize =  smyfile.getSize();
      var sfurl =  smyfile.getUrl();
      var sfid =  smyfile.getId();
      var sfdesc =  smyfile.getDescription();
      var sftype =  smyfile.getMimeType();
    
      subfolderdata = [ 
        (foldersnext+"/"+mysubfolder),
        sfname,
        sfcreate,
        sfdate,
        sfsize,
        sfurl,
        sfid,
        sfdesc,
        sftype
      ];
      
      newdata.push(subfolderdata);
    
   }// Completes while listing of the files in the sub-folder
  } // end loop in the sub-folders

  // get the number of records in the array
  var newdataLen = newdata.length;
  var outputrange = sheet.getRange(2, 1, newdataLen,9)
  outputrange.setValues(newdata);
  return;
} // end of function

Query:

=query(allfiles!A1:I,"select A, B, C,I where C >= date '"&TEXT(B3,"yyyy-mm-dd")&"' and C <= date '"&TEXT(B1,"yyyy-mm-dd")&"'")

End Day: Cell B1: =today()

Days Ago: Cell B2: 7 (as in a "week")

Start Day: Cell B3: =B1-B2


Filter all files by Query

| improve this answer | |
  • Thank you @Tedinoz. This mostly worked and helped me better understand the script. But it seems like it's only searching the parent folder and isn't returning any subfolders. Do I need to add another function line for that? – Alexis Jul 21 at 1:23
  • Doh. In Option#1, Line 103 should read ` if (sfcreate.valueOf()>=startDatevalue && sfcreate.valueOf()<=endDatevalue){`. The "allfiles" version should be already returning any/all files in subfolders because there is no logic statement. – Tedinoz Jul 21 at 13:01
  • Awesome, looks like that worked! Thanks @Tedinoz! – Alexis Jul 21 at 19:44

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.