3

I have a google spreadsheet tab that needs to be copied to another sheet. In most columns I can set the formatting to the proper currency formatting, so when copying the column, the format is kept and the data is copied correctly.

In one of the columns I have some of the cells in one currency, and some other cells in another currency, therefore I cannot apply a currency formatting to this column, and this column has an automatic formatting.

When copying this column, the numbers are copied as values without formatting and displayed as plain numbers.

I am copying the values with the CopyValuesToRange method.

Is there a way to copy the text as is - with the currency sign? Or maybe apply a conditional currency formatting for each cell based on the cell's text?

3

The method copyValuesToRange does what its name suggests: copies values to range. You want to copy both the values and formatting. For this, use the copyTo method:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1:A5").copyTo(sheet.getRange("B1:B5"), {formatOnly: true});
sheet.getRange("A1:A5").copyTo(sheet.getRange("B1:B5"), {contentsOnly: true});

The first step, formatOnly: true, copies over the formatting. The second copies values only (static values, not the formulas that they came from).

  • Thanks, the problem is that the CopyTo method copies the formulas of the cell as well, which I don't want because the formulas are referring to other sheets in this spreadsheet, and I am copying this sheet to another spreadsheet, and that causes errors. Using the {contentsOnly:true} options acts just like the CopyValuesToRange method – offa Jul 18 '16 at 20:18
  • Doing it separately with format and contents seems to work: see the updated answer. – user79865 Jul 18 '16 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.