Skip to content
Advertisement

Set a range to uppercase in sheets APP SCRIPT

I want to create a script that I will have in a menu that gets the range “A1:C” and uppercases it. The problem is that throws an error “TypeError: values.toUpperCase is not a function” trying to use toUpperCase().

Got the following code:

function allUpper() {
  var values = SpreadsheetApp.getActiveSheet().getRange("A1:C").getValues();
  var valuesUpper = values.toUpperCase();
  ss.getRange("A1:C").setValue(valuesUpper);
}

Im pretty new to JS and sheets api. I feel dumb because it looks like something simple.

EDIT 1: Now I know that .toUpperCase() doesnt work on arrays. But the proposed solutions of mapping the array and looping through the elements inside is still throwing the same error with toUpperCase();

EDIT 2: I upload a sample of my data requested by @NEWAZA Sample

Advertisement

Answer

Try:

function allUpper() {
  const range = SpreadsheetApp.getActiveSheet()
                              .getRange("A1:C")

  const values = range.getDisplayValues()
                      .map(row => row.map(col => (col) ? col.toUpperCase() : col))

  range.setValues(values);
}

Once we get our values in values, we use .map() to loop over each row, and within each row, we loop over each cell/column and set to uppercase.

Another common way to do this is to use for loops and nest one inside of another.

Read More:

An important thing to note when modifying a range of values is that you will need to make sure the range you are setting is the same ‘size’ as these values.

Originally you were getting values from A1:C and trying to set them into A1, which would not work due to difference in ‘size’.

Edit: Addressed blank cells in column/cell mapping.

(col) ? col.toUpperCase() : col

If item has value, modify it to .toUpperCase(), or leave be.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement