How to fix or adapt this Find and Replace in Google Apps Script

Question

I have a sheet which I regularly have to paste a large amount of data into and need to do ten iterations of "Find and Replace" to fix some of the data. The data I paste in comes from a database which I will not be given direct access to and for some reason whenever they export it many common special characters such as é or ' get scrambled.

I stole and adapted this excellent solution to make a script which replaces substrings with other strings wherever found in the data. The script searches the active sheet for the specific strings which are messed up and replaces them with the correct characters.

The script has worked great, except it has for some unknown reason occasionally replaced the single character - with a '. I could work with this if it was consistent but for some reason it only occurs intermittently. I suspect this has something to do with the character | essentially functioning as an OR in lines 8 or 9 below, but I cannot figure out why it would only be doing it from time to time.

A perfect solution would be to stop this intermittent replacement occurring. The next best thing would be adapting the script to replace the occurrences of ' back into a - in a single column G after doing what it's currently doing. What I thought was more attainable for my skill level (zero), was making a copy of the script which would only replace ' with - in column G

My understanding is that the getValues() in line 5 basically selects the entire sheet, I've tried replacing this with various other get thingies to just grab G5:G, but I can't quite get it to work. I have absolutely no coding experience unless you count some pretty artful use of Sheets' built in functions. Unfortunately, I don't understand the error messages I'm getting when I debug.

Current script :

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  //  get the current data range values as an array
  //  Fewer calls to access the sheet -> lower overhead 
  var values = sheet.getDataRange().getValues();  

  // Replace Subject Names
  replaceInSheet(values, /\&#39;|-/g, "'");
  replaceInSheet(values, /\‚Äô|-/g, "'");
  replaceInSheet(values, /\√°|-/g, "á");
  replaceInSheet(values, /\√©|-/g, "é");
  replaceInSheet(values, /\√≠|-/g, "í");
  replaceInSheet(values, /\√≥|-/g, "ó");
  replaceInSheet(values, /\√Å|-/g, "Á");
  replaceInSheet(values, /\√Ø|-/g, "ï");
  replaceInSheet(values, /\ƒó|-/g, "ė");
  replaceInSheet(values, /\≈´|-/g, "ū");

  // Write all updated values to the sheet, at once
  sheet.getDataRange().setValues(values);
}

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

I made various attempts at modifying it to only replace getValues with either a named range PO_EligibilityGG or just the G Column, but for brevity I'll paste just the version which I think came the closest to working:

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  //  get the current data range values as an array
  //  Fewer calls to access the sheet -> lower overhead 
  var values = sheet.getRange('PO_EligibilityGG');  

  // Replace Subject Names
  replaceInSheet(values, /\'|-/g, "-");


  // Write all updated values to the sheet, at once
  sheet.getRange('PO_EligibilityGG').setValues(values);
    }
...

Error Message:

Exception: The parameters (SpreadsheetApp.Range) don't match the method signature for SpreadsheetApp.Range.setValues. runReplaceInSheet @ Code.gs:11

I have to run this replacement 2 or 3 times a day at the moment on 3 different sheets and eliminating this problem would just take app the pain out of this.

score 1 · Answer 1 · answered Feb 16 '21 at 00:16

Based on what I can see in your case, it seems you don't get the values in Column G using getRange correctly.

Try this code in getting the values in Column G using getRange:

function valuesOfColumnG(){
  var ss = SpreadsheetApp.getActiveSheet();
  var len = ss.getLastRow();
  for(var i = 1; i < len +1; i++){
    var valuesOfG = ss.getRange("G"+i).getValue();
    console.log('Values of G ' + valuesOfG);
  }
}