google apps script - Set the background color to a cell based on a hex value

PHOTO

Mon Aug 12 2024 12:23:41 GMT+0000 (Coordinated Universal Time)

/*

This script is meant to be used with a Google Sheets spreadsheet. When you edit a cell containing a
valid CSS hexadecimal colour code (like #000 or #000000), the background colour will be changed to
that colour and the font colour will be changed to the inverse colour for readability.

To use this script in a Google Sheets spreadsheet:
1. go to Tools » Script Editor » Spreadsheet;
2. erase everything in the text editor;
3. change the title to "Set colour preview on edit";
4. paste this code in;
5. click File » Save.
*/

/*********
** Properties
*********/
/**
 * A regex pattern matching a valid CSS hex colour code.
 */
var colourPattern = /^#([0-9a-f]{3})([0-9a-f]{3})?$/i;


/*********
** Event handlers
*********/
/**
 * 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){
  // iterate over cell range  
  var range = e.range;
  var rowCount = range.getNumRows();
  var colCount = range.getNumColumns();
  for(var r = 1; r <= rowCount; r++) {
    for(var c = 1; c <= colCount; c++) {
      var cell = range.getCell(r, c);
      var value = cell.getValue();

      if(isValidHex(value)) {
        cell.setBackground(value);
        cell.setFontColor(getContrastYIQ(value));
      }
      else {
        cell.setBackground('white');
        cell.setFontColor('black');
      }
    }
  }
};


/*********
** Helpers
*********/
/**
 * 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){
    var r = parseInt(hexcolor.substr(1,2),16);
    var g = parseInt(hexcolor.substr(3,2),16);
    var b = parseInt(hexcolor.substr(5,2),16);
    var yiq = ((r*299)+(g*587)+(b*114))/1000;
    return (yiq >= 128) ? 'black' : 'white';
}

COPY

https://stackoverflow.com/questions/46427564/set-the-background-color-to-a-cell-based-on-a-hex-value

Save snippets that work from anywhere online with our extensions

Comments

Sheets

@baamn

Use the COUNTIF formula to find duplicates. google apps script - Set the background color to a cell based on a hex value Combine Cells in Google Sheets - Using Ampersand (&) Operator Google Spreadsheet: Script to change background color of a cell based on a hex code in the neighboring cell - Stack Overflow

#html #javascript #nodejs

How to Save HTML Form Data in JSON - Express

<form action="/new" method="post">
 
  <input name="title" type="text">
  <input name="description" type="text">
  <button type="submit">Submit Form</button>
 
</form>

> More steps

#javascript

Get URL and URL Parts in JavaScript

var newURL = window.location.protocol + "//" + window.location.host + "/" + window.location.pathname + window.location.search

#javascript

Add event listener to multiple buttons with the same class

    btns = document.getElementsByClassName("saveBtn");
    for (var i = 0; i < btns.length; i++) {
        btns[i].addEventListener("click", function () {
			//Add function here
        });
    }

#javascript

Replace Textarea with Codemirror editor

<link rel="stylesheet" type="text/css" href="plugin/codemirror/lib/codemirror.css">

<body>
	<textarea class="codemirror-textarea"></textarea>
</body>

<script>

$(document).ready(function(){
    var codeText = $(".codemirror-textarea")[0];
    var editor = CodeMirror.fromTextArea(codeText, {
        lineNumbers : true
    });
});

</script>

<script type="text/javascript" src="plugin/codemirror/lib/codemirror.js"></script>

#javascript #nodejs #commandline

HTTPS Redirect in Nodejs

npm install heroku-ssl-redirect

> More steps

#javascript #nodejs

Save to MongoDB without Duplicates Using Mongoose

var mongoose = require('mongoose');
var Schema = mongoose.Schema;

const exampleSchema = new Schema({
    title: { type: String , required: true},
    content: [{type: String}]
});


var Example = mongoose.model('Example', exampleSchema);
module.exports = Example;

> More steps

#html #javascript #jsfunctions #dom #dommanipulation

getAttribute() - how to get the value of any attribute of HTML element

<html>

<input id="contact" name="address">

<script>

    var x = document.getElementById("contact").getAttribute('name');

</script>

</html>

#javascript

Full Stack Developer

function full_stack_developer() {
    full_stack_developer();
}

#javascript #javascript #functions #parameters

Required Parameters for Functions in JavaScript

 const isRequired = () => { throw new Error('param is required'); };

const hello = (name = isRequired()) => { console.log(`hello ${name}`) };

// These will throw errors
hello();
hello(undefined);

// These will not
hello(null);
hello('David');
The idea here is that it uses default parameters, like how the b parameter here has a default if you don’t send it anything:
function multiply(a, b = 1) {
  return a * b;
}

#javascript

3 Ways To Detect Selenium Bots Using Javascript - CodingTutz

if (navigator.webdriver) {
    document.body.innerHTML = "This is a Bot";
}

#javascript

Copy Text to Clipboard with Line Breaks

function copyToClipboard(){

    var codeToBeCopied = document.getElementById('code-snippet').innerText;
    var emptyArea = document.createElement('TEXTAREA');
    emptyArea.innerHTML = codeToBeCopied;
    const parentElement = document.getElementById('post-title');
    parentElement.appendChild(emptyArea);

    emptyArea.select();
    document.execCommand('copy');

    parentElement.removeChild(emptyArea);
    M.toast({html: 'Code copied to clipboard'})

    }

#javascript #promises #howto

How to Get the Domain From A URL Using Javascript

var url = "http://scratch99.com/web-development/javascript/";
var urlParts = url.replace('http://','').replace('https://','').split(/[/?#]/);
var domain = urlParts[0];

#javascript

Mapping an Object

const object1 = {
  a: 'somestring',
  b: 42
};

for (let [key, value] of Object.entries(object1)) {
  console.log(`${key}: ${value}`);
}

#javascript #jquery

jQuery.post() - AJAX for post requests

$.ajax({
  type: "POST",
  url: url,
  data: data,
  success: success,
  dataType: dataType
});

#javascript

How to store fetch response in javascript variable - Stack Overflow

async function fun() {
  return fetch('https://jsonplaceholder.typicode.com/todos/1').then(res => res.json());
}

const data  = await fun();

google apps script - Set the background color to a cell based on a hex value

Save snippets that work from anywhere online with our extensions

Comments

More like this

Sheets

Browse more snippets >>

google apps script - Set the background color to a cell based on a hex value

Save snippets that work from anywhere online with our extensions

Comments

More like this

Sheets

Browse more snippets >>

Embed code snippet