CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
cphr
cphr

Reputation: 13

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

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:

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:

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.

Upvotes: 1

Views: 720

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15308

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()

Upvotes: 1

Related Questions