I found the following code to emulate the proper formula, but it has a wrong ( maybe outdated) syntax, and as far as i understood, it should applies to all columns of a given sheet.

function PROPER_CASE(str) {
  if (typeof str != "string")
    throw `Expected string but got a ${typeof str} value.`;
  
  str = str.toLowerCase();

  var arr = str.split(/.-:?—/ );
  
  return arr.reduce(function(val, current) {
    return val  = (current.charAt(0).toUpperCase()   current.slice(1));
  }, "");
}

Here's an example of the input :

This would be the desired output:

ColumnA                             ColumnB        ColumnC   COlumnD 
Excel Action Limited (毅添有限公司)   207/2018         n/a        without-proper
Hang Wo Holdings                     205/2015         35/2020    without-proper
Central Southwood Limited           308/2019         n/a       without-proper

And this is the error output of that function :

Erro    
Expected string but got a undefined value.
PROPER_CASE @ macros.gs:115

CodePudding user response：

I have tested your code and it works fine. It does convert the input string into a proper case.

However, take note that in Google Sheets, when you get values, your data is in 2D Array or Nested Array.

So to apply this to your Spreadsheet after getting the values you will have to target the column you want to replace and loop through each string in the array. You will then have to setValues() back to the specified range to replace it in the spreadsheet.

Solution 1:

Try:

With your function, try adding this script to apply to your spreadsheet.

function setToColumn(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getRange(1,1,sheet.getLastRow()); //2ND Parameter is the column, replace if you want to edit different column
  var allData = dataRange.getValues().flat();
  var properData = []

  allData.forEach(function(data){
    properData.push([PROPER_CASE(data)])
  });

  dataRange.setValues(properData);
}

From:

Result:

Solution 2:

If you don't mind using different script which only needs one function you may use the script below:

function properCase() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getRange(1,1,sheet.getLastRow()); //2ND Parameter is the column, replace if you want to edit different column (1 = Column A, 2 = Column B)
  var allData = dataRange.getValues().flat();
  var properData = []

  allData.forEach(function(data){
     properData.push([data.toLowerCase().replace(/\b[a-z]/ig, function(match) {return match.toUpperCase()})]);
  });

  dataRange.setValues(properData);
}

Reference for Solution 2: Apps script how to format a cell to Proper Text (Case)

CodePudding user response：

This is the only way I can see of reproducing you results. I don't see how to avoid captalizing the first letter of the last two columns with avoiding them:

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  if (sh.getLastRow() > 4) {
    sh.getRange(6, 1, sh.getLastRow() - 5, sh.getLastColumn()).clearContent();
    SpreadsheetApp.flush();
  }
  const vs = sh.getDataRange().getDisplayValues().map((r, i) => {
    return r.map((c, j) => {
      if (i > 0 && j < 1) {
        let arr = c.toString().toLowerCase().split(/.-:?-/g);
        return arr.reduce((val, current) => {
          //Logger.log(current)
          return val  = current.charAt(0).toUpperCase()   current.slice(1);
        }, '');
      } else {
        return c;
      }
    });
  });
  Logger.log(JSON.stringify(vs))
  sh.getRange(sh.getLastRow()   2, 1, vs.length, vs[0].length).setValues(vs);
}