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.

This ` var range = sheet.getRange(2,1,lastRow,1);` should be lastRow-1 — Cooper, Apr 06 '22 at 18:55
I thought i could make it dynamic since the range values from `E2:G6` are formulated — , Apr 06 '22 at 19:02
Rather than thinking that you can do something I'd recommend that you read the javascript and google apps script documentation to make sure you can do it. — Cooper, Apr 06 '22 at 19:03
I have to apologize for my poor English skill. Unfortunately, I couldn't understand your expected goal from your script and your showing image. Can I ask you about the detail of your goal? From your goal, I would like to confirm the modification points. — Tanaike, Apr 07 '22 at 00:34
@Tanaike so the columns `E2:G6` are actually formulated, with most occurrences in each of the columns `A2:C16` with respect to their headers accordingly, now when i enter any value in cells `E9:G9` they will be replaces with those found values in columns `A:C` respectively, actually i have 15 columns and rows shall increase or decrease everyday, so i wanted to replace those multiple values with one script, i have been trying to do it, but not successful, — , Apr 07 '22 at 10:50
Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. — Tanaike, Apr 07 '22 at 12:35
@Tanaike why are you apologizing everytime, there is nothing wrong, may be i am not able to explain, alright let me explain again. if the values in `E2:E6` is found in `A2:A16` then it will be replaced with `E9` same way for the other columns like for if `F2:F6` is found in `B2:B16` then needs to be replaced by `F9` , Note the values in columns `E2:G6` will come by formula where as in cells `E9:G9` shall be manual inputs. Since there will be thousands of rows i tried to make to automatic, with one script. which i am failin — , Apr 07 '22 at 13:42

Lorena Gomez · Accepted Answer · 2022-04-08T01:07:20.940

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

Why this find multiple and replace multiple script not working

1 Answers1