在Google表格中自动大写单元格内容的脚本？ [英] Script to automatically capitalize contents of a cell in Google Sheets?

问题描述

我有一个电子表格，需要输入股票代码。我希望他们始终处于ALL CAPS状态，无论他们是如何输入的。这似乎需要一些脚本，因为除非存在第二个副本，否则无法使用函数执行此操作，这是不可接受的。 / p>

我有一个可以解决问题的解决方案。代码如下：

  function OnEdit（e）{
 var ss = e.source.getActiveSheet（） ，
 sheets = ['Trades'，'']，
 ind = sheets.indexOf（ss.getName（））; 
 if（ind === 0& e.range.rowStart> 1&& e.range.columnStart> = 1）{
 e.range.setValue（e。 value.toUpperCase（））; 
 
 
 $ / code $ / pre 
 
 
效果很好，并允许我添加尽可能多的标签和列按照我的意愿进行格式化。不幸的是，它还将单元格中的FORMULAS大写，这会破坏使用 importhtml（）函数的公式，因为它会大写所请求的URL。
 
 
 
所以，任何人都知道一种方法来完成上述代码的功能，但不能触及单元格内的实际公式，只能输出它们输出的文本？
 
 
 
编辑：感谢@ ocordova的评论，我认为我有足够好的工作来完成这项工作。不幸的是，它表现得很奇怪......它在一些栏目中起作用，而在其他栏目中根本不起作用。这是我目前的代码（为了清晰起见，稍微改变了）：
 
 
  function onEdit（e）{
 var activeSheet = e.source.getActiveSheet（），
 sheets = ['NEW Trades'，'']，
 sheetIndex = sheets.indexOf（activeSheet.getName（））; 
 if（sheetIndex === 0&& e.range.rowStart> 1&& e.range.columnStart> 0& e.range.getFormula（）=='' ）{
 e.range.setValue（e.value.toUpperCase（））; 
 
 
 
 
 
 
 $ b 
任何人都有任何想法，为什么某些列中的某些单元格会如预期的大写，其他单元格在这些相同的列将不会，而其他列将不会在任何地方大写？编辑2：我的麻烦似乎与数据验证有关或与数据验证有冲突。我试图大写的列是由另一个表单上的值列表提供的。如果值以前以小写形式出现，然后我将数据验证应用于列，则脚本不会将该值大写。但是，如果我从数据验证列表中选择适当的大写选择，然后以小写字母重新输入相同的值，则脚本会启动并大写。非常奇怪和混乱。编辑3：它与数据验证无关，因为它的行为方式与上述相同简单的列，根本没有验证。如果我以前输入的值已经是小写字母，那么再次输入小写字母将不会激活脚本。但如果我在CAPS中输入值，然后重新输入小写字母，则脚本将其大写。也许一些奇怪的情况涉及到脚本何时被触发......？
 
解决方案
 
 
 
知道一种方法来完成上述代码的功能，但不能触及单元格内的实际公式，只能输出它们输出的文本？ 
 
 
 
 <考虑在OP方法中做一些细微的改变：不要将所有单元格的内容都转换为大写，根据以下条件进行大写：
 
 
 
 
如果单元格值（包括包含常量或公式的单元格的值）不是字符串，则不执行任何操作。
 
如果单元格值是字符串
 
 
 
 
，单元格内容是一个常量，然后直接从脚本中改变大小写。单元格内容是一个公式，然后嵌套内置函数 UPPER 中的原始公式。
 
 
 
 
 
示例：
 
 
 
 function onEdit e）{
 var range = e.range; 
 var value = range.getValue（）; 
 var sheet = range.getSheet（）; 
 var sheetName = sheet.getName（）; 
 if（sheetName ==='Sheet1'&& 
 range.getRow（）> 1 && 
 range.getColumn（）> 1 && 
 typeof value ==='string' ）{
 if（！range.getFormula（））{
 range.setValue（value.toUpperCase（））; 
} else {
 if（range.getFormula（）。substring（0,6）.toUpperCase（）=='= UPPER'）{
 return; 
} else {
 range.setFormula（'= UPPER（'+ range.getFormula（）。substring（1）+'）'）; 
} 
} 
} 
} 
 
注意：
 
 
 
 
 
为简单起见，未包括 ind 数组。
 
 
  typeof e.value 总是返回'string'，所以改为 range.getValue（）; 被使用。
 
 
 
I have a spreadsheet that takes input of stock symbols.  I would like them to always be in ALL CAPS regardless of how they are typed in.  This appears to require some scripting as there is no way to do this with a function unless a second copy of the column exists, which is not acceptable.

I have a solution which works, with one critical problem.  The code is as follows:
function OnEdit(e) {
   var ss = e.source.getActiveSheet(),
       sheets = ['Trades', ''],
       ind = sheets.indexOf(ss.getName());
   if (ind === 0 && e.range.rowStart > 1 && e.range.columnStart >= 1 ) {
       e.range.setValue(e.value.toUpperCase());
   }
}
It works great, and allows me to add as many tabs and columns to format as I wish.  Unfortunately it also capitalizes the FORMULAS inside the cells, which is breaking formulas that use the importhtml() function, because it capitalizes the URL being requested.

So, anyone know a way to do exactly what the above code does, but not touch the actual formulas inside the cells, only the text that they output?

EDIT:  Thanks to @ocordova's comment, I thought I had something that would do the job well enough.  Unfortunately it's behaving strangely... it works partly o some columns, and not at all on others.  Here is my current code (slightly altered from earlier for clarity):
function onEdit(e){ 
   var activeSheet = e.source.getActiveSheet(),
       sheets = ['NEW Trades', ''],
       sheetIndex = sheets.indexOf(activeSheet.getName());
  if (sheetIndex === 0 && e.range.rowStart > 1 && e.range.columnStart >0  && e.range.getFormula() == '') {
    e.range.setValue(e.value.toUpperCase());
  }
}
Anyone have any ideas why some cells in some columns will capitalize as expected, and other cells in those same columns won't, and yet other columns won't capitalize at all, anywhere?

EDIT 2:  My trouble appears to be related to, or a conflict with, Data Validation.  The columns I'm trying to capitalize are fed by lists of values on another sheet.  If the value was present previously in lower case, and then I applied the data validation to the column, the script will not capitalize the value.  However if I select the appropriate, capitalized selection from the data validation list, and then re-type the same value in lower case, the script DOES kick in and capitalize.  Very strange and confusing.  I could be wrong about the conflict, but that's what it seems like to me.

EDIT 3:  It's not related to data validation, because it's behaving the same way on a simple column that has no validation at all.  If the value I had previously entered was already in lowercase, then typing it again in lowercase will not activate the script.  BUT if I type the value in CAPS, then re-type it in lowercase, the script capitalizes it.  Maybe some strange condition relating to when the script is triggered...?
解决方案

  
So, anyone know a way to do exactly what the above code does, but not touch the actual formulas inside the cells, only the text that they output?
Consider to make a slight change in the OP approach: rather than capitalize all the cells content for any case, capitalize according the following conditions: 

If the cell value, including the values of cells that holds constants or formulas, is not a string then do nothing .
If the cell value is a string


and the cell content is a constant, then change the case directly from the script.
and the cell content is a formula, then nest the original formula inside the built-in function UPPER.

Example:
function onEdit(e) {
  var range = e.range;
  var value = range.getValue();
  var sheet = range.getSheet();
  var sheetName = sheet.getName();
  if (sheetName === 'Sheet1' && 
      range.getRow() > 1 && 
      range.getColumn() > 1 && 
      typeof value === 'string') {
    if(!range.getFormula()) {
      range.setValue(value.toUpperCase());
    } else {
      if(range.getFormula().substring(0,6).toUpperCase() == '=UPPER') {
        return;
      } else {
        range.setFormula('=UPPER(' + range.getFormula().substring(1) + ')');
      }
    }
  }
}
Notes: 


For simplicity the ind array was not included.
typeof e.value always returns 'string', so instead range.getValue(); is used.


                        
这篇关于在Google表格中自动大写单元格内容的脚本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！