Google Spreadsheet Script to trim and clean data

Question:

( by Darren Tay )

A common problem when many people are contributing to a spreadsheet is dirty data entry, e.g. leading/trailing whitespace, CR / LF.

For some purposes, there are certain characters that are not accepted.

Is there a way to in-place TRIM and CLEAN?

The usual way I know is to create a new column and do =TRIM(otherColumn), and then you might have to convert from formula to value, then delete the source column?

I would also like to have Conditional Formatting to highlight dirty data. 

Presently the available types of Rules are not able to detect cells that need to be trimmed, unprintable chars etc.

Thanks very much

Solution:

Have a look at the following animated screenshot:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Trim", functionName: "trim"});
  spreadsheet.addMenu("Script", menuEntries);
};

function trim() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");
  var r = s.getRange("A1:E5");
  // Change the range as per your requirement
  // Change the range "A:A" to only trim the values in Column A.
  var v = r.getValues();
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++)
      v[i][j]=v[i][j].toString().trim();
  r.setValues(v);
};

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet.

After inserting the above code in your spreadsheet, you can refresh your spreadsheet and then you would be able to see the custom menu "Script" on the menu bar. And then in menu "Script" click on the sub menu "Trim", then you will notice all the values getting trimmed.

I have marked the range with green color, so that you can edit as per your requirement. And if you want to trim the values on whole sheet then,
Instead of line:
var r = s.getRange("A1:E5");
Change it to:
var r = s.getDataRange();


And If you are not much familiar with scripts then check out the following link:http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive