I am attempting to compute the contrast ratio between two colors in Google Sheets using a custom function developed in Apps Script. The function called testContrastRatio works correctly in the Apps Script editor, but when I use the ContrastRatio function directly in a Google Sheets cell, I get the error: ERROR Parsing Formula.
Google Sheets Configuration:
I have the following hex color codes:
- A1: #000000
- B1: #FFFFFF
I call the custom function in cell C1 like this: =ContrastRatio(A1, B1).
Apps Script Code: Here’s the code I’m using:
/**
* Calculates the contrast ratio between two colors.
* @param {string} colorA The first color in hex format (e.g., '#FFFFFF').
* @param {string} colorB The second color in hex format (e.g., '#000000').
* @return {number|string} The calculated contrast ratio or an error message.
* @customfunction
*/
function ColorContrast(colorA, colorB) {
if (typeof colorA !== 'string' || typeof colorB !== 'string') {
return 'Inputs must be valid hex color strings.';
}
colorA = colorA.trim();
colorB = colorB.trim();
const hexCheck = /^#[0-9A-Fa-f]{6}$/;
if (!hexCheck.test(colorA) || !hexCheck.test(colorB)) {
return 'Invalid hex code(s).';
}
const convertHexToRgb = (hex) => [parseInt(hex.slice(1, 3), 16), parseInt(hex.slice(3, 5), 16), parseInt(hex.slice(5, 7), 16)];
const rgbA = convertHexToRgb(colorA);
const rgbB = convertHexToRgb(colorB);
const luminanceCalc = (value) => { value /= 255; return value <= 0.03928 ? value / 12.92 : Math.pow((value + 0.055) / 1.055, 2.4); };
const computeLuminance = (rgb) => 0.2126 * luminanceCalc(rgb[0]) + 0.7152 * luminanceCalc(rgb[1]) + 0.0722 * luminanceCalc(rgb[2]);
const luminance1 = computeLuminance(rgbA);
const luminance2 = computeLuminance(rgbB);
return luminance1 > luminance2 ? (luminance1 + 0.05) / (luminance2 + 0.05) : (luminance2 + 0.05) / (luminance1 + 0.05);
}
function checkColorContrast() {
let output = ColorContrast('#FFFFFF', '#000000');
console.log(output); // Expecting output: '21'
}
When running checkColorContrast in the Apps Script, it correctly returns 21 for black and white contrast. However, using =ColorContrast(A1, B1) directly in Sheets generates a parsing error, and the same happens with =ColorContrast('#000000', '#FFFFFF').
What I’ve Attempted:
I have confirmed that cells A1 and B1 contain valid hex codes (#000000 and #FFFFFF).
I added logging for debugging:
console.log('ColorA:', colorA);
console.log('ColorB:', colorB);
The logs correctly display values when executed from Apps Script. I also checked for any leading spaces or invalid characters in the input cells.