CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Christoph
Christoph

Reputation: 19

Find and replace values from column

I have a document consisting of five different worksheets. These sheets contain tables of five columns and in excess of 400 rows each. I am trying to replace specific text strings found in these tables with other text strings. To this end, I've created another sheet with another table ($replacement-table), containing the strings I want to replace in one column and the strings I want to replace them with in another column. The number of strings to be replaced in excess of 500, and I'm not certain that they all crop up somewhere in the other sheets.

While I could do it manually, I'd rather come up with a way to automate it. Therefore, I'm looking for a script that looks up whether the value in A2 of $replacement-table shows up anywhere else in the document, and if so, replaces it with the value found in B2 of $replacement-table. The same goes for A3 and B3, A4 and B4, and so on and so forth.

Sadly, I don't even have a starting point, as I'm not experienced in either the syntax or the functions required to tell Google Sheets what I want it to do. Any ideas (or even obvious/plug-and-play solutions?) on how to achieve my goal of replacing a wide selection of strings found in five different tables with the corresponding strings arranged in a certain table?

Upvotes: 0

Views: 3312

Answers (1)

Cooper
Cooper

Reputation: 64032

Here's a simple example that might help.

function replMyText(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('replacements');
  var rgtxt=sh.getRange('A1:A7');//text to replace
  var rgrep=sh.getRange('D1:E5');//replacement table
  var txtA=rgtxt.getValues();
  var repA=rgrep.getValues();
  for(var i=0;i<txtA.length;i++){
    for(var j=0;j<repA.length;j++){
      if(txtA[i][0]==repA[j][0]){
        txtA[i][0]=repA[j][1];
      }
    }
  }
  rgtxt.setValues(txtA);
}

Here's an image of my test sheet:

enter image description here

Upvotes: 1

Related Questions