1

I have just started Google Application Script. I have a folder which has different sub folders and every sub folder contains atleast one csv file. Now i want to extract all of those files from these sub folders and merge into single google sheet. I have one function that iterates through all sub directories and list all the sub folders and one function that merge csv files which are present in single folder. Now is there any way that we can combine these functions and get the desired result. Any help would be appreciated. Thank you

//////////////Merge all csv files in a Folder/////////////////////

    function mergeSheets() {
      /* Retrieve the desired folder */
      var myFolder = DriveApp.getFoldersByName("Apr_05_2013").next();  
      /* Get all spreadsheets that resided on that folder */
      var spreadSheets = myFolder.getFilesByType("text/csv");
      if (spreadSheets.hasNext()) {
        /* Create the new spreadsheet that you store other sheets */
        var newSpreadSheet = SpreadsheetApp.create("Merged Sheets");
        var newSheet = newSpreadSheet.insertSheet("import_");
        /* Iterate over the spreadsheets over the folder */
        while(spreadSheets.hasNext()) {
          var sheet = spreadSheets.next();
          /* Load the csv data */
          var csvData = Utilities.parseCsv(sheet.getBlob().getDataAsString(), ",");
          /* Copy the sheet to the new merged Spread Sheet */

          for (var i=0;i<csvData.length;i++) {
            newSheet.appendRow(csvData[i]);
          }
        } 
      }   
    }

/////////////////**List all Sub folders which a folder contains**///////////////////////////


    function genFolderTree() {

      try {

      var foldername = 'File2013';
      var folderlisting = 'TreeView_' + foldername;

      var parentFolder = DriveApp.getFoldersByName(foldername).next();


      var ss = SpreadsheetApp.create(folderlisting);
      var sheet = ss.getActiveSheet();
      var frontCell = [];
      sheet.appendRow([foldername]).getCurrentCell().setFontWeight('bold').setFontColor('red');
      frontCell.push(" ");
      getChildNode(parentFolder,sheet,frontCell);
      var files = parentFolder.getFiles();
      while (files.hasNext()) {
        frontCell.push(files.next().getName());
        sheet.appendRow( frontCell);
        frontCell.pop();
        }

      } catch (e) {

        Logger.log(e.toString());

      }

    }


    function getChildNode(parent,sheet,frontCell) {

      var childFolders = parent.getFolders();
      while (childFolders.hasNext()) {

        var childFolder = childFolders.next();

        frontCell.push(childFolder.getName())
        sheet.appendRow(frontCell);
        sheet.getRange(sheet.getLastRow(), frontCell.length).setFontWeight('bold').setFontColor('red');
        frontCell.pop();
        var files = childFolder.getFiles();
        frontCell.push(" ");
        var start_row = 0;
        var row_no = 0;
        while (files.hasNext()) {
          frontCell.push(files.next().getName());
          sheet.appendRow(frontCell);
          if(row_no==0){
            start_row = sheet.getLastRow();
          }
          row_no=row_no+1;
          frontCell.pop();
        }
        if(row_no>0){
          var range;
          range = sheet.getRange(start_row, frontCell.length,row_no);
          // The row grouping depth is increased by row_no.
          range.shiftRowGroupDepth(1);
        }

        // Recursive call for any sub-folders
        getChildNode(childFolder,sheet,frontCell);
        frontCell.pop();
      }

    }
Improve this question

1 Answer 1

Reset to default
2

Recursing Subfolders to merge CSV data

This function recurses through the subfolders of the "Main Data Folder" and reads all of the csv files and merges them together and saves the merged file into the "Merged File Folder". You will need to provide the main data folder id and the merged file folder id. It also returns the merged data file Id and appends it into the active sheet.

function loadMergeFile() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.clearContents();
  var data=Utilities.parseCsv(DriveApp.getFileById(mergeCSVs()).getBlob().getDataAsString());
  data.forEach(function(r,i){sh.appendRow(r)});
}

function mergeCSVs() {
  var mfldr=DriveApp.getFolderById('Merged File Folder Id');//merged data file saved in this folder
  var folder=DriveApp.getFolderById('Main Data Folder Id');  
  var ts=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"yyyyMMdd_HH:mm:ss")
  var fn=Utilities.formatString('MergedCSV-%s',ts)
  var mfile=mfldr.createFile(fn,'','text/csv');
  getFnF(folder,mfile.getId());
  return mfile.getId();//return the id so that the loadMergeFile can retrieve the data from the file.
}

function getFnF(folder,id) {
  var mfile=DriveApp.getFileById(id); 
  var files=folder.getFilesByType(MimeType.CSV)
  while(files.hasNext()) {
    var file=files.next();
    mfile.setContent(mfile.getBlob().getDataAsString() + file.getBlob().getDataAsString());
  }
  var subfolders=folder.getFolders() 
  while(subfolders.hasNext()) {
    var subfolder=subfolders.next();
    getFnF(subfolder,id);//The function calls itself as it moves into each subfolder
  }
}

I used the following function to create some test data to test the above function. As you can see it was all numbers and nothing complicated. So depending upon the complexity of your data you may have to make some adjustments.

function createCSVsInFolders() {
  var mainfldr=DriveApp.getFolderById('Main Data Folder Id');
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('csvdata');//set up test data in this sheet
  var vA=sh.getDataRange().getValues();
  var csv='';
  vA.forEach(function(r,i){csv+=r.join(',') + '\r\n'});
  Logger.log(csv);
  var fldrA=['folder1','folder2','folder3','folder4'];
  var fileA=['file1.csv','file2.csv','file3.csv','file4.csv'];
  for(var i=0;i<fldrA.length;i++) {
    var folder=mainfldr.createFolder(fldrA[i]);
    for(var j=0;j<fileA.length;j++) {
      folder.createFile(fileA[j],csv,MimeType.CSV)
    }
  }
}

The csvdata sheet:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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