javascript 向下填充多行范围（多列） - google-apps-script
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16071297/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill a range (multiple columns) down multiple rows - google-apps-script
提问by 5th4x4
I had success filling a single column (A) with the value found in range A1...
我成功地使用范围 A1 中的值填充了单列 (A) ...
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var lastRow = ss.getDataRange().getNumRows();
var rngVal = ss.getRange("A1").getValue()
ss.getRange("A2:A"+lastRow).setValue(rngVal)
So then I thought I was on easy-street, and I tried to modify/apply that to a larger range by filling a multi-column range with the values found in range C1:H1...
然后我以为我在easy-street上，我试图通过用范围C1：H1中找到的值填充多列范围来修改/应用它到更大的范围...
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var lastRow = ss.getDataRange().getNumRows();
var rngVal = ss.getRange("C1:H1").getValues()
ss.getRange("C2:H"+lastRow).setValues(rngVal)
Apparently there is a bit more to this than simply slapping an "S" onto the end of the word "Value".
显然，这不仅仅是在“值”这个词的末尾加上一个“S”。
The error reads as follows:
错误如下：
Incorrect range height, was 1 but should be 10
不正确的范围高度，是 1 但应该是 10
(FYI: var lastRow = 11)
（仅供参考：var lastRow = 11）
Btw, I get no error if I use Value instead of Values, although I end up with cells full of the value found only in range C1.
顺便说一句，如果我使用 Value 而不是 Value s，我不会出错，尽管我最终得到的单元格充满了仅在范围 C1 中找到的值。
So I'm close.... or way off. One of those.
所以我很接近......或者很远。其中之一。
Help???
帮助？？？
回答by Serge insas
The error message is quite explicit... the size of the array must fit into the range in both getValues and setValues. Try it like this :
错误消息非常明确……数组的大小必须适合 getValues 和 setValues 的范围。像这样尝试：
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var lastRow = ss.getLastRow()
var rngVal = ss.getRange("C1:H"+lastRow).getValues();// get an array of 10 "rows" and 6 "columns"
ss.getRange("C2:H"+(lastRow+1)).setValues(rngVal);//write back this array to a range that has the same size. (starting from Row2 it must ends on lastRow+1 to keep the same number of "rows"
}
This function will shift the range C1:H last Row to C2:H lastRow+1, not sure it is very useful but that's not the point here ;-)
此函数会将范围 C1:H lastRow 移动到 C2:H lastRow+1，不确定它是否非常有用，但这不是这里的重点；-)
EDIT : sorry, I didn't understand exactly your requirement... here is a code that reproduce data from C1:H1 in all rows below
编辑：抱歉，我没有完全理解您的要求......这是一个代码，可以在下面的所有行中从 C1:H1 中复制数据
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var lastRow = ss.getLastRow()
var rngVal = ss.getRange("C1:H1").getValues();// get an array of the first row
var rowData = rngVal[0]
var newData = []
for(n=1;n<lastRow;++n){newData.push(rowData)}
ss.getRange("C2:H"+lastRow).setValues(newData);//write back this array to a range that has the same size. (starting from Row2 it must ends on lastRow+1 to keep the same number of "rows"
}
EDIT2 following your comment below :
EDIT2 在您下面的评论之后：
A word of explanation :
一句话解释：
When using range.getValues()we get a 2 dimensions array, meaning an array of arrays that can be represented as follow : [[data1,data2,data3],[data4,data5,data6]]data1, 2 & 3 are the value of the first array (index 0) and data 4,5 & 6 are the values of the second array (index 1). So if you want to get the values in the first array you have to write it like this : value = arrayName[0]and this will return a one dimension array [data1,data2,data3], that's what I used to get rowData.
使用时range.getValues()我们会得到一个二维数组，这意味着一个数组数组可以表示如下：[[data1,data2,data3],[data4,data5,data6]]data1, 2 & 3 是第一个数组的值（索引 0），数据 4,5 & 6 是第二个数组的值数组（索引 1）。因此，如果您想获取第一个数组中的值，您必须像这样编写它：value = arrayName[0]这将返回一个一维数组[data1,data2,data3]，这就是我用来获取 rowData 的内容。
Now we need to get a 2 dimension array again to be able to write back the new data to a range in the spreadsheet. Therefor we create a new array (var newData=[]or var newData = new Array()does exactly the same), and in the for loop we add the rowData array to this new array... the result will be an array of arrays, that is actually what we were looking for and we can write this directly to the sheet in one single setValuesstatement.
现在我们需要再次获得一个二维数组，以便能够将新数据写回电子表格中的某个范围。因此，我们创建了一个新数组（var newData=[]或var newData = new Array()完全相同），并在 for 循环中将 rowData 数组添加到这个新数组中……结果将是一个数组数组，这实际上是我们正在寻找的，我们可以在一个setValues语句中直接将其写入工作表。
回答by 5th4x4
Ok, this seems to do it...
好吧，这似乎做到了......
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var lastRow = ss.getLastRow()
var rngVal = ss.getRange("C1:H1").getValues()
for (var x=1; x<=lastRow; x++) {
ss.getRange("C"+x+":H"+x).setValues(rngVal);
