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