Google Sheets: Find and Replace different text values

1
votes

I'm continually adding new rows to Google Sheets. I need 2 scripts (not formulas!) to find and replace text values in 2 different columns.

Script 1: In Column D, I have multiple rows that contain 1 of 2 values:

  1. Jones
  2. JD-Market

I need ALL rows in Column D to read: JD-Market

Script 2: In Column E, I have multiple rows that contain URLs. I need to find and replace certain text within those URLs with other text.

Sample URL: http://www.sample.com/test/boat-water/2468

I want to replace only "boat-water" with "car-road"

New URL: http://www.sample.com/test/car-road/2468

I can't use formulas for these changes. I need to use scripts.

Thanks for your help! Much appreciated!

1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-spreadsheet-api
Do your columns have headers? Or are they just columns a,b,c,d,e...etc? – Douglas Gaskell

1 Answers

1
votes

Would you please elaborate more about your requirement ?

As my understanding, you want to replace column E url only when parallel column D contain 'JD-Market'.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastrow = ss.getLastRow();
for(var i =1; i<=lastrow; i++){
  //var tempval = sheet.getRange("D"+i).getValue();
  //if(tempval == 'JD-Market'){
    var oldurl = sheet.getRange("E"+i).getValue();
    var newurl = oldurl.replace("boat-water", "car-road");
    sheet.getRange("E"+i).setValue(newurl);
  //}
}