Using ReplaceWith to find & replace text in a google sheet

Question

This may be a dumb question but I was unable to find an answer on stackoverflow, youtube, or the developers (google) site either for this issue.

I'm trying to use createTextFinder to find a certain word, and replace it with a new word. Ideally I'd like to replace the 2nd instance of the word instead of the first, however if that isn't possible that's OK. I'm also trying to ensure that my function can find these words dynamically instead of resting on defined ranges such as A1:D2 as an example.

So for our example below trying to change the 2nd instance of Apple to Pie.

What I find really bizarre, is that replaceWith doesn't seem to work, but replaceAllWith did work.

Problem:

Unable to have replaceWith work with the createTextFinder method. Receiving the error

"Exception: Service error: Spreadsheets"

Current Sheet:

Expected Outcome:

Troubleshooting I've tried:

Attempted to use startFrom and use a range prior to the 2nd instance of Apple but this didn't seem to work
Attempted to make another textfinder in the same function, however I don't think you're allowed to? I did this in order to do my prior attempt
changed replaceWith to replaceAllWith which worked, but then tried to have it find "Pie" in the same function and change the first instance back to "Apple" but this didn't work
Tried to use the findNext() feature as well, but this unfortunately did not work, and I'm unsure how to use this with the replaceWith method.

Common errors occuring during these attempts is the program stating I do not have a proper function or that the parameters don't match the method signature

Code:

function findText() {
  const workSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');//I have a few tabs and would like to call to them directly
  const tf = workSheet.createTextFinder('Apple');
  tf.matchEntireCell(true).matchCase(false);//finds text "Apple" exactly

tf.replaceWith('Pie');

}//end of function findText

Resources:

Google Developers on replaceWith

Exception: Service error: Spreadsheets. Could you try in a new spreadsheet? Try tf.findNext().replaceWith('Pie'); — TheMaster
– TheMaster, Commented Oct 27, 2021 at 21:27
Yeah I've tried this function in 2 different spreadsheets and get this error, not sure what I'm doing wrong — User1938985
– User1938985, Commented Oct 28, 2021 at 12:39
Sorry was just about to comment there, when I attempt to write that code it doesn't appear it will work, and when I ran the function it said "tf.findNext is not a function" — User1938985
– User1938985, Commented Oct 28, 2021 at 12:48

Cooper · Accepted Answer · 2021-10-27 22:15:51Z

4

function replacesecondinstanceofword( word = "Apple",replacement = "Peach") {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const tf = sh.createTextFinder(word).matchEntireCell(true).findAll();
  tf.forEach((f,i) => {
    if(i == 1) {
      sh.getRange(f.getRow(),f.getColumn()).setValue(replacement)
    }
  });

}

Learn More

edited Oct 27, 2021 at 22:15

answered Oct 27, 2021 at 21:30

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

User1938985 Over a year ago

thank you this worked! Just out of curiosity, what does the f, in the tf.forEach((f,i) do?

Cooper Over a year ago

The createTextFinder method returns an array of objects called class TextFinder which has properties and methods which in this case are utilized to define the precise cell that the found text was located in.

Cooper Over a year ago

Ever since the development of EcmaScript 2015 the use of array methods like map and forEach have had a profound effect upon the Javascript Language. In this particular invocation one could easily claim that a for loop would be better because it could stop after the second iteration but the conciseness and efficiency of writing the code in this manner often overrules any other concerns where the time loss is not considered to be a great concern.

Yuri Khristich · Accepted Answer · 2021-10-27 23:59:55Z

1

Based on Cooper's solution:

function replace_second(word = "Apple", replacement = "Peach") {
  try {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1')
    .createTextFinder(word).matchEntireCell(true).findAll()[1]
    .setValue(replacement);
  } catch(e) {}
}

answered Oct 27, 2021 at 23:59

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

2 Comments

User1938985 Over a year ago

This also worked! Being very honest I'm not sure what you did but I'll have to research this try method and catch

Yuri Khristich Over a year ago

Unlike Cooper's solution my code don't use any loops. Because there is no need loops if you want to get a second element of array. It just takes the second element array[1] from the array (if it exists, this why it's need to use try/catch). I'm sure it's a more efficient way.

TheMaster · Accepted Answer · 2021-10-28 13:58:13Z

1

You need to call .findNext() at least once to replace the first matched cell.

function findText() {
  const workSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    'Sheet1'
  );
  const tf = workSheet.createTextFinder('Apple');
  tf.matchEntireCell(true).matchCase(true); //finds text "Apple" exactly
  let match = -1;
  while (++match < 2) tf.findNext();//for second match
  tf.replaceWith('Pie');
}

edited Oct 28, 2021 at 13:58

answered Oct 28, 2021 at 13:11

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

3 Comments

User1938985 Over a year ago

Thanks this did the trick! Seems weird you can't join the findNext() method with replaceWith() but I like what you did

TheMaster Over a year ago

@User1938985 Yeah. .findNext() returns range and not the textfinder. That's why my comment didn't work.

User1938985 Over a year ago

oh ok thank you for clarifying for me

Collectives™ on Stack Overflow

Using ReplaceWith to find & replace text in a google sheet

3 Answers 3

3 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related