Applying "Automatic" number formatting

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

we can use .setNumberFormat('General');

Below is the example:

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

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