1

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);


}
4
  • Possibly because you're using the new Array() constructor, which is generally recommended against in Javascript. What happens if you use var arr = []; (...within your for loop:) arr.push([currentId+i, productSku+i])? Commented Jun 5, 2019 at 5:34
  • not working.. it seems like it pass the "newRow" as reference and by value Commented Jun 5, 2019 at 8:41
  • If you're using another variable, show your revised code Commented Jun 5, 2019 at 8:53
  • The problem was the array I got from my sheet, was passed around by reference and not by value. So I had to copy the data from it to my new array cell by cell Commented Jun 5, 2019 at 10:23

1 Answer 1

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.

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

1 Comment

Hi I actually tried it, but the result was a 2d array were all the lines are the last line of the run, (arr[98]). Please see the new code I added in my question were I use setValues. thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.