Find and Replace in Multiple Google Docs Using Sheet and App Script

Question

Rerefence to this post "Bulk Find and Replace", and my previous post "Replace Text in Multiple Document", I try to replace Text in multiple documents, from the list "Document ID" and "Text To Replace" on my spreadsheet.

This is the Word List To Be Replace :

And this is the documents ID list, where I want to Find and Replace Text.

I try to combine two script from Reference Post :

 function bulkreplaceText() {

 // DATA LIST DOC ID DI SPREADSHEET

 const spreadsheet     = SpreadsheetApp.getActiveSpreadsheet();
 const sheetIDList     = spreadsheet.getSheetByName('DOC TO REPLACE');
 const colId           = 3;
 const firstRowID      = 2;
 const lastRowId       = sheetIDList.getLastRow();
 const n               = lastRowId - firstRowID;

 // TEXT TO REPLACE DATA  AT  SPREADSHEET

 const sheetTextToRplc = spreadsheet.getSheetByName('LIST WORD TO REPLACE');
 const colExstText     = 2 ;
 const firstRowRplcTxt = 2 ;
 const lastRowRplcTxt  = sheetTextToRplc.getLastRow();
 const nRow            = lastRowRplcTxt - firstRowRplcTxt + 1
 const rangeRplc       = 
 sheetTextToRplc.getRange(firstRowRplcTxt,colExstText,nRow,2).getA1Notation() ;


 let   startRow    = firstRowID     // First Row DOC ID 
 var   values      = sheetIDList.getRange(startRow, colId, n).getDisplayValues();

       values.forEach(([docId]) => {
           if (!docId) return;

 var doc           = DocumentApp.openById(docId);
 var rgtxt         = doc.getBody();
 var rgrep         = sheetTextToRplc.getRange(rangeRplc);
 var repA          = rgrep.getValues().filter(r => r.every(c => c.toString()));

 repA.forEach(e => rgtxt.replaceText(...e));

         
    });
 }

I am greatful for any help.

Although I'm not sure whether I could correctly understand your expected result, I proposed a modified script as an answer. Please confirm it. If I misunderstood your expected result and that was not useful, I apologize. — Tanaike
– Tanaike, Commented Jul 2, 2023 at 11:33

Tanaike · Accepted Answer · 2023-07-02 11:33:16Z

2

Modification points:

From your script, I thought that const n = lastRowId - firstRowID; might be const n = lastRowId - firstRowID + 1;.
I think that repA can be moved out of forEach because repA is not changed.

When these points are reflected in your script, how about the following modification?

Modified script:

function bulkreplaceText() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetIDList = spreadsheet.getSheetByName('DOC TO REPLACE');
  const colId = 3;
  const firstRowID = 2;
  const lastRowId = sheetIDList.getLastRow();
  const n = lastRowId - firstRowID + 1;
  const sheetTextToRplc = spreadsheet.getSheetByName('LIST WORD TO REPLACE');
  const colExstText = 2;
  const firstRowRplcTxt = 2;
  const lastRowRplcTxt = sheetTextToRplc.getLastRow();
  const nRow = lastRowRplcTxt - firstRowRplcTxt + 1
  const rangeRplc = sheetTextToRplc.getRange(firstRowRplcTxt, colExstText, nRow, 2).getA1Notation();
  let startRow = firstRowID
  var values = sheetIDList.getRange(startRow, colId, n).getDisplayValues();
  var rgrep = sheetTextToRplc.getRange(rangeRplc);
  var repA = rgrep.getValues().filter(r => r.every(c => c.toString()));
  values.forEach(([docId]) => {
    if (!docId) return;
    var doc = DocumentApp.openById(docId);
    var rgtxt = doc.getBody();
    repA.forEach(e => rgtxt.replaceText(...e));
  });
}

answered Jul 2, 2023 at 11:33

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

1 Comment

PSAB Plalangan Over a year ago

Thanks @Tanaike . Your modification script work perfect. Always

Collectives™ on Stack Overflow

Find and Replace in Multiple Google Docs Using Sheet and App Script

1 Answer 1

Modification points:

Modified script:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related