CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Marco Parasiliti
Marco Parasiliti

Reputation: 23

How to automatically replace a specific word with different one, when it is typed in google sheets

So for a joke in my office, I am trying to write a script that will change my friend's name to something else whenever he types it into our shared google sheets spreadsheet. I got as far as this code that changes everything typed into something else but I would like it to be specifically his name that triggers the script.

Could someone quickly finish it for me? (below is the attempted code)

Much appreciated.

function onEdit(e)
{
  var sh=e.source.getActiveSheet();
  var col=e.range.getColumn();
  var row=e.range.getRow();
  if(sh.getName()=='Sheet1' )
  {
    sh.getRange(row,col).setValue('horse');
  }
}

Upvotes: 1

Views: 117

Answers (1)

Tanaike
Tanaike

Reputation: 201473

I believe your goal as follows.

  • You want to replace the edited cell includes the specific value with the other specific value.
  • For example, when the search value and the replace value are foo and bar, respectively, when sample foo sample is put to a cell, you want to achieve the cell value to sample bar sample.
  • You want to achieve this using OnEdit trigger.

In this case, I would like to propose to use TextFinder. I thought that when TextFinder is used, the script might be simpler. The sample script is as follows.

Sample script:

function onEdit(e) {
  var fromValue = "foo";  // Please set the search value.
  var toValue = "bar";  // Please set the replace value.

  var range = e.range;
  var textFinder = range.createTextFinder(fromValue);
  if (range.getSheet().getSheetName() != "Sheet1" || !textFinder.findNext()) return;
  textFinder.replaceWith(toValue);
}
  • When you use this script, please put a value including foo to a cell. By this, the script is run.

References:

Upvotes: 1

Related Questions