使用setValues函数的问题 [英] Issues using setValues function

问题描述

使用 setValues()函数将数组导出到Google表格时，我一直收到错误消息.我尝试了多种方法来创建2D数组，但仍然会遇到相同的错误.有时我的代码会运行(数组将导出到电子表格)，但我仍然会收到错误消息.

I keep getting an error message when using the setValues() function to export an array to Google sheets. I have tried many different methods for creating my 2D array, but I still keep getting the same errors. Sometimes my code will run (the array will export to the spreadsheet) but I will still get an error.

我最初在for循环中使用了 setValue()函数，但是由于运行时间太长，代码会超时.因此，我尝试将所有数据转储到2D数组中，然后一次将其全部馈送到电子表格中.

I originally used the setValue() function in a for loop but the code would time out because it ran too long. So I tried dumping all my data into a 2D Array and feeding that to the spreadsheet all at once.

试图将数组创建为空的2D数组

Tried creating the Array as an empty 2D array

  var outputArray = [[]]

，然后使用 .push 将数据填充到其中

and using .push to populate the data into it

尝试使用以下功能创建空数组:

Tried creating the empty Array using the function:

function create2DArray(rows) {
  var arr = [];

  for (var i=0;i<rows;i++) {
     arr[i] = [];
  }

  return arr;
}

并按行添加数据(在按行号迭代的for循环内)

and adding the data in by rows (inside of a for loop that iterates by rowNumber)

outputArray[rowNumber] = [data1, data2, data3,...]

使用与上面相同的函数创建空数组和中间数组，然后将其放入输出数组

Used the same function above for creating empty array and created intermediate array and then put that into output array

outputArrayIntermediate[0] = data1;
outputArrayIntermediate[1] = data2;
outputArrayIntermediate[2] = data3;
outputArrayIntermediate[3] = data4;...

outputArray[rowNumber] = outputArrayIntermediate;

这是不断发生错误的地方

Here is where the error keeps happening

var setRows = outputArray.length;
 var setColumns = outputArray[0].length

  revenueSheet.getRange(2,1,setRows,setColumns).setValues(outputArray);

当我包含setColumns变量时，出现错误:数据中的列数与范围中的列数不匹配.数据为0，但范围为11."这样仍会将数据填充到电子表格中.

When I include the setColumns variable I get the error: "The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 11." This will still populate the data to the spreadsheet.

当我不包括setColumns变量时，出现错误:数据中的列数与范围内的列数不匹配.数据有11个，但范围有1个."

When I do not include the setColumns variable I get the error: "The number of columns in the data does not match the number of columns in the range. The data has 11 but the range has 1."

推荐答案

是否有一个实例，其中一行具有比另一行更多的列?例如，如果数据中的行" 1为5列( outputArray.length = 5)，而行2为6，则数据需要具有6列的范围.

Is there ever an instance where one row has more columns than another row? For instance if 'row' 1 in your data as 5 columns (outputArray.length = 5) and row 2 has 6, then the data needs a range with 6 columns.

在这种情况下，为简单起见，这是一些解决方案:

If this is the case, here are some solutions in order of simplicity:

1..如果插入数据的右侧没有重要数据，则可以在 .getRange中使用 revenueSheet.getMaxColumns()().setValues().

1. If there is no important data to the right of where you are inserting you data you can use revenueSheet.getMaxColumns() in your .getRange().setValues().

2..遍历数据集以找到长度最长的行，并将其设置为列数.为此，请参见此答案以获取一些选择.

2. Iterate through the data set to find the row with the longest length and set that as the number of columns. To do this see this answer for a few options.

这篇关于使用setValues函数的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！