JavaScript for Sheets Updated November 2026

String methods

Cell text is a JavaScript string: clean it with trim(), toLowerCase() or toUpperCase(), split(",") to an array and join(", ") back, includes("x") and startsWith("x") for checks, replace(/pattern/g, "") for regex edits, slice(0, 10) for cutting and padStart(5, "0") for leading zeros. Wrap cell values with String() first, since numbers and dates are not strings.

Syntax

str.trim() | str.split(sep) | arr.join(sep) | str.replace(/re/g, text) | str.includes(x) | str.slice(start, end) | str.padStart(n, char)

Examples

const raw = "  Ana Lopez  ";
const clean = raw.trim().toLowerCase().split(" ").map(w => w[0].toUpperCase() + w.slice(1)).join(" ");
Logger.log(clean);

Result: Ana Lopez

const tags = "red, green ,blue".split(",").map(t => t.trim());
Logger.log(tags.includes("green") + " " + tags.join("|"));

Result: true red|green|blue

const phone = "+1 (786) 531-7654".replace(/\D/g, "");
const id = String(42).padStart(5, "0");
Logger.log(phone + " " + id);

Result: 17865317654 00042

const sheet = SpreadsheetApp.getActiveSheet();
const emails = sheet.getRange("B2:B100").getValues();
const cleaned = emails.map(r => [String(r[0]).trim().toLowerCase()]);
sheet.getRange("B2:B100").setValues(cleaned);
const msg = `Cleaned ${cleaned.length} emails`;
Logger.log(msg);

Result: Column B trimmed and lowercased; log shows Cleaned 99 emails

Common mistakes

r[0].trim is not a function: the cell holds a number or a Date; use String(r[0]).trim().
replace only changed the first match: a string pattern replaces once; use a regex with the g flag or replaceAll.
split(",") leaves spaces around items: map each item through trim, or split on /\s*,\s*/.
Comparing text fails because of invisible characters: non-breaking spaces survive trim; use replace(/\u00a0/g, " ") first.

Practice String methods

These drills check your answer on live data, free, no account needed.

Capitalize each wordWords are separated by single spaces. Uppercase the first character of every word and lowercase the rest of it. Keep the spacing exactly as it is, including repeated spaces and spaces at the ends.
Make a URL slugTurn a title into a URL slug: lowercase, accents removed (é becomes e), and every run of characters that are not a to z or 0 to 9 replaced by a single hyphen. No hyphen at the start or end. Characters with no plain-letter form, such as 日本, are dropped.
Truncate with an ellipsisIf `s` has at most `max` characters, return it unchanged. Otherwise keep the first `max - 1` characters and add the ellipsis character `"…"`, so the result is exactly `max` characters long. Count characters as Unicode code points, so a character outside the Basic Multilingual Plane, such as 𝒳, is never cut in half. `max` is at least 1.

Questions

How do I capitalize the first letter of each word?

Split on spaces, uppercase the first character and keep the rest: s.split(" ").map(w => w ? w[0].toUpperCase() + w.slice(1).toLowerCase() : w).join(" ").

How do I check if a cell contains text, case-insensitively?

String(value).toLowerCase().includes("invoice") or a regex with the i flag: /invoice/i.test(value).

map, filter and reduceUse map, filter and reduce on Google Sheets data in Apps Script: transform rows, keep rows that match a condition, sum a column, chain them, write back.
Utilities.formatDateFormat dates in Google Apps Script with Utilities.formatDate: the time zone argument, Java pattern letters like yyyy MM dd HH mm, month names, MM versus mm.
Gmail search and labelsAutomate Gmail with Apps Script: GmailApp.search with Gmail query syntax, loop threads and messages, read subject, sender and body, apply labels, archive.
JSON.parse and JSON.stringifyUse JSON.parse and JSON.stringify in Google Apps Script: turn an API response into objects, write nested fields to a sheet, store objects as properties.