Find text and replace entire cell value in Google Sheets with script-CodePudding

I am working with an external source as raw data which provides column headers with country codes some other text (e.g. sessions US, active users US). I am looking for a script I can make a button for that replaces column headers as such:

sessions US --> Is entirely replaced with --> United States of America
active users --> Is entirely replaced with --> United States of America

In short it strips everything else and replaces US with United States of America as the new cell value. So the script looks for 'part of' the cell value (US) and replaces the entire cell value with what I've configured. The script I got working is the following:

  SpreadsheetApp.getActive()
   .createTextFinder("US")
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)
   .ignoreDiacritics(false)
   .replaceAllWith("United States of America");

  SpreadsheetApp.getActive()
   .createTextFinder("IT")
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)
   .ignoreDiacritics(false)
   .replaceAllWith("Italy");
}

However, this script replaces 'US' with 'United States of America'. This means the following happens:

sessions US --> Is partly replaced with --> sessions United States of America
active users --> Is partly replaced with --> active users United States of America

Also good to note is that the script should work for multiple country codes at the same time (e.g. CA --> Canada, IT --> Italy). I got that working as well by simply copying the textfinder section over

I am simply not good enough with scripting to get this figured out as there is no use case I can find and reverse engineer myself. Hopefully, somebody here can help me out.

CodePudding user response：

Try this way

function myFunction() {
  SpreadsheetApp.getActive().createTextFinder("US").findNext().setValue('United States of America')
}

if you have multiple occurences, you can use

function myFunction() {
  SpreadsheetApp.getActive()
    .createTextFinder("US")
    .matchEntireCell(false)
    .matchCase(true)
    .matchFormulaText(false)
    .findAll()
    .forEach(function (range) {
      range.setValue("United States of America");
    });
}

findAll()