Fixing the #VALUE! Error in Custom JavaScript Functions (Sheets)

Quick reference: Advanced Logic troubleshooting

You wrote an Apps Script custom function: function CALC_TAX(amount, rate) returning amount * rate. Call it with =CALC_TAX(100, 0.07), expecting 7. The cell shows #VALUE!. The function exists in your Apps Script project. Other custom functions work. But this specific one returns #VALUE!. Custom JavaScript functions in Apps Script have a narrow set of valid return types and behaviors — and stepping outside them produces opaque errors.

Before You Start: The 60-Second Diagnostic

Before the fix: the failure mode this guide addresses

Three checks:

Test in the Apps Script editor: Use Logger.log(CALC_TAX(100, 0.07)) and run. If this errors, the function is broken in code, not just in sheet calls.
Check return type: Custom functions must return a primitive (number, string, boolean) or a 2D array — not objects.
Verify no async/await: Custom functions cannot use async operations.

Step-by-Step Solution

After the fix: the workbook restored to a healthy state

H2: Return Valid Types

Apps Script custom functions can return:

Numbers: return 42;
Strings: return "hello";
Booleans: return true;
2D arrays: return [[1, 2], [3, 4]]; (displays as a 2×2 grid)
Dates: return new Date(); (displays as date)
null/undefined: shows as empty cell

Invalid returns:

Objects: return {name: "John", age: 30}; produces #ERROR! or #VALUE!.
Functions: returning a function reference is invalid.
Promises: async returns are not unwrapped by Sheets.

Fix: ensure your function returns a valid primitive or array.

H2: Avoid Async/Await

Apps Script custom functions are synchronous only:

// Wrong:
async function GET_DATA() {
  const response = await fetch('https://api.example.com/data');
  return await response.text();
}

This appears valid JavaScript but Sheets does not await. The cell receives a Promise object, not the resolved value — and renders as #VALUE! or #ERROR!.

Fix: use synchronous APIs available in Apps Script:

function GET_DATA() {
  const response = UrlFetchApp.fetch('https://api.example.com/data');
  return response.getContentText();
}

UrlFetchApp.fetch is synchronous. The function works in cell calls.

H2: Handle Execution Time Limits

Custom functions must complete within 30 seconds. If they exceed this:

The first call returns #ERROR! after 30s.
Subsequent calls may use cached results (if available).
For slow operations (heavy API calls, large data), the call always fails.

Workarounds:

Reduce scope: process less data per call.
Use trigger-based caching: a time-driven trigger pre-computes results into a cell; custom functions read from that cell instead of running live.
Use Sidebar UI: for interactive long-running operations, use HTML sidebars instead of custom functions.

H2: Pass Cell Ranges as 2D Arrays

When a custom function receives a range, it arrives as a 2D array:

function SUM_VISIBLE(range) {
  // range is a 2D array like [[1, 2], [3, 4]]
  let sum = 0;
  for (let row of range) {
    for (let val of row) {
      if (typeof val === 'number') sum += val;
    }
  }
  return sum;
}

Calling =SUM_VISIBLE(A1:B2) passes A1:B2 as [[A1value, B1value], [A2value, B2value]].

Single cells arrive as scalars, not arrays — handle both cases:

function CUSTOM_OP(input) {
  if (Array.isArray(input)) {
    // It's a range
    return input.map(row => row.map(v => v * 2));
  } else {
    // It's a single cell
    return input * 2;
  }
}

H2: Debug via Logger

Use Logger.log to debug:

function CALC_TAX(amount, rate) {
  Logger.log("amount: " + amount + ", rate: " + rate);
  Logger.log("typeof amount: " + typeof amount);
  return amount * rate;
}

Then in the Apps Script editor: View → Logs (or Execution → Logs). After calling the function from the sheet, the logs show what values were actually received.

Logs reveal type mismatches and unexpected behavior. Most “why is this #VALUE!?” investigations resolve with one logging session.

Information Gain Box: The Hidden Trigger-Based Caching Pattern

Here is the pattern that makes Apps Script custom functions actually production-ready: separate slow data fetching from fast cell calculation by using triggers to cache results.

The pattern:

Create a regular (non-custom) function that fetches and writes data:

function refreshCache() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Cache');
  const data = UrlFetchApp.fetch('https://api.example.com/rates').getContentText();
  const rates = JSON.parse(data);
  for (let [currency, rate] of Object.entries(rates)) {
    // Write to cache sheet
    sheet.appendRow([currency, rate, new Date()]);
  }
}

Add a time-driven trigger: Triggers → Add Trigger → refreshCache → Time-driven → Every hour.

Build a custom function that reads from the cache:

function GET_RATE(currency) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Cache');
  const data = sheet.getDataRange().getValues();
  for (let row of data) {
    if (row[0] === currency) return row[1];
  }
  return "Not found";
}

The custom function is fast (just reads cells) and never times out. The slow fetch happens on a schedule, hidden from the user. End users see fresh data without slow custom-function calls.

This pattern is the foundation of every production-grade Google Sheets dashboard using external data. It is documented in Apps Script reference but rarely taught as a primary pattern. Adopt it from day one for any data-fetching functionality.

Comparison Table: Wrong Way vs. Correct Way

Issue	Wrong Way (#VALUE!)	Correct Way
Async functions	`async function() { await ... }`	Synchronous: `UrlFetchApp.fetch()`
Return objects	`return {data: 1}`	Return primitive or 2D array
Long-running fetch	Direct in custom function	Trigger-based cache + read from cells
Range vs cell input	Assume one type	Check `Array.isArray()` for both cases
Diagnostic	Trust the `#VALUE!`	Add Logger.log and check executions log
Performance	Live API call per cell	Pre-fetch with trigger, read cached values
Error handling	Hope for the best	try/catch with error return string

Original Image Descriptions

Screenshot 1: Show a Google Sheets cell with =CALC_TAX(100, 0.07) displaying #VALUE!. The Apps Script editor is open in a side window showing the broken async function. Draw a red circle around the async keyword and a red arrow to the cell error. Add a red annotation: “Custom functions cannot be async — Sheets doesn’t await Promises.”

Screenshot 2: Show the corrected synchronous function in the editor and the cell now displaying 7 correctly. Beside, the Logger output shows the parameter values that were passed. Draw a red circle around the synchronous return statement. Add a red annotation: “Synchronous + valid return type = working custom function.”

Frequently Asked Questions

Q: How long do custom function results stay cached?
A: Google Sheets caches custom function results based on the inputs. If you call =CALC_TAX(100, 0.07) twice in a row, the second call returns the cached result instantly. The cache persists until the inputs change or the workbook is reloaded. For functions that depend on external data (like API calls), the cache means the data may be stale; force-refresh by changing a parameter or pressing Ctrl+Shift+F5.

Q: Can I read other cells from within a custom function?
A: Yes, but with caveats. SpreadsheetApp.getActiveSpreadsheet() works but is slow — each call to it forces re-reading the spreadsheet. For performance, pass ranges as function arguments (Sheets caches the read) rather than reading inside the function. Cross-sheet reads via SpreadsheetApp also require authorization, complicating distribution.

Q: Why does my function work in the Apps Script editor but not from a cell?
A: Editor calls run with full user permissions; cell-called custom functions run with restricted permissions. Common limitations: no UrlFetchApp without prior authorization, no SpreadsheetApp.getUi (no dialogs), no async operations, 30-second time limit. Audit your function for any of these.

Related guides in Advanced Logic

Browse the full Advanced Logic library or return to the byyours homepage.