Google-sheets – Cell formatting in Google Spreadsheets does not get preserved

conditional formattingformattinggoogle sheets

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

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

Expected Results

Text in column E is in font size 12

Actual Results

Text in column E is in smaller font size

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

Steps to Reproduce

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

Results Expected

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

Actual Results

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.

Best Answer

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

Related Solutions

Google-sheets – IMPORTRANGE with preserved formatting

May be an old question but its still being looked up - so here is the workaround:

Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data from- right click the sheet tab n click 'copy to'

when the browse file window pops up select the sheet you plan to use the importrange formula on.
once you get the successful copy message - open that sheet - press ctl A and press the delete key
then in cell A1 type your importrange formula - your data will now display with all conditional formatting rules that have been set in the original sheet which you are importing from.

check out my post on "importrange while preserving conditional formatting rules" https://productforums.google.com/forum/#!topic/docs/-KnnBPq4d24

Google-sheets – Google sheets recently changed American datetime format, how to change it back

In my experience, the cells remember any format they were given. Once a column is formatted M/d/yyyy it should stay that way.

There is no setting (other than the country setting) that controls the default date format. But entering the following script in the Script Editor (under Tools) will automatically format any dates you enter or edit from now on as M/d/yyyy.

function onEdit(e) {
  if (e.range.getNumberFormat() == 'm/d') {
    e.range.setNumberFormat('M/d/yyyy');
  }
}

Technical note: M/d/yyyy means 6/27/2016, while MM/dd/yyyy is 06/27/2016. If you prefer leading zeroes, change the setNumberFormat accordingly.

Another technical note: Google using m/d instead of M/d is inconsistent with SimpleDateFormat specification that they otherwise follow. This is something they decided to do a while back, to allow m instead of M in date formats.