如何在Google Apps脚本中使用For循环将数据存储在数组中-按值传递数组 [英] How to store data in Array using For loop in Google apps script - pass array by value

问题描述

在底部回答

我在使用Google脚本时遇到了一些奇怪的行为.我有一个二维数组，我使用for循环将数据插入其中.我注意到如果使用appendRow(someArray [i])在for循环中，一切正常.

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.

但是，如果尝试访问数据或在for循环之外使用appendRow，则它总是为我提供在for循环中运行的最后一行的数据.所以:

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

得到与

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

使用for循环的OUTSIDE时.

when used OUTSIDE of the for loop.

谁能告诉我是什么原因引起的?当im在2d数组上使用setValue时，也可以在循环外使用它，或者所有行都相同.

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.

我在这个简单的小事情上花了2个小时，终于了解了导致问题的原因，但是我仍然不知道如何解决该问题.我附上了一个简单的代码来说明问题所在，请关注第二个FOR循环.

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.

添加了我的代码，该代码使用"setValues"，但仍然遇到相同的问题.我的数组仅填充有"for循环"创建的LAST行

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

}

第二次修改:

所以问题是"getValues()"返回一个我需要使用的数组.但是Array是通过引用传递的，而不是通过值传递的，因此，我对代码进行的任何更改都会更改从"getValues"获得的原始数组.

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".

解决方案:遍历从"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.

我还创建了一个2d数组，这也需要运行"for循环"

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

我附上了以下工作代码:1.复制第13行，其中包括我原始工作表中的51列.2.创建一个空的2d数组(9999x51).3.进入第13行，并根据当前迭代操作其列(即行"1"将包含原始数据+"1"

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'

代码:

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


}

推荐答案

我不确定是什么原因导致了您的问题.但是，与其附加每行(它可能变得很慢)，不如附加一个范围并设置其值，它会更快.例如:

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

两个1是您要使用的起始位置.有关更多信息，请查看 getRange .

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

这篇关于如何在Google Apps脚本中使用For循环将数据存储在数组中-按值传递数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！