How to avoid creating a new file by counting the number of files in a folder

Question

I have a software on my computer where when i run reports from it auto puts out a folder with 4 csv files. Every time i run a report i get a new folder with 4 new files. These folders and files automatically sync with one of my folders in GDrive. I have the following script set up to take the 4 spreadsheets and combine them into 1 new sheet in the same folder after the file has been uploaded. I am planning on setting up the script to run on a time-based event, it iterates through all its sub folders and combines the files into the 1 file.

I am trying to figure out how to make sure if a file has already been generated for that folder that it skips that folder. I have the following code but it is not working and multiple files are still being generated. My initial thought was to count the basic number of files i have in the folder and if its greater than that number the script should not be ran. I believe i have an error in my if statement code.

/* Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */

/* Top Level Google Drive Folder ID (not absolute root, just the top of the directory 
you want to work from)*/
var TopDriveFolderID = "0B2rN5b8fW77ldXZXOXFLZGlSamc";
var BaseNumberOfFilesInFolder = 5;

/* End Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

/* Body >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

/* Functions to iterate through all sub folders */
function listFolders() {
  var parentFolder = DriveApp.getFolderById(TopDriveFolderID);
  var childFolders = parentFolder.getFolders();
  var cnt = 0;

  while(childFolders.hasNext()) {

    var child = childFolders.next();
    //    Logger.log(child.getName() + " |Drive_ID: " + child.getId());
    var newSpreadSheetChildId = child.getId();
    // **the newSpreadSheetChildId Variable is also the folder ID,
    //    hence they are used interchangeably **

    /* SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>*/    

    /* Name of combined Spreadsheet*/
    var newSpreadSheetName = DriveApp.getFolderById(newSpreadSheetChildId).getName();

    /* Name of Folder to be looked up*/
    // var masterFolder = "reports";

    /* Retrieve the desired folder */
    //var myFolder = DriveApp.getFoldersByName(masterFolder).next();
    var myFolder = DriveApp.getFolders().next();

    /* Get all spreadsheets that resided on that folder */
    var spreadSheets = myFolder
       .getFilesByType("application/vnd.google-apps.spreadsheet");
    var spreadSheetName = myFolder.getName();
    //    Logger.log(spreadSheetName);

    /* Create the new spreadsheet that you store other sheets */  
    var newSpreadSheet = SpreadsheetApp.create(newSpreadSheetName);


    /* Iterate over the spreadsheets over the folder */
    while(spreadSheets.hasNext()) {

      cnt++;
      Logger.log(cnt + "early");

      var sheet = spreadSheets.next();

      /* Open the spreadsheet */
      var spreadSheet = SpreadsheetApp.openById(sheet.getId());

      if (cnt > BaseNumberOfFilesInFolder) {

        /* Get all its sheets */
        for(var y in spreadSheet.getSheets()) {
          Logger.log(cnt + "late");
          /* Copy the sheet to the new merged Spread Sheet */
          spreadSheet.getSheets()[y].copyTo(newSpreadSheet);

          /* In order to move the file to the folder we want, and because 
          google considers the SpreadSheet a Google Spreadsheet
          instead of a file, we have to convert the SpreadSheet to a file in
          order to move it. Thats what the next 2 lines of code do.*/
          var getNewSSid = newSpreadSheet.getId();
          var SStoGFile = DriveApp.getFileById(getNewSSid);
          /* Actually moving the file*/
          DriveApp.getFolderById(newSpreadSheetChildId).addFile(SStoGFile);
          /* Deleting the duplicate file that's created in the process*/
          var rootFolder = DriveApp.getRootFolder();
          DriveApp.getRootFolder().removeFile(SStoGFile)

        }
      }  else {
        continue;
      }

      /* End SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>*/       


      getSubFolders(child); 

    }

  }

  function getSubFolders(parent) {
    parent = parent.getId();
    var childFolder = DriveApp.getFolderById(parent).getFolders();
    while(childFolder.hasNext()) {
      var child = childFolder.next();
      Logger.log(child.getName());
      getSubFolders(child);
    }
    return;
  }
  /* End iterate through Sub Folders */

}

I inserted few break lines to avoid the horizontal scroll bar for readability. Also removed the Javascript / HTML / CSS snipped as Google Apps Script doesn't run here. — Rubén, Oct 15 '16 at 16:42

score 0 · Answer 1 · answered Oct 15 '16 at 17:35

0

I think you are right about the if statement. Try changing:

