Basic · B5
Custom functions
Sheets has hundreds of formulas, but not one for Thai grades or school email addresses. With Apps Script you
can add your own. You write a function, and the sheet can call it as =YOUR_FUNCTION().
A formula in four lines
/**
* Doubles a number.
*
* @param {number} value The number to double.
* @return {number} The number times two.
* @customfunction
*/
function DOUBLE(value) {
return value * 2;
}- Paste this into a Sheet-bound project and save. You don't press Run.
- Back in the Sheet, type 21 in A1.
- In B1, type
=DOUBLE(A1). As you type, DOUBLE appears in the autocomplete with your description. - B1 shows 42. Change A1 and B1 updates by itself.
The function's parameters receive the values in the brackets, and whatever it returns goes into the
cell. The comment block is JSDoc: @param and @return become the help card in
Sheets, and @customfunction puts it in autocomplete.
Why capital letters?
Sheets formulas aren't case-sensitive, so =double(A1) also works. Naming custom functions in
CAPITALS makes them look like formulas and keeps them apart from your ordinary helper functions.
Cleaning text
/**
* Turns " ploy SRISUK " into "Ploy Srisuk".
* @param {string} text A name in any case, with any spacing.
* @return {string} The name in Title Case.
* @customfunction
*/
function PROPER_NAME(text) {
return String(text)
.trim()
.split(/\s+/)
.map((w) => w.charAt(0).toUpperCase() + w.slice(1).toLowerCase())
.join(' ');
}
/**
* Makes a school email address from a first name and a student number.
* @param {string} first First name.
* @param {number} id Student number.
* @return {string} e.g. ploy.12345@school.example
* @customfunction
*/
function SCHOOL_EMAIL(first, id) {
return `${String(first).trim().toLowerCase()}.${id}@school.example`;
}| A | B | C | |
|---|---|---|---|
| 1 | " ploy SRISUK " | =PROPER_NAME(A1) | Ploy Srisuk |
| 2 | Ploy | =SCHOOL_EMAIL(A2, 12345) | ploy.12345@school.example |
Column B shows the formula and column C shows what the cell displays.
A Thai grade formula
Turning a mark into a 0–4 grade is a long nested IF in Sheets. As a custom function, it reads like a table:
/**
* Converts a mark out of 100 to a Thai 0–4 grade.
* @param {number} mark The mark, 0 to 100.
* @return {number} The grade: 4, 3.5, 3 … 0.
* @customfunction
*/
function THAI_GRADE(mark) {
if (mark === '' || mark === null) return '';
if (typeof mark !== 'number') return 'Not a number';
if (mark >= 80) return 4;
if (mark >= 75) return 3.5;
if (mark >= 70) return 3;
if (mark >= 65) return 2.5;
if (mark >= 60) return 2;
if (mark >= 55) return 1.5;
if (mark >= 50) return 1;
return 0;
}It checks for an empty cell first (return an empty string so the cell looks blank) and for text that isn't a mark. Custom functions should handle bad input politely, because real sheets are messy.
Ranges arrive as 2D arrays
When you write =COUNT_WORD(A2:A40, "late"), the function doesn't get a Range object. It gets the
values, as the same 2D array you met in B3. So all your array methods work.
/**
* Works on a single cell or a whole column: =THAI_GRADE_ALL(C2:C40).
* @param {number|number[][]} input A mark or a range of marks.
* @return The grade, or a column of grades.
* @customfunction
*/
function THAI_GRADE_ALL(input) {
if (Array.isArray(input)) {
return input.map((row) => row.map((cell) => THAI_GRADE(cell)));
}
return THAI_GRADE(input);
}
/**
* Counts the cells in a range that contain a word, ignoring case.
* @param {string[][]} range The cells to search.
* @param {string} word The word to look for.
* @return {number} How many cells contain it.
* @customfunction
*/
function COUNT_WORD(range, word) {
const w = String(word).toLowerCase();
return range.flat().filter((cell) => String(cell).toLowerCase().includes(w)).length;
} THAI_GRADE_ALL checks whether it got one value or many. With a range it maps every row and every cell
and returns a 2D array of grades, so one formula in D2 fills the whole column. That is much faster than 40
separate formulas, each calling the script.
Returning a table
/**
* Returns a whole table: a multiplication grid of the given size.
* The result "spills" into the cells to the right and below.
* @param {number} size How many rows and columns.
* @return {number[][]} The table.
* @customfunction
*/
function TIMES_TABLE(size) {
const out = [];
for (let r = 1; r <= size; r++) {
const row = [];
for (let c = 1; c <= size; c++) row.push(r * c);
out.push(row);
}
return out;
}
Type =TIMES_TABLE(5) in A1 and a 5 × 5 grid fills A1:E5. If any of those cells already has something
in it, the formula shows #REF! with "Array result was not expanded because it would overwrite data".
What custom functions can't do
A formula runs every time the sheet recalculates, for anyone who opens it. To keep that safe and quick, Google adds limits:
| Rule | What it means for you |
|---|---|
| No services that need permission | No Gmail, Drive, Calendar, or editing other files. Reading values passed in, UrlFetchApp, Utilities and maths are fine. |
| Only return a value | A formula can't set other cells, change colours or show pop-ups. It only fills its own cell (or spills). |
| 30-second limit | Each call must finish in 30 seconds or the cell shows an error. |
| Deterministic | Sheets caches results. A function using new Date() or Math.random() won't update on its own. |
| Names | No name ending in _ (that marks a private function), and avoid clashing with built-ins like SUM. |
// This will NOT work as a custom function.
function EMAIL_ME(text) {
MailApp.sendEmail('me@example.com', 'From my sheet', text);
return 'sent';
}
// The cell shows #ERROR! with the message:
// "Exception: You do not have permission to call MailApp.sendEmail."Debugging a custom function
Hover over a #ERROR! cell to read the message. To step through the logic, write a test function in the
editor that calls it, e.g. console.log(THAI_GRADE(72)). A custom function's own
console.log output goes to the Executions page (left rail), not the editor's log.
Practice
Write each one with a JSDoc comment so it shows in autocomplete.
1. =WITH_VAT(price) Easy
Add 7% VAT and round to 2 decimal places.
Show a solution
/**
* Adds Thai VAT (7%) to a price.
* @param {number} price Price before VAT.
* @return {number} Price including VAT, rounded to 2 decimals.
* @customfunction
*/
function WITH_VAT(price) {
return Math.round(price * 1.07 * 100) / 100;
}2. =INITIALS(name) Easy
"Ploy Srisuk" → "P.S."
Show a solution
/**
* "Ploy Srisuk" → "P.S."
* @param {string} name A full name.
* @return {string} The initials.
* @customfunction
*/
function INITIALS(name) {
return String(name)
.trim()
.split(/\s+/)
.map((w) => w[0].toUpperCase() + '.')
.join('');
}3. =AGE(birthDate) Medium
Age in whole years. Careful: someone born later in the year hasn't had their birthday yet.
Show a solution
/**
* Age in whole years from a birth date cell.
* @param {Date} birth A date.
* @return {number} Age in years.
* @customfunction
*/
function AGE(birth) {
if (!(birth instanceof Date)) return 'Need a date';
const now = new Date();
let age = now.getFullYear() - birth.getFullYear();
const hadBirthday =
now.getMonth() > birth.getMonth() ||
(now.getMonth() === birth.getMonth() && now.getDate() >= birth.getDate());
if (!hadBirthday) age--;
return age;
}4. =REVERSE_ROWS(range) Medium
Return the rows of a range in reverse order, so it spills as a table.
Show a solution
/**
* Reverses the order of rows in a range.
* @param {any[][]} range The cells.
* @return {any[][]} The same rows, last first.
* @customfunction
*/
function REVERSE_ROWS(range) {
return range.slice().reverse();
}End of the Basic level
You can now run code, use JavaScript, read and write Sheets, and add formulas. Intermediate starts by making sheets look good and react to the people using them.