Google-sheets – Get List of *new* Google Drive Files within Specific Folder (including subfolders) into Google Sheet

google sheetsgoogle-apps-scriptgoogle-drive

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.

Best Answer

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