0

I've seen some helpful answers, but none that have a script that lists only new files/folders from a specified parent folder.

I think what I'm trying to do is combine @Tedinoz's WebApps answer to "Get a list of Drive files into a spreadsheet" and Wojciech Dziemianczyk's gist script for emailing Drive updates (but without the need for email notifications).

I don't want to list preexisting files so, for example, is there an easy modification to Tedinoz's code that will only include files added within the last 24 hours?

0

1 Answer 1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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