CODEWITHSUNDEEP
csvgoogle-apps-scriptgoogle-sheets
Agha Musa Khan
Agha Musa Khan

Reputation: 27

Merge Multiple CSV Files in single file on gdrive

I've been trying to merge multiple csv files into single csv file from Google drive Folder, but I'm unable to do it as it works well with google sheets but not with csv files.

I used the code that is already available on stackoverflow.com but it didn't work well with my requirements.

function mergeSheets() {

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName("Test 1").next();

  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType("MimeType.csv");

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

  /* 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); 
    }
  }      
}

It does create a new sheet but without any csv data.

Upvotes: 1

Views: 5608

Answers (3)

Cooper
Cooper

Reputation: 64140

function mergeCSVFiles(folderId,filename) {
  var folder=DriveApp.getFolderById(folderId);
  var files=folder.getFilesByType(MimeType.csv);
  var s='';
  while(files.hasNext()) {
    var file=files.next();
    s+=file.getBlob().getDataAsString();
  }
  var currentFiles=DriveApp.getRootFolder().getFilesByName(filename);
  while(currentFiles.hasNext()) {
    currentFiles.next().setTrashed(true);
  }
  DriveApp.getRootFolder().createFile(filename,s,”text/csv”);//The merged file is in your root folder. 
}

Upvotes: 0

ZektorH
ZektorH

Reputation: 2770

The main problems with your solution were:

  1. You create the new merged sheet even if you don't find any CSV to merge.
  2. You were trying to get CSV files with the wrong mimetype string. (invalid "MimeType.csv" vs correct "text/csv". Alternatively, just MimeType.csv without quotes would work too.)
  3. You assumed that opening a csv with Spreadsheets app was possible, when it isn't.

To fix those issues I have:

  1. Added a check before creating the merge file.
  2. Fixed the mimetype string to the correct one.
  3. Switched the SpreadsheetApp.openById(sheet.getId()); with Utilities.parseCsv(sheet.getBlob().getDataAsString(), ","); so we can open a csv file propperly.
  4. Added logic to add the rows one by one.

Here is some code that works in putting multiple csv files into a single spreadsheet, each file on its own sheet.

function mergeSheets() {
  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName("Test 1").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");
    /* 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 */
      var newSheet = newSpreadSheet.insertSheet("import_"+sheet.getName());
      for (var i=0;i<csvData.length;i++) {
        newSheet.appendRow(csvData[i]);
      }
    } 
  }   
}

Hope this helps!

Upvotes: 2

Sourabh Choraria
Sourabh Choraria

Reputation: 2331

Here's my solution to it:

  • involves using MimeType.CSV (the OP just had to avoid using quotes 😊)
  • involves adding rows from one csv to the new Spreadsheet - altogether, instead of iterating through rows one after the other

Try this -

function mergeSheets() {

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName("Test 1").next();

  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType(MimeType.CSV);

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

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

    var sheet = spreadSheets.next();


    var file = DriveApp.getFileById(sheet.getId());
    var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
    var newSheet = newSpreadSheet.getActiveSheet();
    newSheet.getRange(newSpreadSheet.getLastRow() + 1, newSpreadSheet.getLastColumn()+1, csvData.length, csvData[0].length).setValues(csvData);



  }      
}

I'm assuming you must've gotten the idea of the original code from here - Merge multiple tabs, download as CSV

I found my inspiration from this article here.

Upvotes: 1

Related Questions