if (cnt > BaseNumberOfFilesInFolder) {

to

if (cnt >= BaseNumberOfFilesInFolder) {

answered Oct 15 '16 at 17:35

Ed Nelson

10,015
2
27
29

I tried incrementing the "BaseNumberOfFilesInFolder" to 5 and then using an >= operater but no luck. – Zac Henson Oct 17 '16 at 00:06
My logger.log() function that i am running on my cnt variable is not logging anything. I think there is a problem with my javascript. Anyone have any ideas? – Zac Henson Oct 17 '16 at 00:07
Check out this answer from Sandy Good http://stackoverflow.com/questions/30328636/google-apps-script-count-files-in-folder I would add var fname=theFolder.getName() after var files = theFolder.getFiles(); to make sure what file you are counting. If I understand your code, you only want to combine the files if there are 4 in the folder. – Ed Nelson Oct 17 '16 at 02:12

score 0 · Answer 2 · answered Oct 17 '16 at 22:31

Here is the code i came up with that does what I mention above.

/* Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */

/* Top Level Google Drive Folder ID */
var TopDriveFolderID = "0B2rN5b8fW77ldXZXOXFLZGlSamc";
var BaseNumberOfFilesInFolder = 4;

/* End Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

/* Body >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

/* Functions to iterate through all sub folders */
function listFolders() {
  var parentFolder = DriveApp.getFolderById(TopDriveFolderID);
  var childFolders = parentFolder.getFolders();
  while(childFolders.hasNext()) {
    var child = childFolders.next();
    //    Logger.log(child.getName() + " |Drive_ID: " + child.getId());
    var newSpreadSheetChildId = child.getId();
    // **the newSpreadSheetChildId Variable is also the folder ID,
    //    hence they are used interchangeably **
    
    /* SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/    
    
    /* Name of combined Spreadsheet*/
    var newSpreadSheetName = DriveApp.getFolderById(newSpreadSheetChildId).getName();
    
    /* Name of Folder to be looked up*/
    // var masterFolder = "reports";
    
    /* Retrieve the desired folder */
    //var myFolder = DriveApp.getFoldersByName(masterFolder).next();
    var myFolder = DriveApp.getFolders().next();
    
    /* Get all spreadsheets that resided on that folder */
    var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
    var spreadSheetName = myFolder.getName();
    //    Logger.log(spreadSheetName);
    
    /* Create the new spreadsheet that you store other sheets */  
    var newSpreadSheet = SpreadsheetApp.create(newSpreadSheetName);
    
    /* Check if a file needs to be created or if one already exists*/  
    var theFolder = DriveApp.getFolderById(newSpreadSheetChildId);
    var files = theFolder.getFiles();
    var fname = theFolder.getName();
    
    var cnt = 0;
    var file;
    
    /* Check the quantity of files in a folder to see if you need to combine the sheets and create a file*/  
    while (files.hasNext()) {
      cnt++;
      file = files.next();
      //Logger.log(file.getName());
      if (cnt > BaseNumberOfFilesInFolder) {
        Logger.log("File already exists")
        break;
      } 
      ;
    };
    
    // If a file needs to be created this checks it and creates it
    Logger.log(cnt);
    if (cnt > BaseNumberOfFilesInFolder){
      Logger.log("No file needs to be created")
    }else{
      /* Iterate over the spreadsheets over the folder */
      while(spreadSheets.hasNext()) {
        
        var sheet = spreadSheets.next();
        
        /* Open the spreadsheet */
        var spreadSheet = SpreadsheetApp.openById(sheet.getId());
        
        
        /* Get all its sheets */
        for(var y in spreadSheet.getSheets()) {
          
          /* Copy the sheet to the new merged Spread Sheet */
          spreadSheet.getSheets()[y].copyTo(newSpreadSheet);
          
          
          /* In order to move the file to the folder we want and because 
          google considers the SpreadSheet a Google Spreadsheet
          instead of a file, we have to convert the SpreadSheet to a file in
          order to move it.Thats what the next 2 lines of code do.*/
          var getNewSSid = newSpreadSheet.getId();
          var SStoGFile = DriveApp.getFileById(getNewSSid);
          /* Actually moving the file*/
          DriveApp.getFolderById(newSpreadSheetChildId).addFile(SStoGFile);
          /* Deleting the duplicate file that was created in the process*/
          var rootFolder = DriveApp.getRootFolder();
          DriveApp.getRootFolder().removeFile(SStoGFile);
          
        }
      }  
      Logger.log("New File was created with the name - " + newSpreadSheetName)
    }
  };
  
  
  /* End SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/       
  
  
  getSubFolders(child); 
  
}


function getSubFolders(parent) {
  parent = parent.getId();
  var childFolder = DriveApp.getFolderById(parent).getFolders();
  while(childFolder.hasNext()) {
    var child = childFolder.next();
    Logger.log(child.getName());
    getSubFolders(child);
  }
  return;
}
/* End iterate through Sub Folders */

How to avoid creating a new file by counting the number of files in a folder

2 Answers2