CODEWITHSUNDEEP
replacegoogle-apps-scriptgoogle-sheetsautomationfind
M. Hardwick
M. Hardwick

Reputation: 21

Auto Find and Replace Script in Google Sheets - Delete some certain cell content - global replace

Many cell entry's in my sheet contain extraneous words that I want to delete. I need a script to find keywords within a single column (in this case "B") and delete them in order. The goal is to make the cell entries shorter.

My keywords are "Epic Artifactory DIY", "Barn", "Planks", "Pack, "Coupon: WTXPXZP", "Coupon: FREESHIP50", "Coupon: SPRING10", and "Wall".

I found this script, but it will not work for me.

function fandr() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var r=s.getDataRange();
  var vlst=r.getValues();
  var i,j,a,find,repl;
  find="abc";
  repl="xyz";
  for (i in vlst) {
    for (j in vlst[i]) {
      a=vlst[i][j];
      if (a==find) vlst[i][j]=repl;
    }
  }
  r.setValues(vlst);
}

Thanks

Upvotes: 2

Views: 852

Answers (2)

Alan Wells
Alan Wells

Reputation: 31300

Here is some code that gets the data in only one column, and replaces all the content with an empty string (deletes the words). Replace words in one column of a Google Sheet.

function replaceInColumn() {
  var arrayWordsToFind,dataInColumn,dataAsString,newString,
      newData,outerArray,i,lastrow,L,sh,ss,tempArray,toFind;

  arrayWordsToFind = [
  "Epic Artifactory DIY", "Barn", "Planks", "Pack", 
    "Coupon: WTXPXZP", "Coupon: FREESHIP50", "Coupon: SPRING10", "Wall"
    ]

  ss = SpreadsheetApp.getActiveSpreadsheet();
  sh = ss.getSheetByName("Your Sheet Name Here");
  lastrow = sh.getLastRow();//Get row number of last row

  //sh.getRange(start row, start column, number of Rows, number of Columns)
  dataInColumn = sh.getRange(2, 2, lastrow).getValues();

  dataAsString = dataInColumn.toString();//Convert 2D array to a string
  //Logger.log('dataAsString: ' + dataAsString)

  newString = dataAsString;

  L = arrayWordsToFind.length;//The number of words to find

  for (i=0;i<L;i++) {//Loop once for every word to find
    toFind = new RegExp(arrayWordsToFind[i], "g");//define new Reg Ex with word to find - replace globally
    newString = newString.replace(toFind,"");//Delete all found words
  }

  //Logger.log('newString: ' + newString)

  newData = newString.split(",");//Convert string to 1D array
  outerArray = [];

  L = newData.length;

  for (i=0;i<L;i++) {
    //Logger.log('i: ' + i)
    //Logger.log('newData[i]: ' + newData[i])

    tempArray = [];//Reset
    tempArray.push(newData[i]);
    outerArray.push(tempArray);//Create a new 2D data array
  }

  sh.getRange(2, 2, outerArray.length).setValues(outerArray);
}

Key words: find replace column global

Upvotes: 3

Amit Agarwal
Amit Agarwal

Reputation: 11268

This Google Script function will help you find and replace text across all cells in a particular column (B in this case).

function findReplace() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var column = 2;
  for (var d=0, l=data.length; d<l; d++) {
    if (data[d][column-1] === "find") {
      sheet.getRange(d+1, column).setValue("replace");
    }
  }
  SpreadsheetApp.flush();
}

Upvotes: 0

Related Questions