0

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

  • 1
    Welcome. It's very fine to make reference to another post on this site but in order to make your question better received, please add a brief description of your search/research efforts to accomplish what you are looking for as it's suggested on How to Ask. – Rubén Jan 4 at 22:46
1

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
}
  • This is perfect - thank you so very much! – Nancy Jan 17 at 14:14

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.