Bulk combine CSV files into a single Google Sheet

The following Google Apps Script is designed to take a number of CSV files (structured in an identical way) stored in a Google Drive folder and combine them all into a single Google Sheet file. It maps the data within the CSV file to specific cells in the Google Sheet, along with creating a tab for each of the CSV files to separate them out.

This blog post is a development of my previous one on importing CSV data into (multiple) Google Sheet files.

Bulk combine CSV files into a single Google Sheet

The Code

There are a couple of aspects of the code I want to tease out that are new to this tool. For each CSV file we want to create a new Google Sheet tab to paste the data into. For that we use a template tab to duplicate each time:

var newSheet = attendanceSheetOpen.insertSheet(newSheetName, { template: sheetTemplate });

It would also be nice to organise all of the tabs once they have been created, into ascending order. We start by getting an array of all of the tabs and pushing their names into another array for later sorting:

    // get array of Sheets (Tabs)
    var sheets = ss.getSheets();

    // get length of array of Sheets
    var sheetsLength = sheets.length;

    // create empty array to push Sheet names into
    var sheetNameArray = [];


    // loop through each Sheet (Tab) *************************
    for (var i = 0; i < sheetsLength; i++) {

      // get Sheet name and push into array
      var sheetName = sheets[i].getName();
      sheetNameArray.push(sheetName);

    }
    // loop through each Sheet (Tab) *************************

Next we use the JavaScript method 'sort()' to arrange the new array alphabetically. Then we need to apply this to our Google Sheet and physically rearrange the tabs:

    // use JavaScript 'sort' method to sort array alphabetically
    sheetNameArray.sort();


    // loop through each Sheet and physically reorder ****************
    for (var j = 0; j < sheetsLength; j++) {

      // get Sheet (Tab) name from array
      var sheetNameFromArray = sheetNameArray[j];

      // get Sheet (Tab)
      var singleSheet = ss.getSheetByName(sheetNameFromArray);

      // set current Sheet (Tab) active ready for moving
      ss.setActiveSheet(singleSheet);

      // move Sheet (Tab) to new position
      ss.moveActiveSheet(j + 1);

    }
    // loop through each Sheet and physically reorder ****************

Download

Bulk combine CSV files into a single Google Sheet download folder (please make a copy of each of the files for your own version).