Comments
Comments cannot be copied. They are not considered part of the contents of a spreadsheet, they are more like chat messages, the record of a conversation that took place about that spreadsheet. If there was no such conversation about a copy of that spreadsheet, comments won't be there.
Alternative: use notes instead of comments. They get copied same way as cell contents.
Formatting
In the situation you have, it'd be better to create a blank template of grade report, with all the formatting, and then copy that to individual students. importrange
is meant for importing data, not formatting. (Also, it does not import notes or comments.)
Instead of getFormulas and setFormulas, use copyTo
method:
range.copyTo(newRange);
This will have the same effect as if you selected the last row and copied it below. In particular, it will also copy down any values that were entered directly into cells. If you prefer such values to not be copied, use getFormulas/setFormulas combination on newRange:
range.copyTo(newRange);
newRange.setFormulas(newRange.getFormulas());
The effect of the last line: no change of formulas, but the cells that didn't contain a formula will become empty.
R1C1 approach
You can also try replacing the last three rows of addRow
with
var formulas = range.getFormulasR1C1();
var newRange = sh.getRange(lRow+1,1,1,lCol);
newRange.setFormulasR1C1(formulas);
This will make sure the references in formulas are remapped as if you copy-pasted them. However, this has a major limitation: setFormulasR1C1 doesn't work correctly with empty string as its argument, which will result in #ERROR! messages in the cells that should be left blank.
The reason R1C1 works differently is that getFormulasR1C1
retrieves formulas in a relative form: for example, a reference from A3 to D2 becomes R[-1]C[3]
, meaning "one row up, three columns to the right". When the formula is set to, for example, E4 with setFormulasR1C1
, the cell reference will appear as H3.
Best Answer
Use the code below to copy also formula's as normal values. Add the code by selecting Tools from the spreadsheet menu. Then select script editor and add the code. Make sure to press the "bug" button and authenticate the script.
Code
Remark
Setting the contentOnly to
false
will yield a standard copy. Setting it totrue
, will paste only values. The example script you found, does way more then pasting values.....Example
I've created an example file for you: Add Row With Formula's