How to store data in Array using For loop in Google apps script - pass array by value

Question

Edit: answer at the bottom

Im experiencing some weird behavior I came across when using google script. I have a 2d array and I insert data into it using for loop. I noticed that if use appendRow(someArray[i]) INSIDE the for loop, everything works as expected.

But If try to access data or use appendRow outside of the for loop, it always gives me the data of the last row the ran in the for loop. so:

appendRow(someArray[1])

gives same result as

appendRow(someArray[2]),appendRow(someArray[3])

when used OUTSIDE of the for loop.

Can anyone tell me whats causes it? It also happens when im using setValue on a 2d array, I can use it outside of the loop, or all of the rows are identical.

I have spent 2 hours on this simple little thing, and finally understand what causing the problem but I still cant figure out how to fix it. Im attaching a simple code that explains what the problem, please focus on the second FOR loop.

function myFunctionAppendRowInside() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newRow = data[5];
  var arr = new Array(100,100);
  var currentId = 20000;
  var productSku = data[5][2];

  for (var i = 0; i < 99; i++){
    arr[i] = newRow
  }
  for (var i = 0; i < 99; i++){
     target.getRange(targetRow,1).setValue(evento[i]);
      arr[i][0] = currentId + i;
      arr[i][2] =  productSku + i;
      sheet.appendRow(arr[i]);
  }

//All of them gives the same row, which is the one created in the last run of the FOR loop arr[98]
     sheet.appendRow(arr[1]); 
     sheet.appendRow(arr[2]);
     sheet.appendRow(arr[3]);

 }

Please explain to me whats causes it and how to overcome it.

Edit : added my code which uses "setValues" , but still experiencing the same problem. My array is populated only with the LAST row created by the "for loop"

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = activeSpreadsheet.insertSheet();
  newSheet.setName("newSheet");
  var data = sheet.getDataRange().getValues();

  //Taking the 5th row, using it as a template for rest of rows
  var newRow = data[5];

  //2d Array
  var arr = new Array(100,100);

  //data for the only 2 columns who are going to change between rows
  var currentId = 20000;
  var productSku = data[5][2];


  for (var i = 0; i < 99; i++){
      newRow[0] = currentId + i;
      newRow[2] =  productSku + i;
      arr[i] = newRow;
  }

  newSheet.getRange(2, 1, arr.length, arr[0].length).setValues(arr);

}

Second Edit:

So the issue was that "getValues()" returns an array, which I needed to work with. But Array are passed by reference and not by value, so any changes I made along the code, where changing the original array which I got from "getValues".

The solution: iterate over the array received from "getValues", and copy the values one by one(cell by cell) to the a new array and only then manipulate it.

I also created a 2d array, which also requires running a "for loop"

Im attaching the working code which does: 1.copy row 13, which includes 51 columns from my original sheet. 2.create an empty 2d array (9999x51). 3. take row 13 and manipulate its columns based of current iteration (ie row '1' will include original data + '1'

The code :

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var data = sheet.getDataRange().getValues();


  //create Array
  var arr = []; 

  //turn it to a 2 dimension array
  for (var i=0;i<10000;i++) {
     arr[i] = [];
  }

  //starting point for our new id which will run from 30000-39999
  var currentId = 30000;

  //run on our 2 dimension array and manipulate data cell by cell
  for (var i=0; i <= 9999; i++){
    for (var j=0; j<= data[13].length - 1; j++){
      if (j == 0){
      var obj = currentId + i;
        arr[i][j] = obj;
      }else{
        if (j == 2){
        arr[i][j] = data[13][j] + i;
        }else{
          arr[i][j] = data[13][j];
        }
      }  
   }
  }

  //copy to new sheet
  var newSheet = activeSpreadsheet.insertSheet();
  newSheet.setName("newSheet466");
  newSheet.getRange(1, 1,10000, 51).setValues(arr);


}

Answer 1

I'm not sure what causes your problem. But instead of appending each row (which can get very slow) it is faster to get a range and set its value. For example:

sheet.getRange(1, 1, arr.length, arr[0].length).setValues(arr);

Where the two 1's are the starting position that you wish to use. For more info check out the documentation on getRange.