/docs/community?hl=en
/docs/community?hl=en
6/15/17
Original Poster
crossbeats

Google Sheets - Script for TitleCase & Lowercase - on specific columns

This is a sheet populated by a form, so I would like the script to run on form submit.

I need Columns: C, F, G, H, L, M, R, and S to all be TitleCase
Column L is for street address, so those fields will have numbers.

I need Column J to be lowercase - this column holds email addresses, so it needs to account for that.

I've found several scripts for TitleCase, but not a single one I've found seems to work.

I can't allow for edit access on my sheet because there's a ton of data - but it can be viewed here to get an idea of what we've got going on: https://docs.google.com/spreadsheets/d/1zE7mfoTECFNoxrao6knxzZDbREiGg7we7-uK7hDkmf0/edit?usp=sharing 

Community content may not be verified or up-to-date. Learn more.
All Replies (4)
Selena - Community Specialist
6/16/17
Selena - Community Specialist
Hi there,

Welcome to the Google Docs Help Forum.

As you have a question about using Apps Scripts in Google Sheet, I suggest cross posting your question to the Google Apps Script G+ community for better insight. By posting in this forum, there may be instances where Apps Script questions will not be answered, such as the questions are too broad or the question may be too intensive to receive a timely reply in the forum. 

If you do not receive a timely reply here, you might like to try these other resources:
  • If you have already attempted some Apps Script code and need to troubleshoot why it is not working, you should visit the Google Apps Script online documentation for Developers.
  • Support questions are also discussed in Stack Overflow. Please include the code if you have tried already.
  • If you would like to contact someone to write the code for you, you may also find the Jobs category of the Google Apps Script G+ community a good resource. 
To get you started, try out this tutorial that teaches Scripts basics.

Hope this information helps.

Best,
Selena
RaucusBacchus
6/16/17
RaucusBacchus
Character case functions include: =UPPER, =LOWER, =PROPER.
As a means to converting a column to Title Case, create a duplicate column and add the formula =PROPER() with the brackets referencing the sour cell you would like to convert.
Example: If column "A" contains the source and column "B" contains your correction formula use =PROPER(A:A). Any "B" cell with this formula will fill in with the corrected info from "A".
You can then Copy column "B" and Paste Special/Format Only over column "A" to replace the original values.
~m
RaucusBacchus
6/16/17
RaucusBacchus
Here's another tip when using Copy and Paste commands: 
Ctrl+Shift+V pastes without format. With this in mind, you can copy the content from the corrected column as normal, then you the Ctrl+Shift+V command to paste without wrecking any formatting you may have outside of the case change.
~m
Jean-Pierre Verhulst
6/16/17
Jean-Pierre Verhulst
Hi crossbeats,

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 them.

(note: authorise the script when prompted).

See if that works?


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
}
Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.

false
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false