Cell formatting in Google Spreadsheets does not get preserved

Question

My Google Spreadsheet does not preserve some formatting. Every time I set, for example font size to 12, for the whole sheet, newly copied data uses a smaller font size.

This only started being an issue after an upgrade to New Sheets, some time in March.

An example sheet that recreates the issue:

Steps to reproduce

1. CTRL + A twice to select the whole sheet
2. Set font size to 12
3. Copy & Paste clear text into column E

Expected Results

4. Text in column E is in font size 12

Actual Results

4. Text in column E is in smaller font size

UPDATE: To recreate the date formatting issue I have created another sheet.

Steps to Reproduce

1. Set date formatting on column D to DD MMM YYY (like 1 Jun 2015)
2. Insert some short test in a cell in column B

Results Expected

3. LMDT triggered on OnEdit event preserves the formatting: 1 Jun 2015

Actual Results

3. LMDT triggered on OnEdit event does not preserve the formatting: 01/06/2015 07:20:34

   time = Utilities.formatDate(time, "UTC", "dd-MM-yyyy **HH:mm:ss** ");
   
   s.getRange(r.getRow(), LMDTCol).setValue(time);
   

Yes, the formatting is set to dd-MM-yyyy HH:mm:ss but for last five years or so column formatting had higher priority than the formatting set in the script.

Answer 1

Manual paste

Indeed, pasting plain text with Ctrl-V results in it having the default size of 10. It seems that pasting is understood to overwrite the formatting of the cell, even if it's plain text coming in.

The solution is to paste using Ctrl-Shift-V, which means paste values only. Then the font size is 12 pt, as set in the spreadsheet, regardless of where the text came.

Script modification

Generally, when you simply use .setValue(value), the existing formatting of the cell remains. For example, if the cell A1 was formatted as "1 Jun 2015" then executing

sheet.getRange("A1").setValue(new Date());

will respect that formatting.

The format is changed only if the script explicitly applies a different formatting with Utilities.formatDate(). I would say this is to be expected, and the fact that the old version behaved differently is more of a bug or poor design.

It's known that the new sheets have a somewhat different format functionality.

Migration can be a breaking change for some scripts: for example, one of my scripts stopped working because it tried to store more than 50000 characters in a single cell (which was possible with the old version, but not with the new one).