谷歌应用程序脚本 - 工作表上的批量setValues() [英] google apps script - batch setValues() on Sheet
问题描述
为了优化我的代码,我试图执行批量更新。我不想在循环中调用setValues(),而是将数据存储在数组中,然后在循环后执行一次setValues()。然而,我不能让它工作:
var tempValuesArr = [];
var ctr = 0;
代表.... {
//每第二张纸只需要一行,第二张
//有6行列
var secondSheetRange = secondSheet.getRange(2,1,1,6);
tempValuesArr.push(secondSheetRange.getValues());
ctr ++;
}
//最后,将这些tempValues复制到另一个工作表,从第二行开始
var anotherSheetRange = anotherSheet.getRange(2,1,ctr,6);
anotherSheetRange .setValues(tempValuesArr);
这是我收到的错误:
< blockquote>
不正确的范围宽度为1,但应该是6
谢谢!
我认为问题在于你正在推送一个2D数组( [[CellA2,CellB2],[CellA3,CellB3] ]
)转换成一个1d数组( []
),留下一个3d数组( [[[CellA2,CellB2 ],[CellA3,CellB3]]]
)。
要使用setvalues,您需要一个2d( [[CellA2 ,CellB2],[CellA3,CellB3]]
- 2 rows OR [[CellA2,CellB2]]
- 1 row) / p>
试试这个,它只会将第一个1d数组( [CellA2,CellB2,CellC2]
)从范围放入您在开始时创建的空数组中。更多信息此处。
tempValuesArr.push(secondSheetRange.getValues()[0])
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!
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])
这篇关于谷歌应用程序脚本 - 工作表上的批量setValues()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!