Script to automatically capitalize contents of a c

问题:

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

回答1:

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:

1. If the cell value, including the values of cells that holds constants or formulas, is not a string then do nothing .
2. 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.

回答2:

If you don't want to capitalize if the cell contains a formula, you can use the method getFormula() and check if the cell contains a formula.

Returns the formula (A1 notation) for the top-left cell of the range, or an empty string if the cell is empty or doesn't contain a formula.

The code should look like this:

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

EDIT:

If I've understood you correctly, you're typing exactly the same value, example: if the value in the cell is México, and you delete all or some characters and inmediately type México again, in that scenario the old value and the new value are the same and the OnEdit() won't be fired. Another example is if you change the format of the value, that's another type of event.

If you want know how the event is considered, you can use an installable on change trigger:

function triggerOnChange(e) {
  MailApp.sendEmail('[e-mail]', 'Testing triggerOnChange', JSON.stringify(e));
}

Then in the Script Editor menu: Resources -> Current Project Triggers -> Add a new trigger -> ['triggerOnChange', 'From spreadsheet', 'On change']

On how to change the case of the formula's result, I think @Rubén has the right idea, but it will only work if the formula contains UPPER() in the first characters, and also since you're using the formula IMPORTHTML() using UPPER() will break it and maybe some other functions like array formulas, unless you use INDEX():

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

Another option could be Regular expressions, but I think it's a little risky considering all the combinations.