1

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

  1. Text in column E is in font size 12

Actual Results

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

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

Actual Results

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

|improve this question
  • I edited my answer. Not much to say about script part other than "yes, this is how new version works, and some scripts might break in it". – user79865 Jun 1 '15 at 19:39
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).

|improve this answer
  • Thank you for the answer but I would call it a hack rather than a solution. The reason for that is that date inserted on OnEdit event, like LMDT, does not preserve date formatting either. All the scripts that I wrote years ago now suddenly, after upgrade to New Sheets started, having formatting issues that cannot be simply resolved with Ctrl+Shift+V – matcheek Jun 1 '15 at 7:05
  • 1
    This merits a separate question, since this one was explicitly about pasting with Ctrl-V rather than with a script. So far, I was unable to reproduce your issue with a script. Using a method sheet.getRange("A1").setValue(...); to insert values with a script, I find original formatting preserved, be it font size, currency format or date format. – user79865 Jun 1 '15 at 7:21
  • OK, updated the question with a working example. – matcheek Jun 1 '15 at 7:32

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.