How to replace text in Google Spreadsheet using App Scripts?

Question

I tried to get the value of a range and than remove all points from the cell.

var FILE = SpreadsheetApp.openById("xyz");
var CONTENT = FILE.getSheetByName("Sheet1");
var A1 = CONTENT.getRange("I17").getValue();
A1. replace(".", "");

It gives me that can't find the replace function. Is there any function in Google Apps Script that allows me to replace the string?

Answer 1

If this is an exact copy of your script then you have a space in-between A1. and replace but I assume it is not.

@SergeInsas is right the content needs to be a string for the replace() function to work, so if your trying to replace the . in a decimal number then you can use the toString() method first like below.

var FILE = SpreadsheetApp.openById("xyz");
var CONTENT = FILE.getSheetByName("Sheet1");
var A1 = CONTENT.getRange("I17").getValue();
var A1String = A1.toString().replace(".", "");

Or you can multiply the number to get rid of the decimal but this will depend on how many decimal places you have :)

Answer 2

There is a more powerful, and simpler, method available: TextFinder.

The accepted answer to this question requires an additional step to post the replaced string back to the cell.

The TextFinder method does not need you to write the data back to the cell.

And if you want to search multiple cells, then this method saves you the iterations.

  var FILE = SpreadsheetApp.openById("xyz");
  var CONTENT = FILE.getSheetByName("Sheet1");
  var A1 = CONTENT.getRange("I17");
  A1.createTextFinder(".").replaceAllWith("");

I haven't tested it on a large data set but I suspect this would be quite quick.

---

Edit: I wrote a short tutorial on this.

Answer 3

For some reason, this solution doesn't work for me. Here is my whole code that should replace the '+' symbol with 'nothing'

  // I need to replace more occurrences of different strings, so this is just an example..
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("G5:G7").getValues();

  // this is a loop, to go through multiple cells that may contain the text, that needs to be replaced.
    for (var i = 0 ; i<range.length ; i++) {
        var le = range.length;
        var stri = range[i].toString().replace("+", "");
        Logger.log(stri);
  }  

  var msg = ui.alert("Replaced?");
  return msg;

Answer 4

Hope this help you

function removeAccents() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRange("F3:F");
  var data  = range.getValues();

  for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
      data[row][col] = (data[row][col]).toString().replace(/é/g, 'e');
      data[row][col] = (data[row][col]).toString().replace(/ã/g, 'a');
    }
  }
  range.setValues(data);
};

Answer 5

Sharing a very helpful solution from Bannager Bong on this Google Docs Editor Help Forum thread. Made a slight modification to the function so that it accepts arguments for the find, replace values and then added a range argument so that the function can target a specific region. Even so, this method is extremely slow (my sheets have 5k rows).

function Cleanup12m() {
  var spreadsheet = SpreadsheetApp.getActive();
  //fandr(",", "");
  //fandr("\"","");
  fandr("�","",spreadsheet.getRange('BA:BA')); //uses specific range
};

function fandr(find, repl) {
  var r=SpreadsheetApp.getActiveSheet().getDataRange();
  var rws=r.getNumRows();
  var cls=r.getNumColumns();
  var i,j,a,find,repl;
  //find="abc";
  //repl="xyz";

  for (i=1;i<=rws;i++) {
    for (j=1;j<=cls;j++) {
      a=r.getCell(i, j).getValue();
      if (r.getCell(i,j).getFormula()) {continue;}
      //if (a==find) { r.getCell(i, j).setValue(repl);}
      try {
        a=a.replace(find,repl);
        r.getCell(i, j).setValue(a);
      }
      catch (err) {continue;}
    }
  }
};

//Revised to apply to a selected range
function fandr(find, repl, range) {
  var r= range;//SpreadsheetApp.getActiveSheet().getDataRange();
  var rws=r.getNumRows();
  var cls=r.getNumColumns();
  var i,j,a,find,repl;
  //find="abc";
  //repl="xyz";

  for (i=1;i<=rws;i++) {
    for (j=1;j<=cls;j++) {
      a=r.getCell(i, j).getValue();
      if (r.getCell(i,j).getFormula()) {continue;}
      //if (a==find) { r.getCell(i, j).setValue(repl);}
      try {
        a=a.replace(find,repl);
        r.getCell(i, j).setValue(a);
      }
      catch (err) {continue;}
    }
  }
};