Google Spreadsheets script, copyTo vs copyValuesToRanges & copyFormatsToRanges

Question

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?

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2014-08-12T13:55:53

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 script, copyTo vs copyValuesToRanges & copyFormatsToRanges

1 Answers