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.
• Open the script from that spreadsheet with Extensions, 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

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

   Choose Extensions, then Apps Script. Paste the code over the starter 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. You do not need to click Run.

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 a bad value does

   In cell C2, type =CELSIUS_TO_FAHRENHEIT(A2:A20) and press Enter. Because the range includes the word warm, C2 shows #ERROR! and no values spill into the cells below it. Hover over C2 to read the message.

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 in both places 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?

Save the Apps Script project, check the spelling in the cell, and make sure the function name does not end with an underscore, which marks a function private. The code must be in the script attached to this spreadsheet, not in a separate project.

The cell shows #ERROR! with "CELSIUS_TO_FAHRENHEIT needs a number."

Fix the cell the message names, or point the formula somewhere else. A cell in the range holds text, and one bad cell makes the whole formula show the error.

The cell shows "Exceeded maximum execution time".

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

The cell shows "You do not have permission to call GmailApp.sendEmail".

Move that work into a normal function that you run from a menu or a trigger. A custom function cannot use services that ask for access, such as Gmail or Drive. UrlFetchApp is allowed.

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

Read the values straight from the input argument instead of calling Range methods on it. A formula passes cell values or a 2D array of them, never a Range object.