Custom functions: write your own =FUNCTION()

When a named function can't express your logic, write the function in JavaScript instead. An Apps Script custom function is used in a cell exactly like SUM.

Open Extensions › Apps Script, paste this, and save:

/**
 * Return on ad spend: revenue divided by spend, 0 when spend is 0.
 * Works on one cell or a whole column.
 *
 * @param {number|number[][]} spend Spend, one cell or a range.
 * @param {number|number[][]} revenue Revenue, the same shape as spend.
 * @return Revenue per dollar of spend.
 * @customfunction
 */
function ROAS(spend, revenue) {
  if (Array.isArray(spend)) {
    return spend.map(function (row, i) {
      return row.map(function (s, j) {
        return ROAS(s, revenue[i][j]);
      });
    });
  }
  return spend ? revenue / spend : 0;
}

Back in the sheet, =ROAS(B2, C2) works on one row and =ROAS(B2:B50, C2:C50) fills the column. How it works:

• Ranges arrive as 2D arrays. B2:B50 becomes [[2400], [5200], ...], a list of rows. Return a 2D array and it spills; return a single value and it fills one cell. The Array.isArray check lets one function handle both.
• The comment block matters. @customfunction puts ROAS in autocomplete, and the description and @param lines show up as help while someone types.
• Inputs are values, not cells. The function gets the numbers in B2, not B2 itself, so it can't format cells, write to other cells or see what's next to its inputs.

Limits worth knowing before you build on one:

• No services that need permission. A custom function can't send email or edit the sheet. It can read the web with UrlFetchApp.
• 30 seconds per call. Called from 500 separate cells, it runs 500 times and slows the file. Pass a whole range once instead.
• Recalculates only when its inputs change. A function that reads today's date or a web page won't refresh by itself.

Rule of thumb: if a LAMBDA can do it, make a named function. It's faster and needs no script. Reach for a custom function when you need loops, string building, or a call to an outside API.