WeirdGeek

Data Science | Machine Learning | Automation

  • Data Analytics
  • Python
  • Data Science
  • Google Apps Script
  • Machine Learning
  • Artificial Intelligence
  • SQL Server

05/05/2020 By WeirdGeek 2 Comments

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.

Find and Replace in Google Apps Script

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.

Related posts:

  1. Google Apps Script to search a string in Google Sheet [Part II]
  2. Loop over two list or array in Google Apps Script
  3. Regular Expression in Google Apps Script
  4. Import CSV file data to Google Spreadsheet using Google Apps Script

Filed Under: Google Apps Script Tagged With: Google, Google Apps Script, Google Sheets

Comments

  1. Dirk says

    22/08/2020 at 4:27 pm

    How does the code look like, if I want to replace ALL found strings?

    E.g. I want to replace all found “#” by “\n”.

    Reply
  2. Manjunath says

    18/05/2022 at 5:00 am

    Hi Team

    Thanks a ton….
    you have reduced my time…

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Subscribe to my Blog !!

Enter your email below to subscribe my blog and get the latest post right in your inbox.

  • Home
  • Terms
  • Privacy
  • Contact Us

Copyright © 2023 · WeirdGeek · All trademarks mentioned are the property of their respective owners.