-
-
Save hraban/0a79392fecd213be143ef4f3f834b9f5 to your computer and use it in GitHub Desktop.
Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <script> | |
| // https://github.com/reaxis/mu | |
| (function(d){"object"===typeof exports?module.exports=d():"function"===typeof define&&define.amd?define(d):mu=µ=d()})(function(){function d(){}function m(a){return a.replace(/-(.)/g,function(a,c){return c.toUpperCase()})}function k(a){return"[object Array]"==={}.toString.call(a)}function g(a){return[].slice.call(a)}d.one=function(a){return document.querySelector(a)};d.all=function(a){return g(document.querySelectorAll(a))};d.create=function(a){return document.createElement(a)};var h={one:function(a){return this.querySelector(a)},all:function(a){return g(this.querySelectorAll(a))},each:function(a){a.bind(this)(0);return this},"µAddEventListener":Node.prototype.addEventListener,addEventListener:function(){this.µEventCache=this.µEventCache||[];this.µEventCache.push(arguments);this.µAddEventListener.apply(this,arguments)},on:function(a,b){this.addEventListener(a,b);return this},add:function(){return g(arguments).reduce(function(a,b){k(b)?a.add.apply(a,b):a.appendChild(b.nodeType?b:document.createTextNode(b));return a},this)},css:function(a){if("string"===typeof a)return this.style[m(a)];for(var b in a)this.style[m(b)]=a[b];return this},attr:function(a){if("string"===typeof a)return this.getAttribute(a);for(var b in a)this.setAttribute(b,a[b]);return this},cls:function(){this.classList.add.apply(this.classList,arguments);return this},empty:function(){this.innerHTML="";return this},remove:function(){return this.parentNode.removeChild(this)},text:function(a){if("undefined"===typeof a)return this.textContent;this.textContent=a;return this},copy:function(){var a=this.cloneNode(!1);this.µEventCache&&this.µEventCache.each(function(){a.addEventListener.apply(a,this)});return a.add.apply(a,g(this.childNodes))}},e;for(e in h)Node.prototype[e]=h[e];Window.prototype.on=Node.prototype.on;h={each:function(a){this.forEach(function(b,c){a.bind(b)(c)});return this},one:function(a){var b=[];this.each(function(){this.querySelector(a)&&b.push(this.querySelector(a))});return b},all:function(a){var b=[];this.each(function(){b=b.concat(g(this.querySelectorAll(a)))});return b},add:function(){var a=arguments;return this.each(function(){return g(a).reduce(function(a,c){k(c)?a.add.apply(a,c.copy()):a.appendChild(c.nodeType?c.copy():document.createTextNode(c));return a},this)})}};for(e in h)Array.prototype[e]=h[e];"on css attr cls empty remove text copy".split(" ").each(function(){var a=this+"";Array.prototype[a]=function(){var b=arguments,c=[];this.each(function(){c.push(this[a].apply(this,b))});return c}});var l={img:["src","alt","title"],a:["href"],input:["type","name","value"],option:["value"],abbr:["title"],canvas:["width","height"]};e={ul:"li",ol:"li",tr:"td",table:"tr"};"section nav article aside header footer address main div span p strong em h1 h2 h3 h4 h5 h6 li td".split(" ").each(function(){l[this]=[]});for(var f in l)d[f]=d[f.toUpperCase()]=function(a,b){return function(){var c=g(arguments);return d.create(a).attr(b.reduce(function(a,b){c.length&&(a[b]=c.shift());return a},{})).add(c)}}(f,l[f]);for(f in e)d[f]=d[f.toUpperCase()]=function(a,b){return function(){return d.create(a).add(g(arguments).map(function(a){return d[b].apply(null,k(a)?a:[a])}))}}(f,e[f]);return d}); | |
| </script> | |
| <script> | |
| function isArray(o) { | |
| return Object.prototype.toString.call(o) === '[object Array]'; | |
| } | |
| var data; | |
| var formId = 'form'; | |
| function drawFormAux() { | |
| if (!data) return; | |
| if (!isArray(data)) { | |
| return µ.p('This cell has no ', µ.a('https://support.google.com/drive/answer/139705?hl=en', 'Data validation')); | |
| } | |
| const inputs = data.map(([head, ...tail]) => { | |
| if (!head) return; | |
| const suffix = tail.filter(x => x).join('; '); | |
| const label = head + (suffix ? ` - ${suffix}` : ''); | |
| return [µ.create('label').add(µ.input('checkbox', head, head), ' ', label), µ.create('br')]; | |
| }).filter(x => x); | |
| return µ.div(inputs); | |
| } | |
| function drawForm() { | |
| var outputEl = document.getElementById(formId); | |
| try { | |
| const node = drawFormAux(); | |
| if (node) { | |
| outputEl.innerHTML = node.outerHTML; | |
| } | |
| } catch (e) { | |
| outputEl.innerHTML = µ.div(µ.h1(e.message), µ.pre(e.stack)).innerHTML; | |
| } | |
| } | |
| function onData(result) { | |
| data = result | |
| drawForm(); | |
| } | |
| google.script.run | |
| .withSuccessHandler(onData) | |
| .getValidationData(); | |
| function set() { | |
| google.script.run | |
| .withSuccessHandler(x=>{}) | |
| .fillCell(document.getElementById(formId)) | |
| } | |
| function update() { | |
| google.script.run.withSuccessHandler(x=>{ | |
| }).updateCell(document.getElementById(formId)) | |
| } | |
| function reset() { | |
| drawForm() | |
| } | |
| </script> | |
| </head> | |
| <body> | |
| <div style='position:fixed; margin-top: 10px; background-color: white; height: 30px; width: 100%; top: 0;'> | |
| <input type="button" value="Set" onclick="set()" /> | |
| <input type="button" value="Update" onclick="update()" /> | |
| <input type="button" value="Reset" onclick="reset()" /> | |
| <input type="button" value="Refresh validation" onclick="google.script.run.showDialog()" /> | |
| </div> | |
| <div style="font-family: sans-serif; margin-top: 30px;"> | |
| <form id="form" name="form"> | |
| </form> | |
| </div> | |
| </body> | |
| <html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function onOpen(e) { | |
| SpreadsheetApp.getUi() | |
| .createMenu('Scripts') | |
| .addItem('Multi-select for this cell', 'showDialog') | |
| .addToUi(); | |
| } | |
| function showDialog() { | |
| var html = HtmlService.createHtmlOutputFromFile('dialog').setSandboxMode(HtmlService.SandboxMode.IFRAME); | |
| SpreadsheetApp.getUi() | |
| .showSidebar(html); | |
| } | |
| function getValidationData(){ | |
| try { | |
| return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); | |
| } catch(e) { | |
| return null | |
| } | |
| } | |
| function setValues_(e, update) { | |
| var separator = ', ' | |
| var selectedValues = Object.keys(e); | |
| var total = selectedValues.length; | |
| if (total === 0) { | |
| return; | |
| } | |
| var range = SpreadsheetApp.getActiveRange() | |
| var value = selectedValues.join(separator) | |
| if (!update) { | |
| range.setValue(value); | |
| return; | |
| } | |
| var values = range.getValues() | |
| // check every cell in range | |
| for (var row = 0; row < values.length; ++row) { | |
| for (var column = 0; column < values[row].length; ++column) { | |
| //typeof values[row][column] === Array ? values[row][column].split(separator) : [values[row][column]+''] | |
| var currentValues = values[row][column].split(separator); | |
| var newValues = Array.from(new Set(currentValues)); | |
| if (newValues.length > 0) { | |
| range.getCell(row+1, column+1).setValue(newValues.join(separator)+separator+value) | |
| } else { | |
| range.getCell(row+1, column+1).setValue(value); | |
| } | |
| } | |
| } | |
| } | |
| function updateCell(e) { | |
| return setValues_(e, true) | |
| } | |
| function fillCell(e) { | |
| setValues_(e) | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment