Google Sheets Custom Function returns "ERROR Parsing Formula"

I am trying to calculate the contrast ratio between two colors in Google Sheets using a custom function written in Apps Script. While the test function testContrastRatio works perfectly in the Apps Script editor, calling the function ContrastRatio directly in a Google Sheets cell results in the error: ERROR Parsing Formula.

Here’s what I’ve done:

Google Sheets Setup:

I have two hex color codes in cells A1 and B1:

A1: #000000

B1: #FFFFFF

I call the custom function as follows in a cell (e.g., C1): =ContrastRatio(A1, B1)

Apps Script Code

Here is the code I am using for the custom function:

/**
 * Calculates the contrast ratio between two colors.
 * @param {string} color1 The first color in hex format (e.g., "#FFFFFF").
 * @param {string} color2 The second color in hex format (e.g., "#000000").
 * @return {number|string} The contrast ratio or an error message.
 * @customfunction
 */
function ContrastRatio(color1, color2) {
  // Ensure inputs are strings
  if (typeof color1 !== 'string' || typeof color2 !== 'string') {
    return "Inputs must be valid hex color strings.";
  }

  // Trim whitespace
  color1 = color1.trim();
  color2 = color2.trim();

  // Validate hex codes
  const isValidHex = /^#[0-9A-Fa-f]{6}$/i;
  if (!isValidHex.test(color1) || !isValidHex.test(color2)) {
    return "Invalid hex code(s).";
  }

  // Convert hex to RGB
  const hexToRgb = (hex) => [
    parseInt(hex.slice(1, 3), 16),
    parseInt(hex.slice(3, 5), 16),
    parseInt(hex.slice(5, 7), 16)
  ];

  const rgb1 = hexToRgb(color1);
  const rgb2 = hexToRgb(color2);

  // Calculate relative luminance
  const luminanceComponent = (value) => {
    value /= 255;
    return value <= 0.03928 ? value / 12.92 : Math.pow((value + 0.055) / 1.055, 2.4);
  };

  const calculateLuminance = (rgb) =>
    0.2126 * luminanceComponent(rgb[0]) +
    0.7152 * luminanceComponent(rgb[1]) +
    0.0722 * luminanceComponent(rgb[2]);

  const L1 = calculateLuminance(rgb1);
  const L2 = calculateLuminance(rgb2);

  // Return contrast ratio
  return L1 > L2 ? (L1 + 0.05) / (L2 + 0.05) : (L2 + 0.05) / (L1 + 0.05);
}

/**
 * Test function to verify ContrastRatio.
 */
function testContrastRatio() {
  let result = ContrastRatio("#FFFFFF", "#000000");
  console.log(result); // Should log: "21"
}

Running the test function testContrastRatio in the Apps Script editor returns the expected result (21 for black vs white contrast). If I hardcode values into the function call within Apps Script, it also works.

When calling =ContrastRatio(A1, B1) directly in Google Sheets, I get the parsing error. And =ContrastRatio("#000000", "#FFFFFF") gives the same parsing error.

What I’ve Tried

Verified that both cells (A1 and B1) contain valid hex codes (#000000 and #FFFFFF).

Added logging to debug inputs:

console.log("Color1:", color1);
console.log("Color2:", color2);

The logs show correct values when tested via Apps Script.

Checked for extra spaces or invalid characters in the input cells.

Locale timezone affects formula

I have tried your code and it is working as intended. I suspect that you have a different locale time zone with your spreadsheet from your apps script time zone, which can be a factor with parsing a formula.

So far there are two different delimiters which are 1. comma , (commonly used in US) and a 2. semicolon ; commonly used in European countries.

You can try to change your locale timezone following these steps:

Go to File -> settings -> locale -> US 

Or you can try this Google Apps script code:

function changeLocale() {
 SpreadsheetApp.getActiveSpreadsheet().setSpreadsheetLocale("en_US");
}

This Google apps script will convert your spreadsheet's time zone into US in which you can now use comma,, as a delimiter. If you wish to turn it into a semicolon ; or a europe based locale, you can change the code from "en_US" to "UK" (you can try different country code as per your preference)

For more information you can visit this: Explaining syntax differences in your formulas due to your Google Sheets location