Create new column based on if condition that searches for a substring in the row?

Question

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:

data_column
sadfsd harness
test harness data
harness test data

And I want the output to be like this:

data_column	Harness?
sadfsd harness	Harness
test harness data	Harness
test data	NO

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?

Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for official documentation, free resources and more details. — TheMaster, Jul 21 '22 at 02:16
@TheMaster sorry about that, I added the function that I researched. — silentninja89, Jul 22 '22 at 00:36
`indexOf` won't work in 2D arrays. See https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept — TheMaster, Jul 22 '22 at 10:21

Century Tuna · Answer 1 · 2022-07-22T00:33:12.180

1

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 reference:

function subString(range, string, replace) {

  return Array.isArray(range) ? range.map(row => row.map(data => data.toLowerCase().includes(string) ? string : replace)) : range.toLowerCase().includes(string) ? string : replace;

}

edited Jul 22 '22 at 00:33

answered Jul 21 '22 at 05:21

Century Tuna

1,378
1
6
13

Sorry but I don't get it, my first time using this, should I go something like this: `function subString("B:B: , "harness", "No") { return range.toLowerCase().includes(string) ? string : replace; }` – silentninja89 Jul 21 '22 at 19:06
@silentninja89 uhm no, you directly use the script above on your sheets bound script (or app script bounded to your sheet), then you save it, and use the function similar to how you use functions for Google Sheet spreadsheets. Similar to how I used it on the given output images – Century Tuna Jul 22 '22 at 00:35

score 1 · Accepted Answer · answered Jul 22 '22 at 19:20

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)
}

Create new column based on if condition that searches for a substring in the row?

2 Answers2