I've got the following script and it works fine, except I seem to have to modify the sheet (every month) for it to work. Is there a way I can just have it run this script for all the tabs in my Spreadsheet automatically?
function onEdit(e) {
var range = e.range;
var value = range.getValue();
var sheet = range.getSheet();
var sheetName = sheet.getName();
if (sheetName === 'Nov. 2021' && //only do the current sheet!
range.getRow() > 1 &&
range.getColumn() == 6 &&
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) ')');
}
}
}
}
Thanks in advance!
CodePudding user response:
Try this:
function onEdit(e) {
if (e.range.rowStart > 1 && e.range.columnStart == 6 && e.range.getFormula().subString(0, 6).toUpperCase() != '=UPPER') {
e.range.setFormula('=UPPER(' range.getFormula().substring(1) ')');
}
}
CodePudding user response:
I figured it out. My code is below:
function onEdit(e) {
var range = e.range;
var value = range.getValue();
if (e.range.rowStart > 1 && e.range.columnStart == 6 && 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) ')');
}
}
}
}
Thanks guys for pointing me in the right direction.