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.

score 2 · Accepted Answer · answered Mar 25 '21 at 01:34

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 '21 at 01:34

Tanaike

181,128
11
97
165

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. – Sachin Pathare Mar 25 '21 at 11:22
@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. – Tanaike Mar 25 '21 at 13:00
https://stackoverflow.com/q/66800898/7419737 hope you understand. ありがとうございました – Sachin Pathare Mar 25 '21 at 13:53
@Sachin Pathare Thank you for replying. Now I noticed that your new question had already been resolved. I'm glad for it. – Tanaike Mar 26 '21 at 01:26

score 1 · Answer 2 · answered Dec 06 '22 at 10:08

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 06 '22 at 10:08

talker90

79
8

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. – chrslg Dec 11 '22 at 09:55
Just figured wouldn’t do any harm to add this alternative to this thread with the disclaimer – talker90 Dec 12 '22 at 13:32

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

2 Answers2

Modified script:

Reference:

Linked