Organise files into a JavaScript Object 2.0

The following Google Apps Script is an enhanced version of this blog post for organising files into a JavaScript Object. The difference this time however is that we are extracting 3 (not 1) pieces of information from the CSV file name and coding them into an iteratble JavaScript Object. This is more likely the process you will want to use going forwards given the extra flexibility.

The code is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (their IDs, the module code and group number) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.

What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.

In this example we have a file name pattern of ModuleCode - Tutor Name - Group Number for example ABC - Jane Doe - Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).

 The collated data will look like this:

{Micky Mouse=[{fileId=FILE ID HERE, group=Grp 05.csv, moduleCode=CDE}, {moduleCode=CDE, fileId=FILE ID HERE, group=Grp 01.csv}], Jane Doe=[{group=Grp 04.csv, fileId=FILE ID HERE, moduleCode=ABC}, {moduleCode=ABC, fileId=FILE ID HERE, group=Grp 03.csv}, {fileId=FILE ID HERE, moduleCode=ABC, group=Grp 02.csv}]}

Files to loop through and organise

The Code - Organising the Data

We are using the typical File Iterator to go through all of the files within the Google Drive folder. To target the CSV files specifically we also make use of 'endWith()' to look at the end of the file name. As an alternative we could have use something like 'MimeType' instead:

if (theFileName.endsWith(".csv")) {

Once we have split the module code, tutor name and group number from the file name we need to check if it already exists within the JavaScript Object before trying to add it. If it does not (null) then create the key for the tutor and attach the file ID, module code and group number to it. If it does already exist then add these items to the existing tutor key:

// see if the tutor already exists in the JavaScript Object **********
      if (obj[tutor] == null) {

        // the Object has not seen this tutor yet

        // create an array in that tutor with file info.
        obj[tutor] = [{ fileId: theFileId, moduleCode: moduleCode, group: group }];

      }
      else {

        // the Object knows about this tutor

        // so add the currently file info onto the existing array.
        obj[tutor].push({ fileId: theFileId, moduleCode: moduleCode, group: group });

      }
// see if the tutor already exists in the JavaScript Object **********

The Code - Accessing the Data 1

One way to access the collated data within the JavaScript Object is via a 'for ... of' statement that executes a loop that operates on a sequence of values sourced from an iterable object (eg an array).

So we create 2 variables (tutor and fileObjs) as the Object is then iterated over as an array - using the 'Object.entries()' method:

  var arrayToIterate = Object.entries(obj);

  for (var [tutor, fileObjs] of arrayToIterate) {

At this point in the code we could create a Google Sheet file - which was my original aim - for the given tutor, as we are now looping through the first tutor in our list and subsequently all of the file IDs (containing student data) that belongs to them. As we get each CSV file of data we can read it and transfer the contents into the Google Sheet for the relevant tutor, which we would do in the next loop:

for (fileObj of fileObjs) {

      // extract File ID
      var fileId = fileObj.fileId;

      // extract Module Code
      var modCode = fileObj.moduleCode;

      // extract Group
      var group = fileObj.group;

      Logger.log(tutor + "\t" + fileId + " " + modCode + " " + group);

}

The Code - Accessing the Data 2

Another way to access the collated data is via a 'for' loop for those more familiar with this method when working with arrays. We still make use of a 'for ... of' statement initially so that we can work with each tutor and their corresponding file IDs in turn:

for (tutor of obj) {

    // return the JavaScript Object as an Array to loop through each tutor
    var theArrayOfObjects = obj[tutor];

Again this is where we would create our Google Sheet file per tutor, before next looping through each CSV file to bring the data across into it:

for (var i = 0; i < theArrayOfObjects.length; i++) {

    // get the Object for specific tutor
      var fileObj = theArrayOfObjects[i];

      // log the tutor
      Logger.log('Tutor is: ' + tutor);

      // extract file ID
      var fileID = fileObj.fileId;
      Logger.log('single fileID is: ' + fileID);

      // extract module code
      var modCode = fileObj.moduleCode;
      Logger.log('single modCode is: ' + modCode);

      // extract group
      var group = fileObj.group;
      Logger.log('single group is: ' + group);

Download

Organise files into a JavaScript Object 2.0 download folder (please make a copy of each of the files for your own version).