应用“自动”数字格式

问题描述

是否可以通过GAS以编程方式应用'自动'数字格式？我的问题是，当我编写数字列时，Sheets似乎尝试应用适当的格式，但有时会出错。也就是说，特定的小整数（有时候是1）将被格式化为日期。范围正在写入一个

我使用 copyFormatToRange

  var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getActiveSheet（）; 
 var source_cell = sheet.getRange（A1）; // A1：具有自动格式的单元格
 source_cell.copyFormatToRange（sheet，1,1,2,2）; //复制单元格A1的格式到单元格A2 
  

您可以编写一个打开另一个电子表格的API，读取任何具有自动格式。

  var ss = SpreadsheetApp.openById（SpreadsheetId）; //另一个电子表格的ID 
  
 
 
 
然后使用 copyFormatToRange 到您想要的单元格。
 
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.

 解决方案 
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.
                        
这篇关于应用“自动”数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！