Scan cells in one column for specific text value,

Question

We have a spreadsheet we are using where personnel input text values in one column, within these text values are certain words we want to hunt for.

If they appear, we want to populate another column with another word. E.g.:

Col1: 'today i went to the beach'
function finds 'beach'
function enters 'sand' in same row of Col5.

Any help greatly appreciated.

Answer 1

Here's a simple approach. It doesn't deal with a cell containing multiple terms or with a term being embedded in another word. eg. If a term were see then seething would also match.

function findTerms() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data  = sheet.getDataRange().getValues();

  // Key-value pairs of a term to look up and a value to display
  var pairs = [
    ["from", "to"],
    ["this", "that"],
    ["...", "..."]
  ];

  var inspectCol = 1; // The column you want to look in
  var resultsCol = 2; // The column to write results to  

  // loop over the rows of data
  for(var i=0; i < data.length; i++){

    // loop over the terms and values
    for(var n in pairs){
      var term = pairs[n][0];
      var val  = pairs[n][1];

      // if the specified column in the current row contains the current term
      if(data[i][inspectCol-1] && data[i][inspectCol-1].indexOf(term) != -1){

        // write val to the results column
        sheet.getRange(i+1, resultsCol).setValue(val);
      }
    }
  }
}

To get fancier and do things like only find complete words, use match() and regular expressions, instead of indexOf().