Google-sheets – Making select columns UPPERCASE

google sheetsgoogle-apps-script

In relation to the post linked below, is there a similar script out there that will apply this same functionality to only certain columns in a Google Sheet?

Convert all text to UPPERCASE in a Google Spreadsheet

Best Answer

Add below scripts to the script editor and save it. Then refresh your spreadsheet. After refreshing you should have a new menu-item in the spreadsheet, called 'Custom Menu'. Now you can select a column(s), and run any of the three provided functions on that selection.

As a bonus, it will ask you what to do when formulas are found: overwrite them (with the value) or keep authorize> (note: authorise the script when prompted).

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Custom Menu')
        .addItem('to uppercase', 'upper')
        .addItem('to lowercase', 'lower')
        .addItem('to titlecase', 'proper')
        .addToUi();
}

function lower() {
    run(toLowerCase)
}

function upper() {
    run(toUpperCase)
}

function proper() {
    run(toTitleCase)
}

function run(fn) {

    var r, s, v, f;

    s = SpreadsheetApp.getActiveSheet(),
    r = s.getActiveRange()
    v = r.getValues();
    f = r.getFormulas()

    r.setValues(
        v.map(function (ro) {
            return ro.map(function (el) {
                return !el ? null : typeof el !== 'string' && el ? el : fn(el);
            })
        })
    )
    keepFormulas(s, r, f);
}

function toUpperCase(str) {
    return str.toUpperCase();
}

function toLowerCase(str) {
    return str.toLowerCase();
}

function toTitleCase(str) {
    return str.replace(/\w\S*/g, function (txt) {
        return txt.charAt(0)
            .toUpperCase() + txt.substr(1)
            .toLowerCase();
    });
}

function keepFormulas(sheet, range, formulas) {

    var startRow, startColumn, ui, response;

    startRow = range.getRow();
    startColumn = range.getColumn();

    if (hasFormulas(formulas)) {

        ui = SpreadsheetApp.getUi();
        response = ui.alert('FORMULAS FOUND', 'Keep formulas ?', ui.ButtonSet.YES_NO);

        if (response == ui.Button.YES) {
            formulas.forEach(function (r, i) {
                r.forEach(function (c, j) {
                    if (c) sheet.getRange((startRow + i), (startColumn + j))
                        .setFormula(formulas[i][j])
                })
            })
        }
    }
}

function hasFormulas(formulas) {
    return formulas.reduce(function (a, b) {
        return a.concat(b);
    })
        .filter(String)
        .length > 0
}
Related Topic