Trim And Clean Google Script

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

Solution 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 :

functioncleanForGAS(str) {
  if (typeof str == "string") {
    var escaped = escape(str.trim());
    for (var i = 0; i <= 31; i++) {
      var s = i.toString(16);
      var re = newRegExp("%" + (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 = newRegExp(e, "g");
      escaped = escaped.replace(re, "");    
    });
    returnunescape(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.