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

Question

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

Answer 1

from Cameron Roberts answer that works in almost every cases (if all cells are filled with strings only) and just for typing convenience, 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()
    replaceInSheet(sheet,'values','test');
}

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
  sheet.getDataRange().setValues(values);
}

Answer 2

You can achieve this by reading in all values in the sheet (as an Array), Looping over the array, replacing the values, then writing the entire array back to the sheet.

There is a basic example below. You may need to modify this if your sheet contains formulas, or if you want to replace the text more than once in a given cell.

Note that any changes made to sheet between the time you read in and write out the data will be lost, and potentially could break this example.

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    replaceInSheet(sheet,'ga: sessions','Sessions');
}

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.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
  sheet.getDataRange().setValues(values);
}

Documentation:

Array.map: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map

String.replace: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace

Sheet.getDataRange: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

range.GetValues: https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()

Answer 3

TextFinder was released April of 2019:

function textFinder() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();
  // Creates  a text finder for the range.
  const textFinder = range.createTextFinder('findValue');
  const allOccurrences = textFinder.replaceAllWith('replaceValue');
}

Answer 4

This is what worked for me, and it's simple.

function changevalues() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NameOfSheet');
    var rangecells = sheet.getRange('DesiredRange');
    rangecells.setValue('DesiredValue')
}

Answer 5

The getActiveSheet() function returns a Sheet object whose functions are documented at the link. It would be awesome if there were an API like that, but currently you need to delete and insert (rows, columns, or the contents of a specific range) in order to be able to do that sort of replacement.

Answer 6

Try something like this:

var sheet  = SpreadsheetApp.getActiveSheet();
var cell = sheet.getRange("A1:A1");
cell.setValue('ga: sessions','Sessions');

Answer 7

The selected answer does not work with the data containing Date cells. Actually it changes these cells to String and this is not expected behavior.

I modified the code to check if the value is number and change only string values.

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    replaceInSheet(sheet,'values','test');
}

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){
      if (typeof original_value != 'string') {
        return original_value;
      } else {
        return original_value.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
  sheet.getDataRange().setValues(values);
}

Have a nice day