Applying "Automatic" number formatting / number formatting reset

Question

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.

Answer 1

You can try .setNumberFormat('General');

Below is the example:

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

Answer 2

I was having trouble finding anything documented, and tried pretty much everything suggested previously (null, 'General', the "magic" format of '0.###############', etc., etc.).

In my particular case, I had ranges previously set to strict plain text, which then got replaced with a checkbox data validation. Anytime the box was checked it was converted to the text "TRUE" instead of remaining a checkbox. 'General' and the "magic" format functionally worked fine, but did not actually set the format back explicitly to "Automatic".

I finally decided, why not just try this:

range.setNumberFormat('Automatic');

And it worked. This really should be documented, but at least a little bit of common sense lead me to the answer regardless.

Answer 3

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.

Answer 4

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