Does String.includes() work in Apps Script?

Yes. Apps Script runs on V8 (since 2020), so ES6+ methods including String.prototype.includes, Array.prototype.find, and arrow functions are all available. The older Rhino runtime did not support these, which is why older forum answers fall back to indexOf() !== -1 — both work, includes() is just readable.

How do I check if a cell contains any one of several substrings?

Build an array of targets and use Array.prototype.some: ['urgent', 'asap', 'critical'].some(function(t) { return cell.includes(t); }). That short-circuits on the first match, so it's efficient even with a dozen keywords.

Why does getValues() return an empty array when my sheet has data?

The most common cause is that getLastRow() returns 1 (header only) when the script runs before any data rows exist, making the row count argument 0, which produces an empty range. Guard with: if (sheet.getLastRow() < 2) return; before constructing the range.

Can I use this to highlight matching rows instead of writing a flag column?

Yes — replace the setValues() call with a loop that calls sheet.getRange(i + 2, 1, 1, sheet.getLastColumn()).setBackground('#fff2cc') for matching indices. Keep reads and writes separate: finish all getValues() calls first, then do all the background writes, so you don't interleave API calls and burn quota.

// Sheets · Apps Script

Check whether a cell contains a substring in Google Sheets.

Q: Can I use this to highlight matching rows instead of writing a flag column?

Yes — replace the setValues() call with a loop that calls sheet.getRange(i + 2, 1, 1, sheet.getLastColumn()).setBackground('#fff2cc') for matching indices. Keep reads and writes separate: finish all getValues() calls first, then do all the background writes, so you don't interleave API calls and burn quota.

How to test if a cell contains a specific string in Google Sheets using Apps Script — covering single-cell checks with getValue() and the fast, correct pattern for whole-column scans with getValues().

“I need to find rows in a sheet where a cell contains a particular word or phrase, and do something with them from an Apps Script.”

The script

copy · paste · trigger

containsText.gs

Apps Script

// Flag rows in column B that contain a target substring
function flagMatchingRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues();
  var target = 'urgent';
  var flagCol = sheet.getRange(2, 3, data.length, 1);
  var flags = [];

  for (var i = 0; i < data.length; i++) {
    var cell = String(data[i][0]).toLowerCase();
    if (cell.includes(target)) {
      flags.push(['YES']);
    } else {
      flags.push(['']);
    }
  }

  flagCol.setValues(flags);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

The one-cell case

For a single cell, getValue() returns the cell's value as a JavaScript primitive — a string, number, boolean, or Date. Coercing it to a string first with String() protects you from cells that happen to hold a number or are empty, both of which would throw on .includes() otherwise.

var val = sheet.getRange('B2').getValue(); if (String(val).toLowerCase().includes('urgent')) { /* match */ }. Case-folding with .toLowerCase() before the check means you match 'Urgent', 'URGENT', and 'urgent' with one target string. That's the pattern I keep in a utils file and import into every sheet project.

Why reading the whole column at once matters

The slow trap is calling getValue() inside a loop over rows. Each call is a separate round-trip to the Sheets API. On a 500-row sheet that's 500 network calls; Apps Script's 6-minute execution quota evaporates fast, and you'll hit it before you expect to.

getValues() on a range returns a 2-D array in a single call. The snippet above reads the entire column B from row 2 downward into data, then does all the substring checks in plain JavaScript — no further API calls until the final setValues(). On that same 500-row sheet, total API calls drop from 500+ to 2.

The array shape is data[rowIndex][columnIndex], so for a single-column range every value is at data[i][0]. That catches people the first time: getValues() always gives you a 2-D array, even for a one-column selection.

Matching numbers and mixed-type columns

Sheets columns are often mixed: some rows have strings, others have numbers, a few are blank. String(data[i][0]) handles all three — it turns 42 into '42', an empty cell into '', and leaves real strings untouched. Calling .includes() directly on a raw number throws a TypeError, which will silently kill your loop mid-run if you haven't wrapped the function in a try/catch.

If you need a case-sensitive match — usernames, IDs, exact codes — drop the .toLowerCase() call and compare target without folding. For partial-match patterns more complex than a fixed substring, replace .includes(target) with a RegExp: /pattern/i.test(cell) works on the same String(data[i][0]) value.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Does String.includes() work in Apps Script?: Yes. Apps Script runs on V8 (since 2020), so ES6+ methods including String.prototype.includes, Array.prototype.find, and arrow functions are all available. The older Rhino runtime did not support these, which is why older forum answers fall back to indexOf() !== -1 — both work, includes() is just readable.
How do I check if a cell contains any one of several substrings?: Build an array of targets and use Array.prototype.some: ['urgent', 'asap', 'critical'].some(function(t) { return cell.includes(t); }). That short-circuits on the first match, so it's efficient even with a dozen keywords.
Why does getValues() return an empty array when my sheet has data?: The most common cause is that getLastRow() returns 1 (header only) when the script runs before any data rows exist, making the row count argument 0, which produces an empty range. Guard with: if (sheet.getLastRow() < 2) return; before constructing the range.
Can I use this to highlight matching rows instead of writing a flag column?: Yes — replace the setValues() call with a loop that calls sheet.getRange(i + 2, 1, 1, sheet.getLastColumn()).setBackground('#fff2cc') for matching indices. Keep reads and writes separate: finish all getValues() calls first, then do all the background writes, so you don't interleave API calls and burn quota.

// one good script a week