Iterate over a large 2D array with nested loop in JavaScript

Question

I am trying to iterate over a large 2D array which is over 3000 rows and 54 columns. Each index contains either a string or integer value. When I try to use a nested for loop I am unable to test it since it just takes to long to complete. How can I get around this issue?

I have this code from an earlier post that does something similar:

function getOrder() {
  const srcSheetName = "result";
  const dstSheetName = "Order Changes";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Retrieve source values.
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [[,,,,,,,, ...header], ...srcValues] = srcSheet.getRange("F1:BQ" + srcSheet.getLastRow()).getValues();

  // 2. Create an object using the source values.
  const srcObj = srcValues.reduce((o, [a,,,,,,,, ...v]) => {
    const temp = v.reduce((s, r, i) => {
      if (r.toString() != "") s += `${header[i]} (${r}) `;
      return s;
    }, "");
    return Object.assign(o, {[a]: temp || ""});
  }, {});

  
  // 3. Retrieve the header column of destination values.
  const dstSheet = ss.getSheetByName(dstSheetName);
  const dstRange = dstSheet.getRange(3, 1, dstSheet.getLastRow() - 1);
  const dstValues = dstRange.getValues();
  
  // 4. Create the output values using the header column and the object.
  const putValues = dstValues.map(([a]) => [srcObj[a] || ""]);

  console.log(srcObj)

  // 5. Put the values.
  dstRange.offset(0, 2).setValues(putValues);
}

The above code matches names on two different sheets and returns the values and headers of each column if a match is found and does so very quickly with the same number of entries. I assume it is so quick because of .reduce to remove unnecessary information in the 2d array.

How can I achieve a similar speed? I would like to search the 2d array for non-zero entries of a row in the last 12 columns. If any of the columns contain a value I would like to return index 0 of that row and continue until the array has been completely cycled through.

I have tried to adjust the above code but I don't understand it entirely and can't manipulate it how I'd like. Here is what I have written:

function getCustomer(){
  const srcSheetName = "result";
  const dstSheetName = "Allergy";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const allergyRange = srcSheet.getRange('F1:BQ' + srcSheet.getLastRow()).getValues();
  const dstSheet = ss.getSheetByName(dstSheetName);
  Logger.log(allergyRange);
  for (let i = 0; i < allergyRange.length; i++){
    for (let k = 0; k < allergyRange[i].length; k++){
         Logger.log("hi");
    }
  }
  
}

I would like to nest an if statement in the code I've written but am unaware of a way to refer to a range of indices in a row of a 2d Array and at this point I think this would take too much time to be practical. Any tips would be greatly appreciated.

mel · Accepted Answer · 2020-11-24 05:29:50Z

1

Assuming allergyRange is formatted like [row, ...] where each row is an array with each of its indices referring to a column:

allergyRange.map(row => row.slice(row.length-12)).forEach((row, i) => {
    if(!row.every(val => val == 0)) console.log(allergyRange[i][0])
});

This maps a new array of arrays that contain just the last 12 columns, checks if every value in that row is equal to 0, and if not logs the 0th index of that row.

answered Nov 24, 2020 at 5:29

mel

1151 silver badge6 bronze badges

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

2 Comments

Dan.db Over a year ago

Any change you could point me in the direction of some literature that can help me return the results to a column?

mel Over a year ago

Use an array - JS array reference

Collectives™ on Stack Overflow

Iterate over a large 2D array with nested loop in JavaScript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related