Write a custom function in Google Sheets

1 · Prepare

Put these pieces in place

• Open the spreadsheet where you want the formula and put a few Celsius numbers in column A.
• Write the script inside that spreadsheet, using Extensions and then Apps Script. A custom function works only in the file its script belongs to.
• Pick a name in capital letters that no built-in Sheets function already uses.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

/**
 * Converts a Celsius temperature to Fahrenheit.
 *
 * @param {number|string|boolean|Date|null|undefined|Array<Array<number|string|boolean|Date|null|undefined>>} input One Celsius value, or a range of them.
 * @return {number|string|Array<Array<number|string>>} The Fahrenheit value, or a grid of values the same shape as the input.
 * @customfunction
 */
function CELSIUS_TO_FAHRENHEIT(input) {
  if (Array.isArray(input)) {
    return input.map((row) => row.map((cell) => celsiusCellToFahrenheit(cell)));
  }
  return celsiusCellToFahrenheit(input);
}

/**
 * @param {number|string|boolean|Date|null|undefined} cell
 * @return {number|string}
 */
function celsiusCellToFahrenheit(cell) {
  if (cell === '' || cell === null || cell === undefined) return '';

  const celsius = Number(cell);
  if (isNaN(celsius)) {
    throw new Error('CELSIUS_TO_FAHRENHEIT needs a number. It received: ' + cell);
  }

  return Math.round((celsius * 9 / 5 + 32) * 10) / 10;
}

4 · Run safely

Follow these steps in order

1. Add a few test values

   In A2:A5, put 100, 0, leave A4 empty, and put the word warm in A5. That gives you one round number, one easy answer, one blank, and one mistake to test with.

2. Paste both functions and save

   Open Extensions, choose Apps Script, paste the code, and save. Keep the comment block above the function. The @customfunction line is what makes the name appear in the formula suggestions in the sheet.

3. Try one cell

   In cell B2, type =CELSIUS_TO_FAHRENHEIT(A2) and press Enter. With 100 in A2 you get 212.

4. See what an invalid range does

   In cell C2, type =CELSIUS_TO_FAHRENHEIT(A2:A20). Because the range includes warm, the function throws an error. C2 shows the error and no values spill into the cells below it.

5. Remove the mistake and spill the results

   Clear A5, then enter =CELSIUS_TO_FAHRENHEIT(A2:A20) in C2 again. One formula fills the results down the column, and blank cells stay blank. To adapt the function, rename it and replace the calculation. Keep the blank check and the number check so bad values give a clear message instead of a wrong answer.

5 · Confirm

How to tell it worked

With 100 in A2, =CELSIUS_TO_FAHRENHEIT(A2) shows 212. With warm in A5, =CELSIUS_TO_FAHRENHEIT(A2:A20) shows an error in C2 and does not spill. Clear A5 and enter the formula again to get results for each number while blank cells stay blank.

If the result is different

Troubleshooting

The cell shows #NAME?

Sheets does not know the name yet. Save the Apps Script project, check the spelling in the cell, and make sure the name does not end with an underscore (which marks functions private in Apps Script). The code must live in the script attached to this spreadsheet, not in a separate project.

The cell shows #ERROR! and the message about needing a number.

The throw new Error line produces this message. A cell you pointed at holds text, so fix the value or point the formula somewhere else. If it is in a range, the whole formula shows the error.

The cell shows "Exceeded maximum execution time".

A custom function gets about 30 seconds per call at the time of writing. See https://developers.google.com/apps-script/guides/services/quotas. Pass a smaller range, or split one huge formula into a few smaller ones.

The tooltip says "You do not have permission to call GmailApp.sendEmail".

Custom functions cannot use services that ask for access, such as Gmail or Drive. UrlFetchApp is allowed, so use it when a custom function needs to fetch a website.

The cell shows "TypeError: input.getValue is not a function".

Formulas pass raw cell values or 2D arrays, not SpreadsheetApp.Range objects. Read the cell values directly from the input argument instead of calling Range methods.