Tuesday 6 December 2022

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
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();

    // 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

    // 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

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

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


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


  1. Hello Philly.

    I recently saw your video "Bulk create Google Docs from Google Sheet data - tutorial" and found It really useful. It is possible to create a script that only creates a document for a single row? I am new to programming and don't know how to. Any help you can provide is appreciated.

    1. Hi Joshua

      Great. Yes that absolutely is possible. The current script works via a loop that iterates through the rows from top to bottom. You could strip all of that away however and just have it create a Document for 'activeCell' (https://www.pbainbridge.co.uk/2018/12/get-activecell.html) for instance. So it gets that row and the data from it.

      Kind regards

  2. Hi Phil thanks for the tutorial! I was wondering how you would approach it if the csv files had the columns in different order but had the same type of info. So the columns would need to be reordered before appending altogether e.g., one csv is date,cost,category but another might be category,cost,date. Thanks!

    1. Hi Jonathan

      You're welcome, thank you.

      Errrrrm ... I've got to be honest I wouldn't! Apps script needs to be prescribed the exact location of the data it is collecting - having this in various locations makes it almost impossible. I would strongly recommend a 'data cleanse' where you go through sorting it before trying to automate moving it.

      Now despite saying all of that ... one option you could try (assuming the Column headings are identical regardless of their location) is to search through the Column headings (via Apps Script) for your keyword e.g. 'cost' and then return the Column number for use in getting the rest of the data (e.g. via 'getRange()').

      I suppose (as I write this response) there could be an argument for using Apps Script to rearrange the Columns for you, which I have an example of here: https://www.pbainbridge.co.uk/2022/07/move-column-positions.html

      Lots to think about!