2

I'm trying to loop over multiple arrays using a single "for in" or "for each" loop.

I have three arrays namely, name, id , and available. The size of these arrays are equal.

What I need to do is that I need to iterate through each value of the above arrays and based on the index of the row and column (values of i and j, respectively), copy the value of the element to a cell in spreadsheet.

The below is the code I am using:

for (i = 1; i <= copy_range.getNumRows(); i++) {
  for (j = 1; j <= copy_range.getNumColumns(); j++) {
    if (j == 1) {
      var name_cell = copy_range.getCell(i, j);
      // I want to do this however I'm not able to do this since I already have i and j for
      // row and column iteration and that another nested loop makes things complicated
      name_cell.setValue(name[k]);
    }
    else if (j == 2) {
      var id_cell = copy_range.getCell(i, j);
      Logger.log(id_cell.getA1Notation());
      id_cell.setValue(id[k]); //Same idea as in previous if
    }
    else {
      var availability_cell = copy_range.getCell(i, j);
      Logger.log(availability_cell.getA1Notation());
      availability_cell.setValue(available[k]); //Same as if and else if loops previously.
    }
  }

The reason I'm not able to use indices is that I already use i and j as iterative variables to refer to row and column, and using another nested loop does not give me the intended output - it leads to unwanted iterations and execution time.

Please let me know if there is any way where I can use a "for in" or a similar loop to iterate over each item of all the three arrays.

Subramania Prasad
  • 69
  • 3
  • 12
  • could you share your spreadsheet.I'll make a pleasure to help.? – JSmith Oct 06 '18 at 15:37
  • 2
    You don't say what k is or how it is related to i and j. Is k simply the same as i or i plus some number? You should also look at best practices https://developers.google.com/apps-script/guides/support/best-practices, numerous calls to setValue can degrade performance. You seem to be setValue() for every i and j, why not build a 2D array and then use setValues(array). – TheWizEd Oct 06 '18 at 15:42
  • 1
    Do not use `for in` for arrays. Do not use `for each(var...` at all. – tehhowch Oct 06 '18 at 15:49
  • Also I'd like to point out that cell in cell.setValue() is not defined in your loop. Did you mean name_cell, id_cell and availability_cell? – TheWizEd Oct 06 '18 at 16:25
  • Your code is very convoluted at the moment, only because of numerous calls to .setValue. This will slow your script down quickly, as does .getValue or other methods that must access the spreadsheet. Instead of your solution, the best way to move forward is to create some sort of two dimensional array, or even three dimensional if necessary (I don't understand what you're trying to do completely). Since we need to alter a large portion of your code you should create a copy of your spreadsheet, remove any sensitive information, and share an editable copy here that we can look at! – Swordstoo Oct 06 '18 at 18:21
  • @TheWizEd Yes Indeed will edit the code to reflect that. Apart from that what I mean by k is that I'd like to iterate through the three arrays that I have which are name, id, available, using a variable and that variable is what I wanna call k, but I don't have a way to do that. I'll try to explain the complete code and what I'm doing soon with examples of my input and output Thank you very much for your help guys. – Subramania Prasad Oct 06 '18 at 19:00
  • @JSmith Swordstoo Thanks will try to make a spreadsheet or share a more elaborate code with what ever data I'm dealing with so that it will be a bit more clear. – Subramania Prasad Oct 06 '18 at 19:01
  • @tehhowch Any specific reason for this? – Subramania Prasad Oct 06 '18 at 19:02
  • @SubramaniaPrasad, an array is an object and a loop for( x in a ), x is the index not the value, you would have to do for( x in a ) { a[x] ... } to access the value. For other objects x is the name of the property or method and there is no particular order so there is no guarantee that 1st in 1st out (fifo). – TheWizEd Oct 06 '18 at 19:27
  • @subra `for each(var..)` is deprecated. You can read more in your preferred JavaScript developer reference. `for .. in...` is a property enumeration. You can read all about different array iteration/consumer methods in some very popular SO questions', or your preferred JavaScript developer reference. To start: https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea – tehhowch Oct 06 '18 at 19:37
  • Thanks Everybody for your time and answers, I learnt from all your answers that doing this using a setValue call for each cell is highly inefficient, taking cue of this I have actually made up the required 2D array of values and then assigned it directly to the selected Range of spreadsheet using setValues. Being a n00b your answers helped me much to learn further on best practices, will try to build on top of it. @tehhowch is spot on with the answer – Subramania Prasad Oct 06 '18 at 21:36

1 Answers1

3

To me it seems like you have three "column" arrays of N entries and want to write them to a range that is N rows and 3 columns (named copy_range). The best way to do this is to join these 3 arrays into the requisite 2D array that can be written directly in a single call:

const output = name.map(function (nameVal, row) {
  return [nameVal, id[row], availability[row]];
});
copy_range.setValues(output);

The above uses the Array#map method to iterate the name array, and assigns the index associated with each element nameVal to be row. The corresponding element in the id and availability arrays is then accessed using row.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
  • 1
    I thin you got the answer spot on, sorry for not being more descriptive on original question. Basically I have three 1D arrays namely name, id, available that have N elements each. Hence I declared a range N*3 to accommodate the 2D array that contains all the values of the above 3 1D arrays. Once I did I used the setValues to do this in a single stroke. The way I did it was to use two for loops, one for row and the other for the column , I will explore your answer since I'm not yet aware of map. Thanks for your answer. – Subramania Prasad Oct 06 '18 at 21:45