Tuesday 17 January 2023

Organise files into a JavaScript Object

The following Google Apps Script 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 (specifically their IDs) 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:


Files to loop through and organise
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 tutor name 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 first file ID to it. If it does already exist then add the current file ID to the corresponding tutor:

// 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 first file Id in there.
        obj[tutor] = [theFileId];

      else {

        // the Object knows about this tutor

        // so add one (the currently file Id) onto the existing array.

// 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 fileIds) as the Object is then iterated over as an array - using the 'Object.entries()' method:

  var arrayToIterate = Object.entries(obj);

  for (var [tutor, fileIds] 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 (fileId of fileIds) {

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


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:

var arrayToIterate = Object.entries(obj);

for (tutor of arrayToIterate) {

    // log array for each tutor and their corresponding file Ids

    // log array of all file Ids for given tutor
    var fileIds = tutor[1];

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 < fileIds.length; i++) {

      // extract specific fileId
      var fileId = fileIds[i];
      Logger.log('single fileId is: ' + fileId);


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

No comments:

Post a Comment