Company logo
  • Empleos
  • Bootcamp
  • Acerca de nosotros
  • Para profesionales
    • Inicio
    • Empleos
    • Cursos y retos
    • Preguntas
    • Profesores
    • Bootcamp
  • Para empresas
    • Inicio
    • Nuestro proceso
    • Planes
    • Pruebas
    • Nómina
    • Blog
    • Calculadora

0

51
Vistas
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

7 months ago · Juan Pablo Isaza
3 Respuestas
Responde la pregunta

0

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 );
}
7 months ago · Juan Pablo Isaza Denunciar

0

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:

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

7 months ago · Juan Pablo Isaza Denunciar

0

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(); 
    } );
} 
7 months ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar empleo Planes Nuestro proceso Comercial
Legal
Términos y condiciones Política de privacidad
© 2023 PeakU Inc. All Rights Reserved.