Google-sheets – Copy a column with mixed currencies using Google Apps Script

google sheetsgoogle-apps-script

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?

Best Answer

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).

Related Topic