How to pass variables between functions in Google Apps Script when using menu items?
I was able to successfully create a custom menu with onOpen()
below code .
My desired result is to use my function setSheet()
to set a specific sheet as soon as the user clicked on a menu item to select the given sheet.
The problem is that the function setSheet
doesn't actually do anything, because I can't figure out how to pass the "name" menu item to another function.
I'm not sure what I am doing wrong in this case? Any help you could provide would be appreciated. Thank!
Here is the working code I have so far:
function onOpen(e) {
subMenus = [];
ss = SpreadsheetApp.getActiveSpreadsheet();
allsheets = ss.getSheets();
for ( i = 0; i < allsheets.length; ++i){
sheet = allsheets[i]
subMenus.push({name:sheet.getName(), functionName: 'setSheet'})
}
SpreadsheetApp
.getActiveSpreadsheet()
.addMenu("Pick A Spreadsheet", subMenus)
}
function setSheet() {
ss = SpreadsheetApp.getActiveSpreadsheet();
name = ss.getss.getSheetByName()
ss.setActiveSheet(name)
}
Also, I did a workaround using the code below, but I would like to keep the user from having to enter values into the prompt directly, because they might not know the value of the sheet name.
function onOpen(e) {
subMenus = [{name:"Go To Sheet", functionName: 'setSheet'}];
SpreadsheetApp
.getActiveSpreadsheet()
.addMenu("Pick A Spreadsheet", subMenus)
}
function setSheet() {
ss = SpreadsheetApp
active = ss.getActiveSpreadsheet()
ui = ss.getUi();
response = ui.prompt("What Sheet Do You Need?")
active.setActiveSheet(active.getSheetByName(response.getResponseText()))
}
source to share
Functions called from a menu cannot have parameters, there is no real alternative but to - one way or another - hard-code the values, or at least references to variables stored somewhere else (like a property service).
But in any case, I find the selection of a sheet using the menu less optimal solution, not very user-friendly.
That is why I suggest you try this version which uses a sidebar with html interface. You will need to create 3 files in your project, 1 script file and 2 html files.
the code is below in 3 separate elements (quite a long time, I know ... here's a link to read just a demo that you can copy for use)
below is the screen capture of the result.
code.gs
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Utilities")
.addItem('authorize script', 'autorize')
.addToUi();
showSidebar();
}
function autorize(){
showSidebar();
}
function showSidebar() {
var ui = HtmlService.createTemplateFromFile('Sidebar')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle('Sheet selector');
SpreadsheetApp.getUi().showSidebar(ui);
}
function createList(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var list = [];
var sh, sheetName;
for(var s=0;s<ss.getNumSheets();s++){
sh = ss.getSheets()[s];
sheetName = sh.getName();
list.push(sheetName);
}
var message = '';
var color;
message+= '<table>';
message+='<tr valign="top" cellpadding=5>'
for(var n=0;n<list.length;n++){
color="#000"
//Logger.log('list[n] = '+list[n]+' is in '+alreadyCreated.indexOf(list[n])+' position');
message+='<tr><td align="center"> '+(n+1)+' </td><td>'+
'<input type="button" id="button'+n+'" value="'+list[n]+'" style="color:'+color+';" onclick="selectSheet(\''+n+'\')"/></td></tr>';
}
message+='</table>';
return message;
}
function showSheetGS(sheetNumber){
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getSheets()[sheetNumber].activate();
}
Sidebar.html
<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
<body>
<!-- Below is the HTML code that defines the sidebar element structure. -->
<div class="sidebar branding-below">
<!-- The div-table class is used to make a group of divs behave like a table. -->
<h3> Listof available sheets,<br><span style="color:#0A0"> green color </span> if already selected</h3>
<div id="sidebarList">
<br><br><br>...please wait a second ...<br>
<p>If nothing happens please authorize the script using the menu.</p>
</div>
<!-- Enter sidebar bottom-branding below. -->
<div class="sidebar bottom">
<img alt="Add-on logo" class="logo" width="25"
src="http://insas.cluster006.ovh.net/serge/apps-script_2x.png">
<span class="gray branding-text">sheet selector demo V1.0<br>        © SG 2017</span>
</body>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script>
/**
* Run initializations on sidebar load.
*/
populateEncodeLocal();
function populateEncodeLocal(){
//console.log('populateEncode');
google.script.run.withSuccessHandler(populateList).createList();
}
function selectSheet(sheetNumber){
console.log(sheetNumber);
google.script.run.withSuccessHandler(showSheet(sheetNumber)).showSheetGS(sheetNumber);
}
function showSheet(n){
console.log("showSheet"+n);
$('#button'+n).css('font-size','12pt').css('color','#0A0');
}
function populateList(data){
//console.log(data);
$('#sidebarList').html(data);
}
</script>
Stylesheet.html
<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">
<style>
html, body {
max-width: 100%;
overflow-x: hidden;
}
#sidebarList {
height:600px;
overflow:auto;
}
table{
display:block;
background-color:#EEE;
border:1px solid #666666;
border-spacing:2px;
border-collapse:collapse;
font-family:arial,sans;
font-size:9pt;
}
th {
border-bottom: 0px solid #acacac;
font-weight: normal;
padding: 1px 1px 0;
text-align: left;
}
td {
border-bottom: 0px solid #ebebeb;
padding: 1px 0;
}
input[type=button] {
color:#08233e;
font:11pt Helvetica,Arial,sans;
padding:3px;
background:#FFE;
border:1px solid #AAA;
-moz-border-radius:5px;
-webkit-border-radius:5px;
border-radius:5px;
cursor:pointer;
width:270px;
white-space: normal;
}
input[type=button]:hover {
border:1px solid #000;
}
label {
font-weight: bold;
}
.branding-below {
bottom: 3px;
top: 0px;
padding-left:0px;
}
.branding-text {
left: 7px;
position: relative;
top: 3px;
}
.logo {
vertical-align: middle;
}
</style>
source to share