Find and Replace in Google Apps Script

Here in this post we will see how we can create a find and replace function in Google apps script. This function will find a particular searched string or value and replace it with the desired value.

Google Apps Script code :

Let’ see how code for Find and Replace works in Google Apps Script.

function FindandReplace(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data")
  var lastRow = sheet.getLastRow()
  var lastColumn = sheet.getLastColumn()
  var range = sheet.getRange(1, 1, lastRow, lastColumn)
  var to_replace = "Blank";
  var replace_with = "";
  var data  = range.getValues();

    var oldValue="";
    var newValue="";
    var cellsChanged = 0;

    for (var r=0; r<data.length; r++) {
      for (var i=0; i<data[r].length; i++) {
        oldValue = data[r][i];
        newValue = data[r][i].toString().replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[r][i] = newValue;
        }
      }
    }
    range.setValues(data);
}

 

Understanding the code:

The above FindandReplace function will first get the sheet on which you want to perform the find and replace. Then we get the last row and column dynamically which will help in selecting the data range. Also, it will allow code to work irrespective of the you append or delete the data in the sheet. Then we store the word we need to find and replace in two separate variable. Then we get the all the data in the above range and save it in a variable named data. In the above code we search for the string Blank and replaced it with “” (empty).

And finally we loop over the rows and columns using for loop and replace the old value with the desired new one.

To get more detailed knowledge about Google Apps Script, you can check the official website. Also if you want to explore more codes/posts related to Google Apps Script on our website, then you can find it here.

If you have any questions or if you need any help please get in touch with me using the comment section below.