CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nigel Johnson
Nigel Johnson

Reputation: 522

Importing a range

Initially, I hadn't found the range.copyValuesToRange() so I had written this:

var rr = src.getRange(start_row, start_col, nrows, 1).getValues();
dst.getRange(start_row, start_col+1, nrows, 1).setValues(rr);

And I did similar stuff for larger ranges, so I reworked the above to this:

var rr = src.getRange(start_row, start_col, nrows, 1);
rr.copyValuesToRange(dst, start_col+1, start_col+1, start_row, start_row+nrows);

I am doing some basic logging to another sheet but I'm not seeing a lot of execution speed difference in them. The documentation says to do stuff in batches which is what I'm doing, but is there the extra overhead of doing a conversion to the arrays in both directions?

If there are lots of blank cells in the src range, how will these approaches impact the dst cell count?

Upvotes: 0

Views: 46

Answers (1)

user6655984
user6655984

Reputation:

I suggest using copyTo (with option contentsOnly: true to match what you are currently doing), as the parameters of this method are more consistent with the rest of Apps Script. But this is just because of the aesthetics, not for performance reasons. The process of a script accessing spreadsheet data is so slow that any additional conversion that needs to be done should be negligible.

Blank cells are copied like any others, the blank state has no effect on how you use the methods. One reason I like copyTo is not having to spell out the coordinates of the destination: as the documentation says, only the top-left cell of the destination range is relevant. So, I would write a one-liner 

src.getRange(start_row, start_col, nrows, 1).copyTo(dst.getRange(start_row, start_col), {contentsOnly: true})

Without {contentsOnly: true} the effect is equivalent to normal copy-paste, also carrying over formulas and formatting. with {contentsOnly: true} it's equivalent to copy and paste-values-only. If additional rows or columns are needed in the destination, they will be automatically inserted.

Upvotes: 2

Related Questions