Convert Sheet data into a JavaScript Object for easier handling

The following Google Apps Script offers a way to organise a Google Sheet of data so that your Header row names are paired with your data values. So say you have the Headers 'Forename' and 'Surname' this script will create a JavaScript Object with a name:value pair that makes it significantly easier to extract the data you want.

This is as opposed to the standard approach where you typically work with 2D arrays that require strict column positioning so that you know the exact position of a value within it. Should another column then be inserted or moved in your Google Sheet, the likelihood is that a chunk of your array positions would need manually updating.

Screenshot of Sheet data with Header rows

The Code

Our main Function that does the conversion is called 'objectify' and it is where we are going to pass all of our spreadsheet data into. We create an empty array to push our JavaScript Objects into and then loop through each row of our Sheet data:

var newarray = [];

// loop through data rows

for (var y = 0; y < allData.length; y++) {

For each row we create an empty JavaScript Object and extract all of the data for that one specific row:

var obj = {};

// get individual row of data

var rowOfData = allData[y];

Next we need to loop through our Header row and pair its name with each piece of data from our specific row, pushing this into our JavaScript Object:

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

   // get Header name

   var headerName = headerRow[i];

      

   // create name:value pair between each Header and data value

   obj[headerName] = rowOfData[i];

};

Finally, we push this into the empty array we created at the beginning of the Function:

newarray.push(obj);

Back in our parent Function the Sheet data is now available in a more friendly format that is easier to extract and handle. We have two ways in which to call specific data items (technically called 'object properties'), the latter being more useful for Heading titles that contain spaces:

for (var row = 0; row < niceArray.length; row++) {

  // get individual row of data

  var rowOfData = niceArray[row];

  // get Surname (one way)

  var surname = rowOfData.Surname;

  // get College Name (second way)

  var collegeName = rowOfData["College Name"];

};

Note

Unlike the standard approach where you iterate through a 2D array using positions, it is worth being aware that this method requires consistent Header titles. If you were to change a title after writing your code for instance, you would need to remember to change the bit in your code that calls that specific object property.

Download

Convert Sheet data into a JavaScript Object for easier handling download (please use 'File' > 'Make a copy' for your own version of each item).