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.