Replace text in Google Spreadsheets with Apps Script — II

Photo by Dorothea OLDANI on Unsplash

Following on from this earlier post where Kevin asked:

Is there a way to do it on another Spreadsheet?

I mean, instead of "getActiveSpreadsheet" do it in a different Spreadsheet file using its ID or url.

Say on Sheet1, in cell A1, you have the URL or ID of a Google Sheet that you want to run the replace-text script on.

You’d start with

// The Google Sheet that has the script
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ss1 = ss.getSheetByName(‘Sheet1’);
var targetUrl = ss1.getRange(‘A1’).getValue();

Now you have the URL of the Google Sheet you want to run the replace-text script on.

// Now get the target Google Sheet
var targetSS = SpreadsheetApp.openByUrl(targetUrl);

Or if you want to use the Google Sheet ID instead of the URL:

var targetId = ss1.getRange('A1').getValue();
var targetSS = SpreadsheetApp.openById(targetID);

Now you need to get to the sheet on the target Google Sheet that has the template where you want to replace text:

var targetSheet = targetSheet.getSheetByName('My Template');

And the rest follows as earlier.

All together now

function replaceOnTargetSheet() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var ss1 = ss.getSheetByName('Sheet1');
   var targetUrl = ss1.getRange('A1').getValue();
   var targetSS = SpreadsheetApp.openByUrl(targetUrl);
   var targetSheet = targetSheet.getSheetByName('My Template');
   var textFinder = targetSheet.createTextFinder(‘Tom’);
   textFinder.replaceAllWith(‘Scratchy’);
}