bulk find and replace in google docs using sheet and app script

Question

Thank you in advance.

I am trying to multiple finds and replace google docs with a reference google sheet

Find            |  Replace  
----------------------------
bill number one | Bill No. 1
Bill number one | Bill No. 1
Bill number 1   | Bill No. 1
Bill Number One | Bill No. 1
Bill Number 1   | Bill No. 1

function replMyText(){
  var ss=SpreadsheetApp.openById('1-WblrS95VqsM5eRFkWGIHrOm_wGIPL3QnPyxN_j5cOo');
  var sh=ss.getSheetByName('find and replace');
  var doc=DocumentApp.getActiveDocument();
  var docrange=doc.getBody()
  var rgtxt=doc.getBody();//text to replace
  var rgrep=sh.getRange('A2:B103');//replacement table
  var txtA=rgtxt.getText();
  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.setText(txtA);
}

I did not get the output.

Tanaike · Accepted Answer · 2021-03-25 01:34:24Z

2

I believe your goal as follows.

You want to replace the text in Google Document using the values retrieved from Google Spreadsheet.
The columns "A" and "B" in Google Spreadsheet are the search text and the replace text, respectively.

In this case, I thought that replaceText() might be able to be used. When your script is modified, it becomes as follows.

Modified script:

function replMyText() {
  var ss = SpreadsheetApp.openById('1-WblrS95VqsM5eRFkWGIHrOm_wGIPL3QnPyxN_j5cOo');
  var sh = ss.getSheetByName('find and replace');
  var doc = DocumentApp.getActiveDocument();
  var rgtxt = doc.getBody();
  var rgrep = sh.getRange('A2:B103');
  var repA = rgrep.getValues().filter(r => r.every(c => c.toString()));
  repA.forEach(e => rgtxt.replaceText(...e));
}

Reference:

replaceText(searchPattern, replacement)

answered Mar 25, 2021 at 1:34

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

4 Comments

Sachin Pathare Over a year ago

can tell me how can add exact match ex. last names like "Williams." at the end of a sentence to "WilliaMs." I want ms to replace MS.

Tanaike Over a year ago

@Sachin Pathare Thank you for replying. I'm glad your issue was resolved. About your new question, I would like to support you. But unfortunately, I cannot understand about what you want to do. This is due to my poor English skill. I apologize for this. So can I ask you about the detail of your new question? By this, I would like to try to understand your new question.

Sachin Pathare Over a year ago

stackoverflow.com/q/66800898/7419737 hope you understand. ありがとうございました

Tanaike Over a year ago

@Sachin Pathare Thank you for replying. Now I noticed that your new question had already been resolved. I'm glad for it.

Charles Lehnen · Accepted Answer · 2022-12-06 10:08:17Z

1

If you would like to do this without a script, you can do it right in Google Sheets with the powerful ARRAYFORMULA() function

answered Dec 6, 2022 at 10:08

Charles Lehnen

1112 silver badges12 bronze badges

2 Comments

chrslg Over a year ago

They don't want to do it without a script. That is specified explicitly in the question. The question was how to do it with a script.

Charles Lehnen Over a year ago

Just figured wouldn’t do any harm to add this alternative to this thread with the disclaimer

Collectives™ on Stack Overflow

bulk find and replace in google docs using sheet and app script

2 Answers 2

Modified script:

Reference:

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Modified script:

Reference:

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related