google apps script - batch setValues() on Sheet

问题:

In order to optimize my code, I'm trying to do the batch updates. Instead of calling setValues() on the loop, I want to store the data in an array, then do an setValues() once after the loop. However, I can't make it work:

var tempValuesArr= [];
var ctr = 0;

for ....{

  //there is only one row needed for every second sheet, get the data at the 2nd 
  //row with 6 columns
  var secondSheetRange = secondSheet.getRange(2, 1, 1, 6);  
  tempValuesArr.push(secondSheetRange.getValues());
  ctr++;
}

//finally, copy these tempValues at another sheet, starting at second row
var anotherSheetRange = anotherSheet.getRange(2, 1, ctr, 6);
anotherSheetRange .setValues(tempValuesArr);  

Here's the error I'm getting:

Incorrect range width, was 1 but should be 6

Thanks!

回答1:

I think the issue is that you are pushing a 2d array ( [[CellA2, CellB2], [CellA3, CellB3]] ) into a 1d array ( [] ), leaving you with a 3d array ( [[[CellA2, CellB2], [CellA3, CellB3]]] ).

To use setvalues you need a 2d ( [[CellA2, CellB2], [CellA3, CellB3]]- 2 rows OR [[CellA2, CellB2]]- 1 row ) array of values.

Try this, it will only push the first 1d array ( [CellA2, CellB2, CellC2] ) from the range into the empty array you created at the start. More information Here.

tempValuesArr.push(secondSheetRange.getValues()[0])

回答2:

tempValuesArr.push(secondSheetRange.getValues()[0]);

secondSheetRange.getValues() returns a two-dimensional array where are you require only a single row array