Insights and Inspirations

Merge CSV Files into one single Sheet with Google Scripts

Google Drive can be very useful to share documents across teams. Its use can be increased by using Google Apps Script to automate certain smaller or bigger tasks and one good example is the merging of several CSV files into a sheet.

The following script takes a folder containing CSV files and outputs a newly created sheet with all CSV data merged together. The CSV files may have different headers and the script will dynamically add them to the sheet and assign the values to the corresponding column.

function MergeCSV() {

  // Input Files containing all CSV files
  var folder = DriveApp.getFolderById("folderIdNumber"); // You find the ID of the folder in the URL after folders/)
  var files = folder.getFiles();

  // Output Spreadsheet
  var book = SpreadsheetApp.create("Final Merged CSV");
  var sheet = book.getActiveSheet();

  // Iterate over all input CSV files
  while (files.hasNext()) {

    // Get CSV File
    var csvFile = files.next();
    var csvSheet = Utilities.parseCsv(csvFile.getBlob().getDataAsString(), ";"); // we specify the semicolon (';') as the CSV delimiter

    // Separating CSV Header from CSV Data
    var csvHeader = csvSheet[0]
    var csvData = csvSheet.splice(1)

    // Get Sheet Headers
    var lastCol = sheet.getLastColumn() || 1 // Defaults to 1 in case there is no data in the sheet present.
    var sheetHeader = sheet.getRange(1, 1, 1, lastCol).getValues()
    var sheetHeader = sheetHeader[0] // Prev. returned a nested list, but we only require the first row. 

    // Compare CSV & Sheet Headers & Append new Headers to Sheet, if necessary
    var difference = csvHeader.filter(x => !sheetHeader.includes(x));
    if (difference.length > 0) {
      sheet.getRange(1, lastCol, 1, difference.length).setValues([difference]); // setValue expects an array of arrays (two-dimensional array) 
    }

    // Since the sheet is dynamic (changing headers), we re-fetch the sheet headers
    var lastCol = sheet.getLastColumn() || 1
    var sheetHeader = sheet.getRange(1, 1, 1, lastCol).getValues()
    var sheetHeader = sheetHeader[0]
    var sheetRowIndex = sheet.getLastRow()

    // 1. Iterate over the CSV file rows
    for (var i = 0; i < csvData.length; i++) {

      sheetRowIndex++; // We wish to write underneath the latest row

      // 2. Iterate over the CSV file cols
      for (var k = 0; k < csvHeader.length; k++) {
        var csvHeaderName = csvHeader[k];

        // 3. We iterate over Sheet Headers & Match with CSV Header
        for (var j = 0; j < sheetHeader.length; j++) {
          if (csvHeaderName === sheetHeader[j]) {
            var dataCol = j + 1 // Index starts at 0, therefore + 1 to get correct Cell Range.
            sheet.getRange(sheetRowIndex, dataCol).setValue(csvData[i][k]);
          }
        }
      }
    }
  }
}


How to reach us

Let's grab a coffee and talk.