在Google Sheets中自动大写单元格内容的脚本

Script to automatically capitalize contents of a cell in Google Sheets?

本文关键字：单元格 脚本 Sheets Google      更新时间：2023-09-26

我有一个输入股票符号的电子表格。我希望它们无论是如何键入的，都始终是大写的。这似乎需要一些脚本，因为除非存在列的第二个副本，否则无法用函数执行此操作，这是不可接受的。

我有一个有效的解决方案，解决一个关键问题。代码如下：

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());
   }
}

它工作得很好，允许我添加任意多的选项卡和列以进行格式化。不幸的是，它还将单元格内的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());
  }
}

有人知道为什么某些列中的某些单元格会按预期大写，而同一列中的其他单元格不会，而其他列在任何地方都不会大写吗？

编辑2：我的问题似乎与数据验证有关，或与数据验证冲突。我试图大写的列由另一张表上的值列表提供。如果该值以前是小写的，然后我将数据验证应用于列，则脚本不会将该值大写。但是，如果我从数据验证列表中选择了适当的大写选择，然后用小写重新键入相同的值，脚本就会启动并大写。非常奇怪和令人困惑。我对这场冲突的看法可能是错误的，但对我来说就是这样

编辑3：它与数据验证无关，因为它在一个根本没有验证的简单列上的行为方式相同。如果我之前输入的值已经是小写的，那么再次用小写键入它将不会激活脚本。但是，如果我用大写字母键入值，然后用小写字母重新键入，脚本就会将其大写。也许是与脚本何时触发有关的一些奇怪条件。。。？

如果不想在单元格包含公式时大写，可以使用getFormula（）方法检查单元格是否包含公式。

返回范围左上角单元格的公式（A1表示法），如果单元格为空或不包含公式，则返回空字符串。

代码应该是这样的：

if (ind === 0 && e.range.rowStart > 1 && e.range.columnStart >= 1  && e.range.getFormula() == '') {
  e.range.setValue(e.value.toUpperCase());
}

编辑：

如果我理解正确的话，您键入的值完全相同，例如：如果单元格中的值是México，并且您删除了所有或部分字符，然后在中间再次键入México。在这种情况下，旧值和新值相同，OnEdit()不会被激发。另一个例子是，如果您更改了值的格式，那就是另一种类型的事件。

如果你想知道事件是如何被考虑的，你可以使用一个可安装的更改触发器：

function triggerOnChange(e) {
  MailApp.sendEmail('john.doe@gmail.com', 'Testing triggerOnChange', JSON.stringify(e));
}

然后在脚本编辑器菜单中：Resources -> Current Project Triggers -> Add a new trigger -> ['triggerOnChange', 'From spreadsheet', 'On change']

关于如何更改公式结果的大小写，我认为@Rubén的想法是正确的，但只有当公式的第一个字符中包含UPPER()时，它才会起作用，而且由于您使用的是公式IMPORTHTML()，使用UPPER()会破坏它，可能还会破坏一些其他函数，如数组公式，除非您使用INDEX():

=INDEX(UPPER(IMPORTHTML(url, query, index)))

另一种选择可能是正则表达式，但考虑到所有的组合，我认为这有点冒险。

所以，有人知道一种方法可以做到上面代码所做的事情，但不接触单元格内的实际公式，只接触它们输出的文本吗？

考虑在OP方法中做一个细微的改变：在任何情况下，都不要将所有单元格内容大写，而是根据以下条件进行大写：

  1. 如果单元格值（包括包含常量或公式的单元格的值）不是字符串，则不执行任何操作
  2. 如果单元格值是字符串
    • 并且单元格内容是常量，则直接从脚本更改大小写
    • 并且单元格内容是一个公式，然后将原始公式嵌套在内置函数UPPER中

示例：

函数onEdit（e）{var范围=e.range；var值=范围.getValue（）；var sheet=range.getSheet（）；var sheetName=sheet.getName（）；if（sheetName==='Sheet1'&&range.getRow（）>1&&range.getColumn（）>1&&值的类型==='string'）{if（！range.getFormula（））{range.setValue（value.toUpperCase（））；}其他{if（range.getFormula（）.substring（0.6）.toUpperCase（）=='=UPPER'）{回来}其他{range.setFormula（'=UPPER（'+range.getFormula（）.substring（1）+'）'）；}}}}

注：

  • 为简便起见，未包括ind阵列
  • typeof e.value总是返回'string'，因此使用range.getValue();
相关文章：