Set a range to uppercase in sheets APP SCRIPT-CodePudding

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

CodePudding user response：

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.

Array.map()

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.

CodePudding user response：

Range.getValues returns 2 dimensional array. You will have to do toUpperCase() for each row and column

function allUpper() {
  var values = SpreadsheetApp.getActiveSheet().getRange( "A1:C" ).getValues();

  for ( var row = 0; row < values.length; row   ) {
      for ( var col = 0; col < values[ row ].length; col   ) {
          values[ row ][ col ] = values[ row ][ col ].toUpperCase();
      }
  }

  ss.getRange( "A1:C" ).setValues( values );
}

CodePudding user response：

There is no need for an inefficient nested for loop when you can do the following:

values.map( function( row ) {
    return row.map( function( cell ) { 
        cell = cell.toUpperCase(); 
    } );
}