Google Apps javascript iterating through multiple arrays

Question

I'm relatively new to customizing Google sheets and only learning javascript. I'm trying to create a custom function that will search for a value in an array and return a string (something like vlookup).

What I'm trying to achieve is to get the value checked against multiple arrays. I wrote the below but for some reason, it checks only first element of the array (it did work but stopped for some reason and I cannot figure out why as I don't think I changed anything).

The second part will be trickier still, how to make it work against multiple arrays... I was thinking to create an array: depots = [depot1,depot2...] and then change the code to "depots.length in for loop but even 1 array proves to be problematic.

var depot1 = ["device1", "device2", "device3"];
var depot1 = ["device1", "device2", "device3"];

function _depot(value) {
  if (value) {
    var depotCheckCase = value.toUpperCase();

    for (var i = 0; i < depot1.length; i++) {
      if (depotCheckCase == depot1[i]) {
        return "Depot 1";
      } else {
        return false;
      }
    }
  }
}

Warning to potential answerers: Google Apps Script is not JavaScript. It's very, very similar, along the lines of ES3 plus a bit. It does not have most modern JavaScript features. — T.J. Crowder
– T.J. Crowder, Commented Jul 21, 2018 at 10:49

T.J. Crowder · Accepted Answer · 2018-07-21 11:14:09Z

It only checks the first entry because you have return in both branches of your if/else, so no matter what, the first loop iteration will terminate the function.

Instead, move the return false; to the end, outside of the loop.

A couple of other issues:

You're declaring the same variable twice. Your second var depot1 = ... ends up being just an assignment (but since the array it's assigning has the same entries as the first one, you may not notice).
You're forcing the value to check to upper case, but not doing the same to the entry you're checking against.

Addressing all of those:

var depot1 = ["device1", "device2", "device3"];

function _depot(value) {
  if (value) {
    var depotCheckCase = value.toUpperCase();

    for (var i = 0; i < depot1.length; i++) {
      if (depotCheckCase == depot1[i].toUpperCase()) {
        return "Depot 1";
      }
    }
    return false;
  }
}

console.log(_depot("device2")); // "Depot 1"
console.log(_depot("device8")); // false

Any idea how can I combine it with checking against second/third array?

You have two options:

Additional loops (simplest).
Finding the length of the longest array, using that as the loop max, and checking against undefined before comparing. Since [n] on an array when n is greater than or equal to the length will give you undefined, you can check that before doing the toUpperCase.

Here's that second one:

var depot1 = ["device1", "device2", "device3"];
var depot2 = ["device4", "device5"];
var depot3 = ["device6", "device7", "device8", "device9"];

function _depot(value) {
  if (value) {
    var depotCheckCase = value.toUpperCase();
    var max = Math.max(depot1.length, depot2.length, depot3.length);
    var entry;
    for (var i = 0; i < max; i++) {
      entry = depot1[i];
      if (entry !== undefined && depotCheckCase === entry.toUpperCase()) {
        return "Depot 1";
      }
      entry = depot2[i];
      if (entry !== undefined && depotCheckCase === entry.toUpperCase()) {
        return "Depot 2";
      }
      entry = depot3[i];
      if (entry !== undefined && depotCheckCase === entry.toUpperCase()) {
        return "Depot 3";
      }
    }
    return false;
  }
}

console.log(_depot("device2"));  // "Depot 1"
console.log(_depot("device8"));  // "Depot 3"
console.log(_depot("device5"));  // "Depot 2"
console.log(_depot("device10")); // false

You could give yourself an array of arrays and do that in a loop rather than repeating the logic. I leave that as an exercise for the reader. :-)

Collectives™ on Stack Overflow

Google Apps javascript iterating through multiple arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related