Home > Software engineering >  Search column for text, and use array list to insert text in another cell
Search column for text, and use array list to insert text in another cell

Time:11-25

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:

enter image description here

Example of desired result:

enter image description here

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

My Code:

function snack() {

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



//search headers (converted to sep. function to return row & column)

  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

    //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

Raw Data:

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

References:

  1. enter image description here

    After:

    enter image description here

    Reference:

  • Related