Replace values with Google Sheets AppScript

Question

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.-

Answer 1

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,''));
  }
}

• Regex

• Learn More

• Bitwise Not(~)

try this:

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')
    sh.getRange("A5:A" + sh.getLastRow()).getValues().flat().forEach((el,i) => {
      sh.getRange(i + 5,1).setValue(el.replace(/[-_,:;\.]+/g,''));
    })
  }
}