0

Totally newbie to JS so apologise for the probably obvious solution to this issue!

I'm trying to write a bit of code for use in google sheets that basically removes elements of a 2D array if the 3rd value in each nested array is empty eg.

[[1,2,3],[4,5,,],[7,8,9],[,,,]

would become

[[1,2,3],[7,8,9]]

I've currently got:

if (bkLoc=='HALL'){
      var sumRng = sh.getRange('MIC_SUMM').getValues();
      for (var i =0; i<sumRng.length ; i++){
        if(sumRng[i][2] !== (undefined || '' || null)){
          micSumm.push(sumRng[i]);
        }
      }
    }

But the output seems to contains loads of empty arrays almost like its pushing every loop and I'm not sure why.

Any help would be gratefully received!

EDIT1: So with the help of you guys I got it to work. Using Nikhils answer I am now using this for the IF

if (bkLoc=='HALL'){
      var sumRng = sh.getRange('MIC_SUMM').getValues();
      for (j =0; j<sumRng.length ; j++){
        var x = sumRng[j][2];
        if(x != 0 && x != '??'  && x != undefined){
          micSumm.push(sumRng[j]);
        }
      }
    }

But to be honest I don't really understand it. My understanding was || is OR so in my original code

if(sumRng[i][2] !== (undefined || '' || null))

If the tested content DOESN'T CONTAIN undefined OR "" OR null , the if statement should be true. I thought && meant AND so I'm unclear as to why that ever passes

Apologies for being so dumb!

Cheers

Improve this question
7
  • 2
    Warning to answerers: Google Apps Script is not modern JavaScript. It's roughtly ES3 plus a bit, and doesn't have most of ES5 and certainly not any ES2015+. Commented Aug 14, 2018 at 9:59
  • Use Array.filters() function. sumRng[i][2] !== (undefined || '' || null)) is really wrong. Commented Aug 14, 2018 at 9:59
  • @ArupRakshit Is that available in sheets? Commented Aug 14, 2018 at 10:01
  • 1
    @ArupRakshit - There is no standard Array.filters function. If you meant Array#filter (singular), not in GAS, see my first comment above. Commented Aug 14, 2018 at 10:02
  • 1
    In the statement if (sumRng[i][2] !== (undefined || '' || null), the (undefined || '' || null) is evaluated first to ('' || null) and then to (null) and then null, and then it is compared to sumRng[i][2]. This is because the logical OR returns the RHS if the LHS is not evaluable to true I.e. it becomes if (sumRng[i][2] !== null). The innermost element result of Range#getValues() will have either a value, or "", never null, so that statement will always be true. Commented Aug 14, 2018 at 15:18

4 Answers 4

Reset to default
3

In your current if condition, undefined || '' || null evaluates to null. Hence, the condition eventually becomes sumRng[i][2] !== null.

However, as you need to check for undefined and '' too, you will need to update your condition

From

if(sumRng[i][2] !== (undefined || '' || null)){

to

if(sumRng[i][2] !== undefined && sumRng[i][2] !== '' && sumRng[i][2] !== null){
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Assuming you've defined micSumm somewhere and started out with a blank array in it (var micSumm = [];), then you're on the right track, the issue is here:

if(sumRng[i][2] !== (undefined || '' || null)){

That's not how you do a check against multiple values in JavaScript. The way that's evaluated is:

  1. sumRng[i][2] is evaluated; let's call the resulting value left
  2. undefined || '' is evaluated, the result is ''; let's call it temp¹
  3. temp || null ('' || null) is evaluated, the result is null; let's call that right
  4. The result of left !== right is evaluated

So you end up only checking for null, not undefined or ''.

Instead:

var value = sumRng[i][2];
if (value !== undefined && value !== '' && value !== null) {

Or you can take advantage of the fact != undefined also checks for null:

var value = sumRng[i][2];
if (value != undefined && value !== '') {

...but that can be a bit less clear.

¹ Why does undefined || '' result in ''? Because an expression a || b is evaluated like this:

  1. Evaluate a
  2. If the value from Step 1 is truthy, make that the result of the || operation
  3. Otherwise, evaluate b and make that the result of the || operation

undefined is falsy, not truthy. (A falsy value is any value that coerces to false when used as a boolean. The falsy values are undefined, null, "", 0, NaN, and of course, false. All other values are truthy.)

Improve this answer

6 Comments

!== is always better than != :)
@ArupRakshit - I don't agree with that as an absolute. But I'd use !== here.
I am not challenging, but asking here. Does coercion is a good feature? I think implicit things are some times not good.
@ArupRakshit - In general, I agree that explicit is better than implicit. I just don't generally go for absolute rules, because it seems like there's always an exception.
@ChrisBarrett - They do, but !== only compares two operands, the one on the left and the one on the right. By the time !== happens, undefined || '' || null has already been evaluated and the result is null. I've added the steps to the answer. (You don't want to use sumRng[i][2] != (undefined || '' || null), because that's just sumRng[i][2] != null. It'll be true for null and undefined (because of the coercion rules != applies), but won't be true for ''.)
|
2

You could also use filter(which is supported by apps-script):

var filteredArr =  sumRng.filter(function(e) {return e[2];})

Note that you're getting a 2D array with a specific rectangular dimension. So, there's no possibility of a value being undefined. Unless you specifically made a value null,null isn't returned. As noted in the comments below, the above also filters out 0 and boolean false. So, You can use 

sumRng.filter(function(e) {return e[2].toString();})

or 

sumRng.filter(function(e) {return e[2] || e[2] === false || e[2] === 0;})

References:

Array#filter
Primitives
null
Comparison Operators
Truthy

Improve this answer

3 Comments

If, as you say in a comment on the question, GAS supports filter, you should state that explicitly in your answer. But note that your answer doesn't do what the OP's code does; yours also filters out several other falsy values, including 0, NaN, false...
@TJ In terms of values that Google Sheets will send to an array, NaN won't happen. I'd use return e[2] || e[2] === false || e[2] === 0; to handle the Boolean and 0 cases. I'd also chain the filter directly on getValues().
Thanks so much for all your help everyone. But I'm still a bit confused. I tried the filter code and it seems to work the same as my original code in that the array I'm pushing to still gets filled with a load of empty entries which is just bizarre. I can't see how it ever gets into that part of the IF statement.
1

I still struggle with the why the original OR method I had doesn't work. In fact I went over to reddit to try and see if I could get anymore clarification but still it won't go in. But I appreciate all your help trying.

I did get an alternative solution to my conundrum though which seems a more condensed version of what I was using. That was simply this (courtesy of user insertAlias)

if(sumRng[i][2]){}

Apparently that will only pass for anything truthy so seems to fit the bill. But please point out any shortcomings

Improve this answer

1 Comment

if the value is false or 0 in your 3rd column, then your statement will treat it as though its value were "" (i.e. nonexistent)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.