Google Apps Script: Set Value Of Blank Cell

Perhaps the easiest way to assign a default value to a variable is to append the || conditional after referencing the variable.

For example, after looping through an array and mapping the values to properties within an object, I needed to test whether the property had been assigned.

As I use Google Spreadsheets to iterate through data imagine the following data set:

Data set containing rows of data, except for one row with a blank “Choice” value.

Upon grabbing this data in Google App Script using a quick one liner:

1
2

var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
// => [[ "Name", "Age", "Choice" ], [ "Ryan", 50, "A" ], [ "Sara", 27,  ], [ "Jude", 29, "C" ]]

We now have our sheet data wrapped into a 2-dimensional array.

If we now reduce this array into an object such that we can easily reference each element within the data variable we should easily be able to do like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

var obj = data.reduce( function( prev, el, i ) {
    var result = {};
    if ( i > 0 ) {
        result.name = el[0];
        result.age = el[1];
        result.choice = el[2];
        prev.push( result );
    }
    return prev;
}, [] );

Hopefully the above code is somewhat self-explanatory, but here we only have one condition and that is to remove our header row in our data and then mapped all elements within our array to a property – in essence corresponding to the header titles.

However, upon doing our data manipulation we’d now like to return it back to the sheet and therefore need to map it back to a 2-dimensional array. Here’s how that code would look:

1
2
3

var arr2d = obj.map( function( el ) {
    return [ el.name, el.age, el.choice ];
});

This may appear to look great, but if we were to inspect the arr2d variable we would find it showing this:

1

// => [["Ryan", 50, "A"], ["Sara", 27, undefined], ["Jude", 29, "C"]]

Most of that is fine except for the ugly undefined. Ugh! If we were to return this back to the sheet we would get the undefined word populating our sheet.

So what can we do?

There are a couple of things, but one such simple way is to apply the || conditional on that final map method, making it look like this:

1
2
3
4

var arr2d = obj.map( function( el ) {
  return [ el.name || "", el.age || "", el.choice || "" ];
});
// => [["Ryan", 50, "A"], ["Sara", 27, ""], ["Jude", 29, "C"]]

Now the result is looking much neater and we can return the results as is back to the sheet.

Anyway, be mindful of undefined when working with arrays and objects when sucking data out from Google Spreadsheets playing with it and then returning it back to the sheet. It could make for an unsightly sheet!