将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屋！