Google Script to See If Text Contains a Value

Google Script to see if text contains a value

Google Apps Script is javascript, you can use all the string methods...

var grade = itemResponse.getResponse();
if(grade.indexOf("9th")>-1){do something }

You can find doc on many sites, this one for example.

How to find text in a string using google script?

You need to check if the str is present:

if (str) {
    if (str.indexOf('task') > -1) {
        // Present
    }
}

Alternatively, you can use test and regex:

/task/.test("task is completed");

/task/.test(str);

1. /task/: Regex to match the 'task'
2. test: Test the string against regex and return boolean

How to get a value after a character, from a string based on a text match using formula?

Per my comments to your original post, I feel that there is a lot we don't know about your bigger goal. But as you aren't able to provide that, this solution will work for your one exact example.

Place the following formula in C4:

=ArrayFormula(IFERROR(VLOOKUP(A4;SPLIT(FLATTEN(SPLIT(E4;";"));"$");B4+1;FALSE)))

(See the new sheet "Erik Help.")

The inner SPLIT splits the E4 string at every semicolon.

FLATTEN sends that all to one column.

The outer SPLIT then splits at each "$".

VLOOKUP can then try to find the Col-A term in the first column of the resulting virtual chart. If found, it will return the column value that matches the Col-B value + 1 (since column 1 of the virtual array is the labels, e.g., 'tradicional,' etc.).

If no match is found for both the Col-A and Col-B data, then IFERROR returns null.

Google Script Run Function IF text in another sheet's column contains a 'specific text'

After reviewing your sheet, this is a possible solution

Code.gs

const sS = SpreadsheetApp.getActiveSpreadsheet()
function grabData() {
  const sheetIn = sS.getSheetByName('data')
  const sheetOut = sS.getSheetByName('Desired Outcome')
  const range = 'A2:B'
  /* Grab all the data from columns A and B and filter it */
  const values = sheetIn.getRange(range).getValues().filter(n => n[0])
  /* Retrieve only the names if it containes 250p */
  /* In format [[a], [b], ...] */
  const parsedValues = values.map((arr) => {
    const [type, name] = arr
    if (type.toLowerCase().includes('250p')) {
      return name.split('\n')
    }
  })
    .filter(n => n)
    .flat()
    .map(n => [n])
  /* Add the values to the Desired Outcome Sheet */
  sheetOut
    .getRange(sheetOut.getLastRow() + 1, 1, parsedValues.length)
    .setValues(parsedValues)
}

Google Sheets script - For each used cell in range 1, if cell value exists in range 2, get range 2 match's row number

If you print the value of sheettwoListFlat, it has only 5 elements. Once the iterator of for loop reaches 5 or more it will automatically set if (sheettwoListFlat[i] == rangeOneValues[i]) { to false. Also the statement will only work if the elements of the same index of both arrays are equal.

It would be also more efficient if you search the Sheet1 using the values of Sheet2 since Sheet2 has lesser value.

Here I used TextFinder to find the text within a range.

Using Sheet2 values as search key

Code:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetone = spreadsheet.getSheetByName('Sheet1');

  var lastRowRangeOne = sheetone.getRange('I2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var rangeOneValues = sheetone.getRange('I2:I' + lastRowRangeOne);
  var sheettwo = spreadsheet.getSheetByName('Sheet2');
  var sheettwoLastRow = sheettwo.getLastRow();
  var sheettwoList = sheettwo.getRange('A2:B' + sheettwoLastRow).getValues();

  for (var i = 0; i < sheettwoList.length; i++){
    var find = rangeOneValues.createTextFinder(sheettwoList[i][0]).findNext()
    if(find){
      var sheetOneRowNumber = find.getRow();
      sheetone.getRange('M' + sheetOneRowNumber).setValue(sheettwoList[i][1]);
    }
  }
}

Using Sheet1 values as search key

Code:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetone = spreadsheet.getSheetByName('Sheet1');

  var lastRowRangeOne = sheetone.getRange('I2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var rangeOneValues = sheetone.getRange('I2:I' + lastRowRangeOne).getValues();
  var sheettwo = spreadsheet.getSheetByName('Sheet2');
  var sheettwoLastRow = sheettwo.getLastRow();
  var sheettwoList = sheettwo.getRange('A2:B' + sheettwoLastRow);

  for (var i = 0; i< rangeOneValues.length ; i++){ // for each row in range one
    var find = sheettwoList.createTextFinder(rangeOneValues[i][0]).findNext();
    if(find){
      var rowNumber = find.getRow()
      var sheetOneColumnMValue = sheettwo.getRange('B' + rowNumber).getValue(); // get the cell value of the same row in sheet two column B
      var sheetOneRowNumber = i + 2;
      sheetone.getRange('M' + sheetOneRowNumber).setValue(sheetOneColumnMValue);
    }
  }
}

Output:

Note: Both produce the same output but using Sheet2 values as search key is more faster than the other.

---

Related Topics