How to replace text in Google Spreadsheet using application scripts?

Question

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

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

This gives me something that the replace function cannot find. Is there a feature in Google Apps Script that allows me to replace a string?

Answer 1

If this is an exact copy of your script, then you have a space between A1. and replace , but I guess that is not the case.

@SergeInsas is right, the content should be a string for the replace() function, so if you try to replace . in decimal value, you can use the toString() method as shown 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 a number to get rid of the decimal, but that will depend on how many decimal places you have :)

Answer 2

For some reason, this solution does not work for me. Here is all my 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 3

Sharing a very useful solution from Bannager Bong in this thread of the Google Docs editor's help forum . Small changes have been made to the function so that it takes arguments to search for, replaces the values, and then adds a range argument so that the function can focus on a specific area. Despite this, this method is very slow (my sheets have 5 thousand lines).

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

Answer 4

thanks for the tips, but you are using a very slow method (I have a 4K line and the script runs 10 minutes). there is no other way to do it faster?

I'm trying to do this with getRange and replace, but the replace () method replaces only the first character, if I have 300 or more "," the replace () method replaces only the first "," but I want to replace the range faster than getValue () → replace () (for each cell) → setValue. it will take 10mn ... does anyone have an idea?