将2d阵列移至试算表 [英] Move 2d Array to Spreadsheet
问题描述
如何在Google脚本中使用2D数组并将整个内容推到电子表格中的某个范围?
How do I take a 2d Array in google script and push the whole thing to a range in a spreadsheet?
这就是我所拥有的:
假设该数组已创建并填充,并且具有6000多个行和4列.
Assume that array is created and filled and has 6000+ rows and 4 columns.
以下代码给我一个不正确的范围错误.
The following code gives me an incorrect range error.
不正确的范围宽度为0,但应为4(第56行,代码"文件):
Incorrect range width was 0 but should be 4 (line 56, file "Code"):
formatted.getRange(1,1,targetArray.length,targetArray[0].length).setValues(targetArray);
以下代码起作用,但由于数据集太大而超时:
The following code functions but times out because the data set is too large:
for(var i=0; i<formattedSheetLength; i++)
formatted.getRange(i+1, 1, 1, 4).setValues([targetArray[i]]);
推荐答案
编辑说明:由于有OP注释,因此原始代码已更改.
Edit Notes: Due to an OP comment, the original code was changed.
尝试
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
// Create a jagged array for demonstration purposes
var targetArray = [
[1,2],
[1],
[1,2,3],
[1]
];
/**
* These two loops are safe because they don't make calls to
* the SpreadsheetApp service.
*/
var height = targetArray.length;
//Check the max width.
var width = 0;
for(var i = 0; i < height; i++){
width = Math.max(width,targetArray[i].length);
}
//Add the required empty values to convert the jagged array to a 2D array
var temp;
for(var i = 0; i < height; i++){
temp = targetArray[i].length;
for(var j = 0; j < width - temp; j++){
targetArray[i].push('');
}
}
/**
* Send values to the spreadsheet in a single call
*/
var range = sheet.getRange(1,1,height,width).setValues(targetArray);
}
说明
根据错误消息,targetArray[0].length
返回0
.我们可以假设targetArray
是锯齿状数组而不是2D数组.为了避免使用硬编码宽度,以上代码作为如何将锯齿状数组转换为2D数组的示例.
Explanation
According to the error message, targetArray[0].length
is returning 0
. We could assume that targetArray
is a jagged array instead of a 2D array. In order to avoid the use of a hardcoded width, the above code as an example of how to convert a jagged array into a 2D array.
该代码具有两个可以安全使用的循环,因为它们不调用Google Apps脚本服务.
The code has two loops that are safe to use because they doesn't call a Google Apps Script service.
这篇关于将2d阵列移至试算表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!