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.

we can use .setNumberFormat('General');

Below is the example:

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

APPLYING, 29 synonyms of applying from the Merriam-Webster Thesaurus, plus 92 related words, definitions, and antonyms. Find another word for applying. Apply definition, to make use of as relevant, suitable, or pertinent: to apply a theory to a problem. See more.

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.

Apply, Define applying. applying synonyms, applying pronunciation, applying translation, English dictionary definition of applying. v. ap�plied , ap�ply�ing , ap� plies v. tr. Apply has another meaning. If you apply something to a surface, you put it onto the surface or rub it into it. This is a formal use of apply, which often occurs in written instructions. Apply the cream evenly.

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

Applying Synonyms, Applying Antonyms, applying. Verb form of the word apply. They were applying their judgement for their wedding venue choices. Submitted by MaryC on February� Apply definition is - to put to use especially for some practical purpose. How to use apply in a sentence.

Applying, Word forms: 3rd person singular present tense applies , present participle applying , past tense, past participle applied. 1. verb. If you apply for something such� apply verb (REQUEST) B1 [ I ] to request something, usually officially, especially in writing or by sending in a form: By the time I saw the job advertised it was already too late to apply.

What does applying mean?, Applying to become a CooperVision� Best Practice honoree can be completed in three simple steps. Please see below to learn more about the process of� Whether you’re applying to college for the first time or looking to complete your degree, Common App will help you reach new heights. Apply now Explore nearly 900 colleges and universities using Common App

Apply definition and meaning, Whether you're applying to college for the first time or looking to complete your degree, Common App will help you reach new heights. Apply now. Explore nearly� Planning for graduate school involves deciding what to study, finding the right school and applying to competitive programs. Start here, with our grad school guides, rankings and insider tips.

Comments
  • This is a really good question, and something that is definitely not documented. Closest I could find was stackoverflow.com/questions/13758913/… which only really overs plain text.
  • Really appreciate the comment. I searched and searched on this for nearly a week thinking that I must be missing something obvious. The only suspicion I have left is that "Automatic" formatting is the absence of any number formatting, but my efforts to assign null or undefined have been totally unsuccessful. myRange.setNumberFormat((typeof undefined)); // or just 'undefined' turns a cell with a 0 into: 'un301899fin189930' -- that is, it's parsing undefined as a string and then valiantly trying to format the cell as a date.(?) Passing null throws a server error.
  • And if anyone else is wondering myRange.setNumberFormat(""); does not work. It simply hides all numbers (and dates). Strings still show normally. Just like if you placed "" into the custom number format box in the user interface.
  • It's good to note that this will not set it to automatic to reader's looking for a Tl;Dr. I discovered this before i made my earlier comment. Hope someone can puzzle it out, or show proof of it being impossible.
  • Thanks. Hard to put yourself in others' shoes when writing. Updated to make more clear the limitations of the approach. The link you cited in your earlier comment was also interesting and led me to research more Java documentation on number formatting. There may be more answers there for particular situations, but I did not see any mention of a way to trigger automatic formatting in Java, in the hopes that the method would also work here.