TransWikia.com

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

Web Applications Asked on November 3, 2021

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.

One 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

Answered by Tedinoz on November 3, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP