Convert Selected Data to Uppercase | Google Sheets
Convert Selected Data to Uppercase | Google Sheets
If you have ever needed to turn an entire column, row, or sheet of data into Uppercase... you're welcome
App Script Concepts
Custom Menu
Active Range
JavaScript Concepts
arrays
map
This tutorial will be more advanced, but it is probably an issue you have encountered during your time in Sheets or Excel. You need to turn convert selected data to uppercase.
Let's set the stage. You have just entered a bunch of data, and you are about to hand it off to another team, and they say, "That all needs to be in uppercase." Your heart sinks as you realize you will have to waste another 30 minutes of your day looking over the same data you just entered and do a repeating pattern of cut & paste.
Don't worry; that is a thing of the past!
Let's hop over to the App Script Editor.
toUpperCase()
function toUpperCase(){
const activeRange = SpreadsheetApp.getActiveRange();
const values = activeRange.getValues();
const rows = values.map(row => {
return row.map(value => String(value).toUpperCase())
})
activeRange.setValues(rows)
}We declare the function, and within the function, you want to get the activeRange, which are the cells you have selected within your Google Sheet.
Next line, we get the values of the cells in that activeRange. If you are a JavaScript expert, the output of it will be a nested array. That is the way that Google Sheets handles rows. Each row is an array, and each value within the row is an item of the array. Once I learned this, it changed the way I build sheets because I always want to make my sheets to ensure future automation is easy!
const rows = values.map(row => {
return row.map(value => String(value).toUpperCase())
})The next chunk of code is a JavaScript concept called mapping. What it does is it goes through all the items of the array and then outputs a different array. In the above example, we do nested mapping. This allows us to go row by row and, within each row, column by column, take the values in the cell, and change them to what we want in our case to UpperCase.
You can test this out by selecting some cells in a Google Sheet and then clicking Run in the Apps Script toolbar.
However, we don't want to be required to open the Apps Script editor to run the functions, so that is where custom menus come into play.
function onOpen(){
const ui = SpreadsheetApp.getUi()
ui.createMenu('Custom Menu')
.addItem('Uppercase', 'toUpperCase')
.addToUi()
}Do you have an 💡?
If you have automation ideas you would like built, reach out to me for a free consultation!
When you declare a function with the name onOpen, every time you open your Google Sheet, it will automatically run and, in this case, automatically create a custom menu with our Uppercase function.
In the first line, we are getting the Sheets UI, which allows us to add things to the Sheets UI.
ui.createMenu('Custom Menu')
.addItem('Uppercase', 'toUpperCase')
.addToUi()In the following lines, we create a menu that will show up in the top toolbar, and I gave it the name Custom Menu. Then below it we add an item, a button to which we can link our custom function. Last and very importantly, we have to add it to our UI. I can't tell you how many times I forgot this and would wonder why my menus weren't showing up.
Complete Code
function onOpen(){
const ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Uppercase', 'toUpperCase')
.addToUi()
}
function toUpperCase(){
const activeRange = SpreadsheetApp.getActiveRange();
const values = activeRange.getValues();
const rows = values.map(row => {
return row.map(value => String(value).toUpperCase())
})
activeRange.setValues(rows)
}Let's See it in Action
Support my Work
If this tutorial was helpful to you and you want to support my work, consider buying me a slice of 🍕, my favorite food, by quite a wide margin!