Copy and paste Value and Format Only - Google Script

Question

I'd like to copy and paste just values and format - not formula..

E.G (A1 is a dynamic text):

A1 = "test"

A2 = =A1

I'd like to use a script that when activated it copy A2 format and text (in this case would copy "test" - not "=a1") to A3 .

I tried with "formatOnly:true , contentsOnly:true" - but it keep copying formula.

function Threebrow() {  
   var sheet2 = SpreadsheetApp.getActive().getSheetByName('c');
  sheet2.getRange("a2").copyTo(sheet2.getRange(sheet2.getLastRow()+1,1,1,7), {formatOnly:true , contentsOnly:true});
}

score 22 · Accepted Answer · answered Sep 28 '17 at 08:39

22

Formulas are also counted as content in this context. The below function copies value and format of A2 to the lowest row in the first column.

function Threebrow() {  
  var sheet2 = SpreadsheetApp.getActive().getSheetByName('c');
  var sourceRange = sheet2.getRange("a2");
  var targetRange = sheet2.getRange(sheet2.getLastRow()+1,1,1,1);
  targetRange.setValues(sourceRange.getValues());
  sourceRange.copyTo(targetRange, {formatOnly:true})
}

answered Sep 28 '17 at 08:39

Robin Gertenbach

10,316
3
25
37

awesome - works great and its so much faster then copy cell by cell – Tobias Sarnow Jan 10 '19 at 19:01
How would you do this for an entire sheet? A1 to last column and row? – Matt Winer May 29 '19 at 20:14
1

`getDataRange` instead of `getRange`. – Robin Gertenbach May 29 '19 at 21:21

score 0 · Answer 2 · answered Dec 30 '19 at 12:38

More information requested: my function is not working on line n°9

function CopyFormatting() {
//Déclaration des variables
var SheetResponse = SpreadsheetApp.getActiveSheet(); //Sheet of forms response
var LastRow = SheetResponse.getLastRow(); //last row of sheet (n)
var RefRow = SheetResponse.getRange("3:3"); //Row n°3 considered as reference

//Copy formatting to last row from reference row
RefRow.copyTo(LastRow, {formatOnly: true});
}

Copy and paste Value and Format Only - Google Script

2 Answers2

Linked