Replace values with Google Sheets AppScript-CodePudding

Good morning:

This time I have a spreadsheet in Google Sheets, in which in column "A", users will be entering their identity document. Ideally, numerical or alphanumeric values should be entered in the case of foreign documents. But, many times these users paste the data from other forms, so they may come with special characters ("." ;","; "-"; "_"). The idea is that when the user enters a data, a macro is run, which removes the format of that entire column, cleans it and eliminates those special characters, only leaving numbers and letters to have.

The easy step is to do it with a formula in a contiguous column, the formula is REGEXREPLACE(), but already the document becomes heavier and such. That's why I wanted to use the macro option, since it would be acting on the same value of the cell and reducing it in many cases.

I still have no knowledge of Appscript and have been searching on Youtube, but I did not find anything.

Could you help me create this macro?

I thank you very much for being so!!!

Miguel.-

CodePudding user response：

Here is an example of an onEdit(e) function that will remove unwanted characters. The String.replace() is using a regex expression with the special character dot escaped \.. The same would need to be done for any other special characters.

The onEdit is limited to column A of Sheet1.

function onEdit(e) {
  try {
    if( e.range.getSheet().getName() !== "Sheet1" ) return;
    if( e.range.getColumn() !== 1 ) return;
    let value = e.value;
    if( !value ) { // copy/paste
      value = e.range.getValue();
    }
    value = value.replace(/[-_,;\.]/g,'');
    e.range.setValue(value);
  }
  catch(err) {
    SpreadsheetApp.getActiveSpreadsheet().toast("onEdit() " err);
  }
}

Reference

CodePudding user response：

Remove unwanted whitespace:

function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  const names = ["Sheet1","Sheet2"];//You can changes these sheet names and add as many as you wish
  const idx = names.indexOf(sh.getName());
  if(~idx && e.range.columnStart == 1 && e.value) {
    //e.source.toast('Gate1')
    e.range.setValue(e.value.replace(/[-_,:;\.] /g,''))
  }
}