Why isn't my cell converting to upper case in google sheets?

Question

I get the active sheet, then the active range(a single cell in this case), then the value of that cell, then I convert the value to upper case, then I set that to the value of the cell. I am not coming out the other end with an upper case cell.

I have tried both converting the cell to upper case(this gives me an error), and also converting the value of the cell to upper case, then setting that to the new cell value.

function makeUC() {
  //get sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  //get active range (selected cells)
  var cell = sheet.getActiveRange();

  //get UI for alert functionality
  var ui = SpreadsheetApp.getUi();

  //for testing
  //ui.alert(range.getValue());

  //get value of active cell
  var val = cell.getValue();

  //test 1 - convert value to upper case/// not working. TypeError: Cannot 
  find function toUpperCase in object Range.
  cell.toUpperCase();

  //test 2 part 1 - convert value to upper case/// not working
  val.toUpperCase();

  //test 2 part 2 - set range to new value
  cell.setValue(val);

}

error messages in code section

score 2 · Accepted Answer · answered Jul 18 '19 at 22:43

String.prototype.toUpperCase() works on objects that can be converted to strings. Unfortunately the Range objects aren't converted to strings automatically.

In the case of val.toUpperCase();, the value assigned to val isn't changed. Change the following code lines

//test 2 part 1 - convert value to upper case/// not working
val.toUpperCase();

//test 2 part 2 - set range to new value
cell.setValue(val);

to

//test 2 part 1 - convert value to upper case/// not working
var upperCaseValue = val.toUpperCase();

//test 2 part 2 - set range to new value
cell.setValue(upperCaseValue);

Converting an object to a string

score 0 · Answer 2 · answered Jul 18 '19 at 22:43

Try this:

function makeUC() {
  var sheet=SpreadsheetApp.getActive().getActiveSheet();
  var cell=sheet.getActiveRange();
  var val=cell.getValue();
  cell.setValue(val.toUpperCase());//toUpperCase doesnt change string in place it returns the uppercase
}

String.toUpperCase

Why isn't my cell converting to upper case in google sheets?

2 Answers2