google-apps-scriptgoogle-sheetsfind-replace

Why this find multiple and replace multiple script not working


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);
}

enter image description here

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.


Solution

  • 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;
      }
    }