Skip to content

Instantly share code, notes, and snippets.

@adamchaboryk

adamchaboryk/google-sheets-contrast-checking-with-colour-preview.js

Forked from wjmazza/google-sheets-colour-preview.js
Last active November 9, 2023 12:07
Show Gist options
  • Save adamchaboryk/0dd2529ea9b8480f21905aaf11d1b5a5 to your computer and use it in GitHub Desktop.
Save adamchaboryk/0dd2529ea9b8480f21905aaf11d1b5a5 to your computer and use it in GitHub Desktop.
Download ZIP
Google Sheets script: WCAG 2 contrast checker with colour preview via hexcodes.
Raw
google-sheets-contrast-checking-with-colour-preview.js
/*
To use this script in a Google Sheets spreadsheet:
1. Open Google Sheets, go to Extensions > App Scripts.
2. Erase everything in the text editor;
3. Change the title to "Check WCAG 2 Contrast";
4. Paste this code in;
5. Click File » Save.
Google Sheets Colour Preview script:
Original gist: https://gist.github.com/Pathoschild/29ad5ec92348164b1dea
Modifications: https://gist.github.com/wjmazza/131c050b88bb2a595d6049707693ec13
My changes: Modified so that # is not needed. */
// Globals
const sheet = SpreadsheetApp.getActiveSheet();
// A regex pattern matching a valid CSS hex colour code.
const colourPattern = /^([0-9a-f]{3})([0-9a-f]{3})?$/i;
// Sets the foreground or background color of a cell based on its value. This assumes a valid CSS hexadecimal colour code like #FFF or #FFFFFF.
function onEdit(e){
const range = e.range;
const data = range.getValues();
const rowCount = range.getNumRows();
const colCount = range.getNumColumns();
for(let r = 0; r < rowCount; ++r) {
for(let c = 0; c < colCount; ++c) {
const value = data[r][c];
const cell = range.getCell(r + 1, c + 1);
if(isValidHex(value)) {
cell.setBackground(`#${value}`);
cell.setFontColor(getContrastYIQ(`#${value}`));
cell.setFontFamily('Consolas');
cell.setBorder(false, false, false, false, false, false);
} else {
cell.clear({
formatOnly: true,
contentsOnly: false
});
cell.setBackground('white');
cell.setFontColor('black');
cell.setFontFamily('Arial');
}
}
}
};
//Get whether a value is a valid hex colour code.
function isValidHex(hex) {
return colourPattern.test(hex);
};
// Change text color to white or black depending on YIQ contrast: https://24ways.org/2010/calculating-color-contrast/
function getContrastYIQ(hexcolor){
const r = parseInt(hexcolor.substr(1,2),16);
const g = parseInt(hexcolor.substr(3,2),16);
const b = parseInt(hexcolor.substr(5,2),16);
const yiq = ((r*299)+(g*587)+(b*114))/1000;
return (yiq >= 128) ? 'black' : 'white';
}
/* Determine WCAG contrast ratio
Contrast checking logic credit: https://codepen.io/alvaromontoro/pen/qBBeaJa */
//Convert to RGB
function hexToRgb(hex) {
if (isValidHex(hex)) {
const shorthandRegex = /^([a-f\d])([a-f\d])([a-f\d])$/i;
hex = hex.replace(shorthandRegex, function(m, r, g, b) {
return r + r + g + g + b + b;
});
const result = /^([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(hex);
return result ? {
r: parseInt(result[1], 16).toFixed(0),
g: parseInt(result[2], 16).toFixed(0),
b: parseInt(result[3], 16).toFixed(0)
} : null;
}
}
// function from https://stackoverflow.com/a/9733420/3695983
function luminance(r, g, b) {
const a = [r, g, b].map(function (v) {
v /= 255;
return v <= 0.03928
? v / 12.92
: Math.pow( (v + 0.055) / 1.055, 2.4 );
});
return a[0] * 0.2126 + a[1] * 0.7152 + a[2] * 0.0722;
}
function ratio() {
// Grab values from column A and B.
const color1 = sheet.getRange('A2:A').getValues();
const color2 = sheet.getRange('B2:B').getValues();
// Remove empty cells.
const fg = color1.reduce(function(ar, e) {
if (e[0]) ar.push(e[0])
return ar;
}, []);
const bg = color2.reduce(function(ar, e) {
if (e[0]) ar.push(e[0])
return ar;
}, []);
//Iterate through *valid* hexcodes.
for (let i = 0; i < fg.length || i < bg.length; i++) {
if (isValidHex(fg[i]) && isValidHex(bg[i])) {
// Validate hexcodes.
const fgRGB = hexToRgb(fg[i]);
const bgRGB = hexToRgb(bg[i]);
// Calculate luminance
const lum1 = luminance(fgRGB.r, fgRGB.g, fgRGB.b);
const lum2 = luminance(bgRGB.r, bgRGB.g, bgRGB.b);
// Contrast ratio
const ratioRaw = (Math.max(lum1, lum2) + 0.05) / (Math.min(lum1, lum2) + 0.05);
const ratio = ratioRaw.toFixed(2);
// Set ratio column
const ratioCol = SpreadsheetApp.getActiveSheet().getRange(2 + i, 3);
ratioCol.setValue(ratio);
const normalText = SpreadsheetApp.getActiveSheet().getRange(2 + i, 4);
const largeText = SpreadsheetApp.getActiveSheet().getRange(2 + i, 5);
const normalAAA = SpreadsheetApp.getActiveSheet().getRange(2 + i, 6);
const largeAAA = SpreadsheetApp.getActiveSheet().getRange(2 + i, 7);
// Normal && Large AAA
if (ratio >= 4.5) {
normalText.setValue("Pass");
largeAAA.setValue("Pass");
} else {
normalText.setValue("Fail");
largeAAA.setValue("Fail");
}
// Large
if (ratio >= 3) {
largeText.setValue("Pass");
} else {
largeText.setValue("Fail");
}
// Normal AAA
if (ratio >= 7) {
normalAAA.setValue("Pass");
} else {
normalAAA.setValue("Fail");
}
}
}
}
ratio();
// Global conditional styling because conditional formatting doesn't work properly.
const range = sheet.getRange("C:G");
range.setHorizontalAlignment("center");
const rule = SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo("Pass")
.setFontColor("#00850a")
.setBold(true)
.setRanges([range])
.build();
const rule2 = SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo("Fail")
.setFontColor("#848484")
.setRanges([range])
.build();
const rule3 = SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween(1, 2.99)
.setFontColor("#cc0000")
.setRanges([range])
.build();
const rules = sheet.getConditionalFormatRules();
rules.push(rule, rule2, rule3);
sheet.setConditionalFormatRules(rules);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment