Yuri Yuri - 4 months ago 122
Javascript Question

How do I replace text in a spreadsheet with Google Apps Script?

I wanna find a specified text in spreadsheet and replace it with other word. I tried like this.

sheet = SpreadsheetApp.getActiveSheet()
sheet.replaceText ('ga: sessions','Sessions');

Then it says
"Cannot find function replaceText in object Sheet."


from Cameron Roberts answer that works in almost every cases (if all cells are filled with strings only) and just for typing convenience (please accept his answer) here is the same script with a small change in the map function : I added toString() to the return argument.

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()

function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();

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

  //write the updated values to the sheet