4

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?

Community
  • 1
  • 1
Niccolo
  • 817
  • 1
  • 10
  • 17

1 Answers1

3

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.

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • I used the google documentation https://developers.google.com/apps-script/reference/spreadsheet/range#copyTo%28Range%29 where for copyTo() it specifically states "a destination range to copy to; only the top-left cell position is relevant" while for copyValues " If the destination is larger or smaller than the source range then the source will be repeated or truncated accordingly." This implied that copyTo() copies a range to another of the same size because the target range "only the top-left cell"!!!! That solves everything. Thank you. My script is now much faster. – Niccolo Aug 12 '14 at 14:24
  • I agree with that this explanation is unnecessarily complicated. But nevertheless it says that it is going to be "repeated or truncated". After some effort (and testing) I understood that this is a copy 'n paste as we expect. – Henrique G. Abreu Aug 13 '14 at 14:18
  • My problem was that the copyTo() function does not mention "repeated or truncated". It has a variant copyTo(destination, options) "options" seem to make this the copyTo() version of same as copyValuesToRange() or copyFormatsToRange(). It copyTo() spans ranges "repeated or truncated" as copyValues/FormatsToRange(), it appears to have the same functionality. The "ToRange" term in the latter combined with the description lead me to believe that copyTo() was different the latter functions. – Niccolo Aug 13 '14 at 14:48