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
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 );
}
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.
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();
} );
}