Use loops to iterate through arrays for use in an indexOf

Question

I'm using google scripts to work with a google sheets file. I'm attempting to find, match, and format data from two sheets and insert the results into a third sheet.

Currently, I have a function that collects several columns for each row of data in Sheet One and outputs them into an array of arrays.

I have another function that collects data from one column in Sheet Two and returns a one dimensional array of codes that I want to look for in Sheet One.

These two arrays work fine and are the source of arrayOfData and codeArray (see final code).

To illustrate what I want to accomplish simply, I created this test function. It shows the basic idea without the loops:

function test() {
  var arrayOfData = ["01/06/2016", "JOE BLOGGS , AB12CDE , VIA APP - PYMT", -665];
  var code = "AB12CDE";
  var test = arrayOfData[1].indexOf(code);
  var result = [];

  if (test > -1) {
    result.push(arrayOfData[0],code,arrayOfData[1],arrayOfData[2]);
  }

  return result;
}

This outputs the correct result:

[01/06/2016, AB12CDE, JOE BLOGGS , AB12CDE , VIA APP - PYMT, -665.0]

The problem I'm having is when I attempt to add the loops in order to cycle through larger data sets.

The first loop goes through the arrays in arrayOfData. It contains a second loop that iterates through the 'codes' in codeArray to try and find a match.

The 'code' would be in the second item of the array in arrayOfData, hence the arrayOfData[i][1].

Please see code below (note: there isn't any code to insert the data into Sheet 3 below as this will be handled by another function):

function main() {

  // Array of Arrays
  var arrayOfData = [["01/06/2016", "JOE BLOGGS , AB12CDE , VIA APP - PYMT", -225],
                     ["01/06/2016", "JAY BLOGGS , ZX34CDF , VIA APP - PYMT", -665],
                     ["01/06/2016", "JOHN BLOGGS , AG57HNE , VIA APP - PYMT", -500]]

  // 1D Array
  var codeArray = ["AG57HNE", "ZX34CDF", "AB12CDE"] 

  var dataLen = arrayOfData.length,
      codeLen = codeArray.length,
      i,
      ii,
      results = [];

  // First loop iterates through each array in the arrayOfData
  for(i = 0; i < dataLen; i++){ 

    // Second loop iterates through each code for each row in ArrayOfData
    for(ii = 0; ii < codeLen.length; ii++){

      // test checks  if the second item of the current inner array of arrayOfData contains the currently iterated code in codeArray
      var test = arrayOfData[i][1].indexOf(codeArray[ii]); 

      // If statement checks if test is true 
      if (test > -1) { 

        // If true append the result to the results array. 
        results.push(arrayOfData[i][0],codeArray[ii],arrayOfData[i][1],arrayOfData[i][2]); 

        // If true, break Second loop to allow the First to iterate to the next inner array.
        break; 

      }
    }
  }

  Logger.log(results);
  return results;

}

This outputs an empty array.

I think the problem has something to do with the test logic in the if but I can't seem to figure out what's going on.

Any advice would be greatly appreciated.

why do you use the indexOf method in just a field of the array?, I mean, you are writing arrayOfData[1].indexOf(code), instead of arrayOfData.indexOf(code). — myhappycoding
– myhappycoding, Commented Jul 26, 2016 at 8:57

Nina Scholz · Accepted Answer · 2016-07-26 09:01:44Z

2

You could use some array methods for searching and generating your result array.

function main() {
    var arrayOfData = [["01/06/2016", "JOE BLOGGS , AB12CDE , VIA APP - PYMT", -225], ["01/06/2016", "JAY BLOGGS , ZX34CDF , VIA APP - PYMT", -665], ["01/06/2016", "JOHN BLOGGS , AG57HNE , VIA APP - PYMT", -500]],
        codeArray = ["AG57HNE", "ZX34CDF", "AB12CDE"],
        results = [];

    arrayOfData.forEach(function (a) {
        var code;
        codeArray.some(function (b) {
            if (a[1].indexOf(b) !== -1) {
                code = b;
                return true;
            }
        }) && results.push(a[0], code, a[1], a[2]);
    });
    return results;
}

console.log(main());

answered Jul 26, 2016 at 9:01

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SanSalvage Over a year ago

Worked great! Thank you Nina!

Rob Wilson · Accepted Answer · 2016-07-26 09:05:48Z

1

The trouble with this snippet is that the code is never getting into the second for loop, you are calling .length on the codeLen - but this is already the length value. i.e. just a simple typo. if you change:

for(ii = 0; ii < codeLen.length; ii++){

to:

for(ii = 0; ii < codeLen; ii++){

you shoud see your results being populated

answered Jul 26, 2016 at 9:05

Rob Wilson

1,1331 gold badge8 silver badges13 bronze badges

Collectives™ on Stack Overflow

Use loops to iterate through arrays for use in an indexOf

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related