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

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

Resources:

Google Developers on replaceWith

CodePudding user response：

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

CodePudding user response：

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) {}
}