How to replace text in Google Spreadsheet using App Scripts?

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?

Solution 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 :)

Solution 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.

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