Hello I'm using AppScripts (first time) and I'm trying to create a function as same as a formula that can be done in google sheets:

=iferror(if(search("harness",A2)>0,"Harness",),"NO")

The data looks something like this:

And I want the output to be like this:

So I like to create a new column with it's header name and to look for the substring in the specific column string.

Is there a function that I can do to recreate that formula. Thanks in advance is my first time using AppScripts. I've done a couple of functions to import data out of a folder in google drive and clean it but I haven't found anything on this matter in my research.

Edit: so after more research I manage to create the following function:

function foo() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('Sheet1');
    var string = s.getRange('A:A');
    var v = string.getValues();
    var substring = "harness";
    if(string.indexOf(substring) !== -1){ss.getSheetByName('Sheet1').getRange('B:B').setValue('harness')}        
    }

However it's inserting lines of data in each row with the value harness when it doesn't exist. Any guidance on this?

CodePudding user response：

Try this custom function:

function subString(range, string, replace) {

  return range.toLowerCase().includes(string) ? string : replace;

}

You need to define the range on the first parameter, as well as the substring you are looking for on the second parameter, then the third parameter would be the word you would want to set if the substring does not match.

OUTPUT:

MODIFICATION:

This version handles array values following this

CodePudding user response：

I manage to create a function for this, in case anybody needs in the future:

function findharness() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');
  var r = s.getRange(2,1,s.getLastRow() - 1,1);
  var v = r.getValues();
  var searchTerm = "harness";  
  const result = v.map(row => row[0].toString().toLowerCase().indexOf(searchTerm) > 1 ? ["Harness"]: ["NO"])
  r.offset(0,1).setValues(result)
}