-
-
Save sokqa/a4c6f2042d48c240802805d9122088e1 to your computer and use it in GitHub Desktop.
Google Sheets script to allow multi-select in cells with data-validation
This file contains hidden or 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 xmlns="http://www.w3.org/1999/html"> | |
| <head> | |
| <!--Bootstrap--> | |
| <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" | |
| integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous"> | |
| <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" | |
| integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" | |
| crossorigin="anonymous"></script> | |
| <!--Drawing right sidebar--> | |
| <script> | |
| let validationData; | |
| const formId = 'form'; | |
| function drawForm() { | |
| let outputHtml; | |
| if (!validationData || !Array.isArray(validationData)) { | |
| outputHtml = '<p>This cell has no <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation</a>.</p>'; | |
| } else { | |
| outputHtml = getFormHtmlFromValidationData(validationData); | |
| } | |
| const outputElement = document.getElementById(formId); | |
| outputElement.innerHTML = outputHtml; | |
| } | |
| function getFormHtmlFromValidationData(data) { | |
| let formHtml = ''; | |
| for (let validationElement of data) { | |
| if (!validationElement[0]) { | |
| continue; | |
| } | |
| formHtml += createInputLine(validationElement); | |
| formHtml += '<br>'; | |
| } | |
| return formHtml; | |
| } | |
| function createInputLine(validationElement) { | |
| const validationValue = validationElement[0]; | |
| return `<input type='checkbox' class='form-check-input' name='${validationValue}' value='${validationValue}'> ${validationValue}`; | |
| } | |
| function onData(result) { | |
| validationData = result; | |
| drawForm(); | |
| } | |
| google.script.run.withSuccessHandler(onData).getValidationData(); | |
| function set() { | |
| google.script.run.withSuccessHandler(_ => {}).fillCell(document.getElementById(formId)); | |
| } | |
| function update() { | |
| google.script.run.withSuccessHandler(_ => {}).updateCell(document.getElementById(formId)); | |
| } | |
| function reset() { | |
| drawForm(); | |
| } | |
| </script> | |
| </head> | |
| <body> | |
| <div style="padding-left: 10px"> | |
| <div style="padding-top: 10px"> | |
| <button type="button" class="btn btn-success btn-sm" onclick="set()">Set</button> | |
| <button type="button" class="btn btn-info btn-sm" onclick="update()">Update</button> | |
| <button type="button" class="btn btn-warning btn-sm" onclick="reset()">Reset</button> | |
| <button type="button" class="btn btn-secondary btn-sm" onclick="google.script.run.showDialog()">Refresh</button> | |
| </div> | |
| <div style="padding-top: 10px"> | |
| <form id="form" name="form"> | |
| </form> | |
| </div> | |
| </div> | |
| </body> | |
| <html> |
This file contains hidden or 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() { | |
| const html = HtmlService.createHtmlOutputFromFile('dialog').setSandboxMode(HtmlService.SandboxMode.IFRAME); | |
| SpreadsheetApp.getUi().showSidebar(html); | |
| } | |
| function getValidationData() { | |
| try { | |
| return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); | |
| } catch (exception) { | |
| Logger.log(exception); | |
| Logger.log("No validation data found for active range."); | |
| return null; | |
| } | |
| } | |
| function setMyValues(formData, update) { | |
| const selectedValues = Object.keys(formData); | |
| if (selectedValues.length === 0) { | |
| return; | |
| } | |
| selectedValues.sort(); | |
| const separator = ', '; | |
| const activeRange = SpreadsheetApp.getActiveRange(); | |
| if (!update) { | |
| activeRange.setValue(selectedValues.join(separator)); | |
| } else { | |
| updateRangeCells(selectedValues, separator, activeRange); | |
| } | |
| } | |
| function updateRangeCells(selectedValues, separator, activeRange) { | |
| const rangeValues = activeRange.getValues() | |
| for (let row = 0; row < rangeValues.length; row++) { | |
| for (let column = 0; column < rangeValues[row].length; column++) { | |
| const cellValue = rangeValues[row][column]; | |
| const cellValueElements = cellValue.split(separator); | |
| cellValueElements.push(...selectedValues); | |
| const uniqueValues = Array.from(new Set(cellValueElements)); | |
| uniqueValues.sort(); | |
| activeRange.getCell(row + 1, column + 1).setValue(uniqueValues.join(separator)); | |
| } | |
| } | |
| } | |
| function updateCell(formData) { | |
| return setMyValues(formData, true); | |
| } | |
| function fillCell(formData) { | |
| setMyValues(formData); | |
| } |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Forked from here. Refactored code to resemble industry-like JavaScript & added some styling to the HTML part. Enjoy!