search spreadsheet column for text in a string and

Question

Using google apps script and spreadsheet, I have been trying to do a simple thing but can't figure out the problem. I have a sheet, with a blank column and a column with text. The columns are not next to each other. I am trying to search for text in each cell in one column, and if the text is found, then set the value of the cell in the empty column as 'Yes'.

Example (sorry no code - I've gone round and round with it for hours, and what I have is so convoluted, it's best to just provide an example):

Column A with text  Empty Column F
abcd efg hij
klmn opq rstu  
vwxzy                  Yes

What is the simplest code to search Column A for 'xyz' and return a 'Yes' in Column F?

I've looked at and tried about a dozen different code examples online, and can't get any of them work. Appreciate any help with this!!

EDIT (Final hopefully) for my use (I have some backend utilities that get me the column number based on the header name, that code not included in this, fyi):

var sskey = SpreadsheetApp.openById('**********************')

function otherfunction(){
  addCustomValue('POCs', 'Groups', 'Champion', 'Champion', 'Yes');
}

function addCustomValue(sheetNamestr, searchColnamestr, writeColnamestr, searchKeystr, writeValstr) {
  var sheet = sskey.getSheetByName(sheetNamestr);
  var searchColnum = MyUtilities.getColIndexByName(sheet, 1, searchColnamestr);
  var writeColnum = MyUtilities.getColIndexByName(sheet, 1, writeColnamestr);
  var data = sheet.getDataRange().getValues();
  for (n=0; n<data.length; ++n) {
    if (data[n][searchColnum-1].toString().match(searchKeystr)==searchKeystr){ data[n][writeColnum-1] = writeValstr};
    }
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);
}

Thanks Serge! Now I can run this over my spreadsheets based on any columns and conditions!

Answer 1

function myFunction() {
  //Variable to keep track of the sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //Start at row 1, end at the last row of the spreadsheet
  for(var i=1;i<sheet.getLastRow();i++){
    var value = sheet.getRange(i, 1).getValue();
    //Compare the value of the cell to 'xyz', if it is then set column 4 for that row to "Yes"
    if(value == 'xyz'){
      sheet.setActiveRange(sheet.getRange(i, 4)).setValue('Yes');
    }
  }
}

Answer 2

This is a possible simple script that does what you need. (If your sheet contains formulas or custom function then it should be modified to take it into account)

function test(){
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getDataRange().getValues(); // read all data in the sheet
  for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A
    if(data[n][0].toString().match('xyz')=='xyz'){
      // if column A contains 'xyz' then set value in index [5] (is column F)
      data[n][5] = 'YES'
    };
  }
  Logger.log(data)
  sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
}