Why this find multiple and replace multiple script not working

Question

I am not able to understand why this below script not working on google sheets,

function Find_Multiple_Replace_Multiple(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2,1,lastRow,1);
  var to_replace = sheet.getRange(2,5,5,1);
  var replace_with = sheet.getRange(9,5,1,1);
  var data  = range.getValues();
 
  
    var oldValue="";
    var newValue="";
    var cellsChanged = 0;
 
    for (var r=0; r<data.length; r++) {
      for (var i=0; i<data[r].length; i++) {
        oldValue = data[r][i];
        newValue = data[r][i].toString().replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[r][i] = newValue;
        }
      }
    }
    range.setValues(data);
}

As from the image it can be seen that from E2:E6 needs to be replaced by the cell value E9 in the column range from A2:A16, earlier i used this same script it was working, don't know why its not and how do i make it to work for multiple find and multiple replace. Any help is greatly appreciated.

Answer 1

I found a similar question and edited the script to match your request, I tested it and it works. I’m sure there is a better way to do it but you can try this code.

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var lastRow = sheet.getLastRow();
  var nameRange = sheet.getRange(2,1,lastRow,1);
  var citiesRange = sheet.getRange(2,2,lastRow,1);
  var statesRange = sheet.getRange(2,3,lastRow,1);

  var nameValues = nameRange.getValues();
  var nameReplace = sheet.getRange('E9').getValue();
  
  //Replacing names
  if (sheet.getRange('E2').getValue() == ""){}
    else{replaceInSheet(nameValues, sheet.getRange('E2').getValue(), nameReplace);}

  if (sheet.getRange('E3').getValue() == ""){}
    else{replaceInSheet(nameValues, sheet.getRange('E3').getValue(), nameReplace);}

  if (sheet.getRange('E4').getValue() == ""){}
    else{replaceInSheet(nameValues, sheet.getRange('E4').getValue(), nameReplace);}

  if (sheet.getRange('E5').getValue() == ""){}
    else{replaceInSheet(nameValues, sheet.getRange('E5').getValue(), nameReplace);}

  if (sheet.getRange('E6').getValue() == ""){}
    else{replaceInSheet(nameValues, sheet.getRange('E6').getValue(), nameReplace);}

  var citiesValues = citiesRange.getValues();
  var citiesReplace = sheet.getRange('F9').getValue();

  //Replacing cities
  if (sheet.getRange('F2').getValue() == ""){}
    else{replaceInSheet(citiesValues, sheet.getRange('F2').getValue(), citiesReplace);}

  if (sheet.getRange('F3').getValue() == ""){}
    else{replaceInSheet(citiesValues, sheet.getRange('F3').getValue(), citiesReplace);}

  if (sheet.getRange('F4').getValue() == ""){}
    else{replaceInSheet(citiesValues, sheet.getRange('F4').getValue(), citiesReplace);}

  if (sheet.getRange('F5').getValue() == ""){}
    else{replaceInSheet(citiesValues, sheet.getRange('F5').getValue(), citiesReplace);}

  if (sheet.getRange('F6').getValue() == ""){}
    else{replaceInSheet(citiesValues, sheet.getRange('F6').getValue(), citiesReplace);}

  var statesValues = sheet.getRange(2,3,lastRow,1).getValues();
  var statesReplace = sheet.getRange('G9').getValue();

  //Replacing states
  if (sheet.getRange('G2').getValue() == ""){}
    else{replaceInSheet(statesValues, sheet.getRange('G2').getValue(), statesReplace);}

  if (sheet.getRange('G3').getValue() == ""){}
    else{replaceInSheet(statesValues, sheet.getRange('G3').getValue(), statesReplace);}

  if (sheet.getRange('G4').getValue() == ""){}
    else{replaceInSheet(statesValues, sheet.getRange('G4').getValue(), statesReplace);}

  if (sheet.getRange('G5').getValue() == ""){}
    else{replaceInSheet(statesValues, sheet.getRange('G5').getValue(), statesReplace);}

  if (sheet.getRange('G6').getValue() == ""){}
    else{replaceInSheet(statesValues, sheet.getRange('G6').getValue(), statesReplace);}

  // Write all updated values to the range, at once
  nameRange.setValues(nameValues);
  citiesRange.setValues(citiesValues);
  statesRange.setValues(statesValues);
}

function replaceInSheet(values, to_replace, replace_with) {
  //loop over the rows in the array
  for(var row in values){
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value) {
      return original_value.toString().replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
}