Search column for text, and use array list to insert text in another cell

Question

Current Problem:

1. I am trying to have my script look through column A in the example below, and if a cell contains a certain word, to have text inserted into the cell next to it in column B.
2. I need to have the script find column heads by their name instead of hard references (example, find column called "Ingredient").
3. I'm unsure how to have my script insert text adjacent to a cell if there is a match

I made my script just with apple so far, as I think I can take it from there.

I was hoping to use a script that would use some form of array list, so that if a cell contained a word from that list it would insert text into an adjacent cell.

However. I didn't quite know how to do that so I think what I was able to research may suffice. I couldn't find documentation on how the whenTextContains filter works, so I think match is the next best answer?

At the moment my code works, but it's not placing the snack in the right place (ex. placing it in B1 instead of B2 for apple).

What I've Tried:

1. Made a simple code that works but needs hard references to column/row references
2. Tried a few different for iterations but it doesn't seem to work

Example of current sheet:

Example of desired result:

Simple Code that works but needs hard reference to columns/rows:

function snack() {

const ws = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var indgredientRange = ws.getRange(2,1);
var iValues = indgredientRange.getValues();

for (var i in iValues){
  if(iValues[i][0].match("apple")!=null){
    ws.getRange(2,2).setValue('apple pie');
  }//derived from question https://stackoverflow.com/questions/11467892/if-text-contains-quartal-or-year-then-do-something-google-docs and answer of @Kuba Orlik
}

My Code:

function snack() {

const ws = SpreadsheetApp.getActive().getSheetByName('Sheet1');



//search headers (converted to sep. function to return row & column) as derived from code in @NightEye answer https://stackoverflow.com/questions/69561489/using-column-header-variables-in-r1c1-formula

  const [tfIRow, tfICol] = getLocationOfText(ws,'Ingredient');
  const [tfSnackRow,tfSnackCol] = getLocationOfText(ws,'Snack');
  const [tfRatingRow,tfRatingCol] = getLocationOfText (ws,'Rating');


  //snack arrays below

  let applesnack = ['apple pie']
  let flowersnack = ['flower seeds']
  let orangesnack = ['orange slices']
  let baconsnack = ['bacon pie']
  let ewsnack = ['ew']

    function getLocationOfText(sheet, text) {
    const tf = sheet.createTextFinder(text);
    tf.matchEntireCell(true).matchCase(false);
    const tfNext = tf.findNext();

    return [tfNext.getRow(), tfNext.getColumn()]
    }//end of getLocationofText function as derived from code in @NightEye answer https://stackoverflow.com/questions/69561489/using-column-header-variables-in-r1c1-formula

    //action script below:

    var indgredientRange = ws.getRange(tfIRow,tfICol,ws.getLastRow(),ws.getLastColumn());
    var iValues = indgredientRange.getValues();

for (var i in iValues){
  if(iValues[i][0].match("apple")!=null){
    ws.getRange(tfSnackRow,tfSnackCol).setValue(applesnack);
  }
}

}//end of snack function. For statement derived from  @Kuba Orlik's answer in https://stackoverflow.com/questions/11467892/if-text-contains-quartal-or-year-then-do-something-google-docs

Raw Data:

Ingredient	Snack	Rating
apple
flower
orange
bacon
lemon
apple bitters
bacon bits

References:

1. Filteria Criteria
2. Using Column Header Variables
3. Searching column script example

Answer 1

The result of createTextFinder....findNext() is a Range that have getRow() and getColumn() method that you can use it to determine the position of a column header. Since you've mentioned here that "if a cell contains a certain word, to have text inserted into the cell next to it", you only need to search for the Ingredient column and add +1 to the result of getColumn().

Try this code below:

function snacks(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Sheet1");
  var ing = sh.createTextFinder("Ingredient").findNext(); // find Ingredient Column
  var ingCol = sh.getRange(ing.getRow()+1, ing.getColumn(), sh.getLastRow()-1, 1); //get the range of the content of Ingredient column

  var snacks = ['apple', 'flower', 'orange', 'bacon', 'lemon']; //search strings
  for(var i = 0; i < snacks.length; i++){ 
    // loop each search string and use it to search to Ingredient range.
    var search = ingCol.createTextFinder(".*"+snacks[i]+".*").useRegularExpression(true).findAll(); 
      if(search){ //if found set the appropriate value
        search.forEach(el => {
          var val = "";
          if(snacks[i] == "apple"){
            val = "apple pie";
          }else if(snacks[i] == "flower"){
            val = "flower seeds";
          }else if(snacks[i] == "orange"){
            val = "orange slices";
          }else if(snacks[i] == "bacon"){
            val = "bacon pie";
          }else if(snacks[i] == "lemon"){
            val = "ew"
          } 
          sh.getRange(el.getRow(), el.getColumn()+1).setValue(val)// set value
        })
      }
  }
}

Before:

After:

Reference:

• Class Range
• Class TextFinder

Answer 2

Same functionality no text finder required

function snack() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const [hA, ...vs] = sh.getDataRange().getValues();
  let col = {};
  let idx = {};
  hA.forEach((h, i) => { col[h] = i + 1; idx[h] = i; });//build col and idx so that headers can move anywhere
  const snack = { 'apple': 'apple pie', 'flower': 'flower seeds', 'orange': 'orange slices', 'bacon': 'bacon pie','lemon':'ew'};//relates search keys to snack strings
  const keys = Object.keys(snack);
  vs.forEach((r, i) => { //loop rows
    keys.forEach((k,j) => { //loop through keys
      if(~r[idx['Ingredient']].toString().indexOf(k)) {
        sh.getRange(i + 2, col['Snack']).setValue(sh.getRange( i + 2, col['Snack']).getValue() + '\n' + snack[k]);//displays appropriate keys for each match and it supports multiple matches on the same string
      }
    });
  });
}

Sheet0 (after):

Reference:

• Object

• Additional Question:

If you wish to move the below data to the third column then change this:

sh.getRange(i + 2, col['Snack']).setValue(sh.getRange( i + 2, col['Snack']).getValue() + '\n' + snack[k]);

to this:

sh.getRange(i + 2, 3).setValue(sh.getRange( i + 2, col['Snack']).getValue() + '\n' + snack[k]);