Horizontally Combine Many CSV Sheets in Google Sheets

An odd request came in from a faculty member the other day. As part of their research they had 60 or so CSVs that they wanted combined horizontally. I have many ways to do it vertically but this was a new one. Each file was around 350KB so they weren’t huge and 60 isn’t a ton but it’d be a hassle to do by hand and ripe for accidents.

I Googled a bit. Saw some stuff using PHP and array_merge, saw some people using Pandas in Python.¹ I talked to Jeff and he mentioned uploading them to MySQL and then doing a join off the common ids. All good and reasonable paths but they just didn’t seem like things I wanted to do.

I then saw that Amit² had a script for importing CSVs into Google Sheets. His import wasn’t meant to do quite what we were doing but it gave me enough momentum to create the script below.

The first function³, just loops through all the files in a folder you indicate through the folder ID. It calls the second function which imports the CSVs.

The key was changing the first line to the second.

sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);//I paste in the data starting at the first line of the first column. If I was run in a loop, I'd overwrite each time.
sheet.getRange(1, lastColumn, csvData.length, csvData[0].length).setValues(csvData);//I paste in the data on the first row of the last column

It takes a bit to run. The first time it got through maybe 45 sheets before Google got upset about how many cells I’d created. So I just split the CSVs in half and ran it against two folders and did the final combine manually. Only one chance to make a mistake and wasn’t worth finding a new path at that point.

I did do a rough verification to see if it worked properly by doing a countif statement based on one of the unique column headers. It was something like =countif(Sheet1!A1:AK1,”unitId”) and if that number matched the number of spreadsheets I’d imported I could be pretty sure it all worked.

//put all your csvs in a folder (and only your csvs)
//get the folder ID and put it below
//when you've done that save this and run 'get_all_files' and it should work


var folder_id = 'whatever your folder id is goes here between these quotes'

//this function gets all the files in the folder you designate
function get_all_files(){
 var folder = DriveApp.getFolderById(folder_id);
 var files = folder.getFiles();
 
  while(files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();
    importCSVFromGoogleDrive(fileName);
  }

}

//this function does the horizontal appending of data
//modified from https://www.labnol.org/code/20279-import-csv-into-google-spreadsheet
function importCSVFromGoogleDrive(fileName) {
  var file = DriveApp.getFilesByName(fileName).next();
  Logger.log(file);
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getLastColumn() === 0) {
    var lastColumn = 1;
  } else {
    var lastColumn = sheet.getLastColumn();//this is the key to horizontal, you could change to lastRow and do a vertical append
  }
  sheet.getRange(1, lastColumn, csvData.length, csvData[0].length).setValues(csvData);
}