Import CSV data into Google Sheet files

The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.

CSV File template to copy data into

The Code

There are a couple of aspects of the code I want to tease out as they are relatively new. In this instance we have data in CSV files which cannot be accessed in the usual manner like a Google Sheet. We could opt to convert them into Google Sheets (which was how I first started) but then we are spending time converting them and ultimately needing to delete afterwards. Let us therefore make use of 'Utilities.parseCsv':

var fileData = Utilities.parseCsv(file.getBlob().getDataAsString());

Now that we have access to all of the data within one of the CSV files, we need to extract specific values. In this example we have a comma-separated CSV so that is how we will split the data up, before then pushing it into an empty array (so that we can later append all of the data in one go = faster):

var activityRef = fileData[0].toString();
activityRef = activityRef.split(",")[1];
groupData.push([activityRef]);

Having made a copy of the template file and opened it we can look to map the size of our array of data and append it to the Google Sheet in one go:

var arrayLength = groupData.length;
var arrayWidth = groupData[0].length;
sheet1.getRange(1, 2, arrayLength, arrayWidth).setValues(groupData);

Download

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