Applying “Automatic” number formatting

问题:

Is it possible to apply the 'Automatic' number format programmatically through GAS? My issue is that as I write columns of numbers, Sheets seems to attempt to apply appropriate formatting, but gets it wrong sometimes. That is, particular small integers (1 sometimes) will be formatted as dates. The range is being written in one myRange.setValues() method and I can't see any pattern to the mistakes and therefore don't see any way to prevent the surprise mis-formatting.

But, when I select the range in sheets and just click "Automatic" on the number format menu all returns to normal. It doesn't help to click that upfront as the writing of data somehow resets the format.

Despite the long-winded intro, my question is very simple: how to programmatically apply "Automatic" number formatting. I'm thinking this is very basic, especially since google and searches here have been no help.

My current fallback solution is to use myRange.setNumberFormat("0") as the format for the whole range. This is not ideal as some numbers are very large and are easier to read in scientific notation. There are also some text strings in the range, but these format properly regardless of format applied. I also would prefer to avoid having to iterate through the data and test for values to determine the best format, when it's just a couple clicks in the user interface.

回答1:

we can use .setNumberFormat('General');

Below is the example:

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange("B:B").setNumberFormat("General");

回答2:

I use copyFormatToRange to copy/apply Automatic format:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var source_cell = sheet.getRange("A1");//A1: cell having automatic format 
source_cell.copyFormatToRange(sheet,1,1,2,2);//copy format of cell A1 to cell A2

You can write an API that opens another spreadsheet, read any cell that having the automatic format.

var ss = SpreadsheetApp.openById(SpreadsheetId);//Id of another spreadsheet

Then use copyFormatToRange to your wanted cell.

回答3:

If you don't have dates in the range, the below solution appears to be the best available option (without resorting to an API-based solution):

myRange.setNumberFormat('0.###############');

A zero-point-15x'#' seems to be a 'magic' number format that will allow very large numbers to show as scientific notation and smaller integers and decimals to show in the 'standard' format pre-application of number formatting. This is also the format that is returned for cells that contain non-dates formatted with the 'Automatic' selection in the user interface.

Adding or removing even one # will 'break the spell' and cause very large numbers to display in non-scientific notation. I also tested changes before the decimal place, but leaving the 15x#:

Also functional: myRange.setNumberFormat('#,##0.###############');

So there is some flexibility for prefixes.

Non-functional: myRange.setNumberFormat('#.###############');

The 0 is evidently required.

And finally,

Non-functional: savegameRange.setNumberFormat('0.##############[red]');

This turns numbers red, but breaks the 'magic' formatting. So no suffixes it appears.

---

Again, if you have dates in the range, this will not work as they will, not surprisingly, display as the underlying number. And potentially more problematic (but totally understandable), the only way to return them to date form is manually applying a date format, assuming you know which cells 'were' dates.

Complete replication of 'Automatic' number formatting requires traversing the range to find dates and apply desired date format, but otherwise applying the 'magic' format. (My original dataset was a mix of numbers and strings, so the simple approach given above works.)