Google Spreadsheets脚本，copyTo vs copyValuesToRanges& copyFormatsToRanges [英] Google Spreadsheets script, copyTo vs copyValuesToRanges & copyFormatsToRanges
问题描述
模板单元格的内容可能包含格式，值和函数。
当我将一行复制到一系列多行时，函数copyValuesToRange（）和copyFormatsToRange（）看起来不错。
本质上，我的代码有点像这样;
模板。 copyFormatToRange（sheet，1，lastColumn，lastRow + 1，lastRow + N）;
Template.copyValuesToRange（sheet，1，lastColumn，lastRow + 1，lastRow + N）;
这将我的模板行复制到N行，从lastRow + 1开始。
然后我注意到它没有复制模板行中的函数，例如= SUM（B2：C2）
我可以通过getFunctions（）获取一个包含我的模板行和setFunctions（）函数的数组，将它们复制到目标行。
不便之处是我必须切换到A1表示法，这会将执行时间添加到脚本中。重要的是，setFunctions（）写入的目标范围必须与数组大小相同。这意味着我必须运行一个for循环来将函数复制到N行上。
如果我正在运行for循环，我可以使用
-
for循环copyTo（）
而不是
- copyFormatToRange
- copyValuesToRange
- for循环到setFunctions
我试图避免使用copyTo（），因为我认为N是一定大小的单个命令copy ***** ToRange会比for循环更高效。我坚持使用for循环还是有任何聪明的方法，我可以将函数形成一个单行复制到一个范围的行？
但是你可以使用 copyTo 复制到一个宽目的地，你不必循环。例如
function copyTo（）{
var s = SpreadsheetApp.getActiveSheet（） ;
s.getRange（'A2：D2'）。copyTo（s.getRange（'A3：D10'））;
}
刚测试过它，它正常工作。
但是如果你想让这些函数起作用，你应该使用 getFormulasR1C1 和 setFormulasR1C1 对。设置这些值后设置公式可能会出现的另一个问题是，您必须跳过纯数值单元格，否则您会用空白公式覆盖它们。使用 copyTo 似乎是最好的方法。
I need to copy a template row to a new range.
The template cell have contents where format, values and functions may be present.
As I was copying one row to a range of multiple rows, the functions copyValuesToRange() and copyFormatsToRange() looked good.
Essentially my code went a bit like this;
Template.copyFormatToRange(sheet, 1, lastColumn, lastRow + 1, lastRow + N);
Template.copyValuesToRange(sheet, 1, lastColumn, lastRow + 1, lastRow + N);
This copies my template row over N new rows starting at lastRow+1.
Then I noticed it was not copying the functions in the template row, e.g. =SUM(B2:C2)
I can getFunctions() to get an array that contains the functions in my template row and setFunctions() to copy them into a target ROW. An inconvenience is that I have to switch to A1 notation, which adds execution time to the script. Importantly the target range that setFunctions() writes to must be the same size as the array. This means I have to run a for loop to copy the functions over N rows.
If I am running a for loop anyway I can just use
for loop copyTo()
instead of
- copyFormatToRange
- copyValuesToRange
- for loop to setFunctions
I was trying to avoid using copyTo() because I figured for N of certain size the single commands copy*****ToRange would be more efficient that a for loop. Am I stuck with the for loop or is there any clever way I can copy the functions form a singe row to a range of rows?
But you can use copyTo to copy to a "wide" destination, you don't have to loop. e.g.
function copyTo() {
var s = SpreadsheetApp.getActiveSheet();
s.getRange('A2:D2').copyTo(s.getRange('A3:D10'));
}
Just tested it and it works normally.
But if you want to make the functions work, you should use the getFormulasR1C1 and setFormulasR1C1 pair. Another issue that might arise from setting the formulas after setting the values is that you'll have to skip the "pure values" cell, or you'll overwrite them with blank formulas. Using copyTo does seem to be the best approach.
这篇关于Google Spreadsheets脚本，copyTo vs copyValuesToRanges& copyFormatsToRanges的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
