I can Trim my dada with the script below

But is there a way Clean data in Google using a Google Script similar to Clean in VBA?

i.e. Remove all non-printing characters

I am unable to find-replace on data copied and pasted into GS from another source

Thanks

function trimSpacesSHT(shtName) {
 var sheet = SpreadsheetApp.getActive().getSheetByName(shtName);
 var activeRange = sheet.getDataRange();

  // Read Height and Width only once
  var maxHeight = activeRange.getHeight();
  var maxWidth = activeRange.getWidth();

  // Read all values and formulas at once
  var rangeValues = activeRange.getValues();
 // iterate through all cells in the selected range
 for (var cellRow = 0; cellRow < maxHeight; cellRow++) {
    for (var cellColumn = 0; cellColumn < maxWidth; cellColumn++) {
    rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn].toString().trim().replace(/\s(?=\s)/g,'');
  }
}
 // Write back all values at once
 activeRange.setValues(rangeValues);
}

I can Trim my dada with the script below

But is there a way Clean data in Google using a Google Script similar to Clean in VBA?

i.e. Remove all non-printing characters

I am unable to find-replace on data copied and pasted into GS from another source

Thanks

function trimSpacesSHT(shtName) {
 var sheet = SpreadsheetApp.getActive().getSheetByName(shtName);
 var activeRange = sheet.getDataRange();

  // Read Height and Width only once
  var maxHeight = activeRange.getHeight();
  var maxWidth = activeRange.getWidth();

  // Read all values and formulas at once
  var rangeValues = activeRange.getValues();
 // iterate through all cells in the selected range
 for (var cellRow = 0; cellRow < maxHeight; cellRow++) {
    for (var cellColumn = 0; cellColumn < maxWidth; cellColumn++) {
    rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn].toString().trim().replace(/\s(?=\s)/g,'');
  }
}
 // Write back all values at once
 activeRange.setValues(rangeValues);
}

• javascript
• google-apps-script
• google-sheets

• Explain what you're trying to do – tehhowch Commented Apr 26, 2018 at 21:16
• Remove all nonprinting characters – xyz Commented Apr 26, 2018 at 22:08
• What do you mean by I am unable to find-replace on data copied and pasted into GS from another source? What is your goal? Where are you pasting text? What have you tried to do with existing String methods? – tehhowch Commented Apr 26, 2018 at 22:57
• 2 =arrayformula(clean(a1:d500)) will work too – TheMaster Commented Apr 27, 2018 at 0:31
• 1 Just a warning, this trim function can corrupt some of your data. Example: A cell value of 8055-1 turns into January 1st 8055 (probably due to the toString() method). It works in most cases though. – Kurt Leadley Commented Mar 14, 2019 at 16:25

1 Answer 1

The CLEAN method of VBA removes the characters of 0-31, 127, 129, 141, 143, 144, 157. You want to achieve this using Google Apps Script? If my understanding is correct, how about this sample script? Because I was interested in this method and want to study about this, I created this.

Sample script :

function cleanForGAS(str) {
  if (typeof str == "string") {
    var escaped = escape(str.trim());
    for (var i = 0; i <= 31; i++) {
      var s = i.toString(16);
      var re = new RegExp("%" + (s.length == 1 ? "0" + s : s).toUpperCase(), "g");
      escaped = escaped.replace(re, "");
    }
    var remove = ["%7F", "%81", "%8D", "%8F", "%90", "%9D"];
    remove.forEach(function(e) {
      var re = new RegExp(e, "g");
      escaped = escaped.replace(re, "");    
    });
    return unescape(escaped).trim();
  } else {
    return str;
  }
}

Usage :

When you use this, please modify your script.

From :

rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn].toString().trim().replace(/\s(?=\s)/g,'');

To :

rangeValues[cellRow][cellColumn] = cleanForGAS(rangeValues[cellRow][cellColumn].toString());

Reference :

• WorksheetFunction.Clean Method (Excel)

If I misunderstand your question, I'm sorry.