应用“自动”数字格式 [英] Applying "Automatic" number formatting

问题描述

是否可以通过GAS以编程方式应用'自动'数字格式？我的问题是，当我编写数字列时，Sheets似乎尝试应用适当的格式，但有时会出错。也就是说，特定的小整数（有时候是1）将被格式化为日期。范围正在写入一个 myRange.setValues（）方法，我看不出任何模式的错误，因此看不到任何方法来防止意外错误

但是，当我在表格中选择范围并且只需在数字格式菜单上单击自动时，全部恢复正常。它没有帮助点击这个预先写入数据以某种方式重置格式。

尽管介绍冗长，我的问题很简单：如何以编程方式应用自动数字格式。我认为这是非常基本的，特别是因为谷歌和搜索这里没有帮助。

我当前的回退解决方案是使用

解决方案

我使用 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.

                        
这篇关于应用“自动”数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！