Skip to content

Instantly share code, notes, and snippets.

@oshliaer

oshliaer/.md Secret

Created August 27, 2015 16:38
Show Gist options
  • Save oshliaer/30244dcf98825db217f9 to your computer and use it in GitHub Desktop.
Save oshliaer/30244dcf98825db217f9 to your computer and use it in GitHub Desktop.
Select several elements #gas #sheets
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Custom Menu')
.addItem('Show dialog', 'showDialog')
.addToUi();
}
function showDialog() {
var html = HtmlService.createTemplateFromFile('Page').evaluate();
SpreadsheetApp.getUi()
.showSidebar(html);
}
var valid = function(){
try{
return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
}catch(e){
return null
}
}
function fillCell(e){
var s = [];
for(var i in e){
if(i.substr(0, 2) == 'ch') s.push(e[i]);
}
if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', '));
}
<div>
<? var data = valid(); ?>
<form id="form" name="form">
<? if(Object.prototype.toString.call(data) === '[object Array]') { ?>
<? for (var i = 0; i < data.length; i++) { ?>
<? for (var j = 0; j < data[i].length; j++) { ?>
<input type="checkbox" id="ch<?= '' + i + j ?>" name="ch<?= '' + i + j ?>" value="<?= data[i][j] ?>"><?= data[i][j] ?><br>
<? } ?>
<? } ?>
<? } else { ?>
<p>May be current cell havn't the <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation...</a></p>
<? } ?>
<input type="button" value="fill current" onclick="google.script.run.fillCell(this.parentNode)" />
<input type="button" value="get validation from current" onclick="google.script.run.showDialog()" />
</form>
</div>
@gregmanetti
Copy link

This really nice!

A couple questions...
1- If I wanted to add an input like "Discard" that effectively closes Page.html the way that the system "X" does in the top right corner, what would I set as the onClick event?

input type="button" value="Discard" onclick="..." />

2- When the ShowDialog is fired, any ideas on how the values already in the cell could be used to pre-check options shown in the Page.html? Let's say that my validation list of values is: blue, red, yellow, green and cell B3 contains "blue,green" when I invoke the Custom Menu / showdialog, I'd want blue and green to be checked and red and yellow to be unchecked.

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment