How to replace text in a link across multiple google sheets in the same workbook

Question

I am trying to replace all occurrences of specific text with another text using google script. I want to use a script so that I don't have to manually type each time I want to replace.

This is what I have tried. The script doesn't throw any errors so I'm not sure why it's not working. I want the script to replace the text example inside the links that look like this example.com/this-is-a-link to example2.com/this-is-a-link. I am replacing the text across multiple sheets.

Here is what I have tried.

function rePlace() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("a1:a10000");
  var data  = range.getValues();

  for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
      data[row][col] = (data[row][col]).toString().replace('/.*example.*/', 'example2');
    }
  }
  range.setValues(data);
}

Thanks.

Tanaike · Accepted Answer · 2020-10-21 08:06:19Z

1

In your script, replace('/.*example.*/', 'example2') is required to be modified as follows.

From

data[row][col] = (data[row][col]).toString().replace('/.*example.*/', 'example2');

To:

data[row][col] = (data[row][col]).toString().replace('example', 'example2');

or

data[row][col] = (data[row][col]).toString().replace(/example.com\//, 'example2.com/');

By this, example.com/this-is-a-link is modified to example2.com/this-is-a-link.

In your situation, as other method, how about using TextFinder? When your script is modified, it becomes as follows. When TextFinder is used, I think that the process cost will be lower than that of current script.

Modified script:

function rePlace() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("a1:a10000");
  range.createTextFinder("example").replaceAllWith("example2");  // Added
}

or

function rePlace() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("a1:a10000");
  range.createTextFinder("example.com/").useRegularExpression(true).replaceAllWith("example2.com/");  // Added
}

References:

Added 1:

When you want to use above script for several sheets in the active Google Spreadsheet, how about the following script?

Sample script:

In this script, the range of a1:a10000 in all sheets is used.

SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .forEach(sheet => sheet
    .getRange("a1:a10000")
    .createTextFinder("example.com/")
    .useRegularExpression(true)
    .replaceAllWith("example2.com/")
  );

When you want to use the specific sheets, you can also use the following script.

var sheetNames = ["Sheet1","Sheet3",,,];  // Please set the sheet names.
SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .forEach(sheet => {
    if (sheetNames.includes(sheet.getSheetName())) {
      sheet
        .getRange("a1:a10000")
        .createTextFinder("example.com/")
        .useRegularExpression(true)
        .replaceAllWith("example2.com/")
    }
  });

Added 2:

About your 2nd question of Is there a way to replace all at once without the script visiting one sheet after another to make replacements? Because I think that's why it's delaying. I have 50 sheets. Perfect solution but if it would mass replace I think it would be even better? from your comment, in this case, I would like to propose to use Sheets API. When Sheets API is used, I think that the process cost will be able to be reduced.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const requests = ss.getSheets().map(s => ({
  findReplace:{
    find: "example.com/",
    searchByRegex: true,
    replacement: "example2.com/",
    range: {sheetId: s.getSheetId(), startRowIndex: 0, startColumnIndex: 0, endColumnIndex: 1}
  }
}));
Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());

Note:

If you will check all cells in all sheets in a Google Spreadsheet, I think that you can also use the following script.

  SpreadsheetApp.getActiveSpreadsheet().createTextFinder("example.com/").useRegularExpression(true).replaceAllWith("example2.com/");

edited Oct 21, 2020 at 8:06

answered Oct 21, 2020 at 6:40

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

4 Comments

mary ongubo Over a year ago

Yes, TextFinder is what I was looking for. How can I replace across multiple sheets and not just the current active sheet?

Tanaike Over a year ago

@mary ongubo Thank you for replying. I apologize for the inconvenience. I updated my answer. Could you please confirm it?

mary ongubo Over a year ago

Is there a way to replace all at once without the script visiting one sheet after another to make replacements? Because I think that's why it's delaying. I have 50 sheets. Perfect solution but if it would mass replace I think it would be even better?

Tanaike Over a year ago

@mary ongubo Thank you for replying. I'm glad your issue was resolved. About your 2nd question, I added one more sample script. Could you please confirm it?

Collectives™ on Stack Overflow

How to replace text in a link across multiple google sheets in the same workbook

1 Answer 1

From

To:

Modified script:

References:

Added 1:

Sample script:

Added 2:

Sample script:

Note:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From

To:

Modified script:

References:

Added 1:

Sample script:

Added 2:

Sample script:

Note:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related